postgresql - 如何在 PostgreSQL 中读取带有时区的时间戳？

Question

假设我有一个时间戳，时区存储在我的 PostgreSQL 数据库中。像这样的东西：

2003-04-12 04:05:06.814191 America/New_York

读取此数据时，数据库是否会返回调整后的 GMT 时间以标准化所有读取的时间戳，还是直接按原样返回？

我问这个问题的原因是我创建了一个包含记录的表，其中每条记录都有一个时间戳列。我继续为不同的记录提供不同的时区。当我查询此表并尝试按时间戳列排序时，时区似乎对排序没有任何影响。这是设计使然吗？

我的结果样本：

1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 Asia/Dhaka
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 Asia/Damascus
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 Asia/Damascus
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 Asia/Dhaka
1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 Asia/Baghdad

所有这些记录的日期和时间都相同，不同的时区对排序没有影响。

为了更清楚，我实际上将这些时间戳作为字符串存储在一个 JSONb 列中，该列data以 为键调用datetime，即：

data = {
    "datetime" : "2003-04-12 04:05:06.814191 America/New_York",
    .....
}

在排序和过滤期间，我将其转换为时间戳。像这样的东西：

"(data->>'datetime')::timestamp"

标签： postgresqldatetime

Answer 1

PostgreSQL 的“带时区的时间戳”类型实际上并没有在每条记录中存储时区偏移量。它只是在内部将所有内容标准化为 UTC。

“带有时区的时间戳”的要点在于，由于存储的值是在一个已知的时区中表示的，所以它明确地表示了一个特定的时间点。另一种选择，“没有时区的时间戳”是模棱两可的，因为它有时间但没有说明它在哪个时区——记录可能是今天中午，但那个中午是格林威治吗？中午在纽约？东京中午？“带时区”类型避免了这个问题，因为该区域始终是格林威治。

（为了比较：我相信 Oracle 有一个“带有时区的时间戳”，它实际上在每条记录中存储一个偏移量，以及一个“带有本地时区的时间戳”，它将所有内容标准化为服务器的时区，以避免必须存储每条记录的偏移量. PostgreSQL 的“timestamp with time zone”的命名与前者相似，但行为与后者相似。）

---

但是，听起来您实际上并没有在表中存储“带有时区的时间戳”；您本质上是在存储一个字符串，并将其转换timestamp为select. timestamp关键字本身是指“无时区”变体（根据 SQL 标准的要求），PostgreSQL 文档说：

在已确定为 的文字中timestamp without time zone，PostgreSQL 将默默地忽略任何时区指示。也就是说，结果值源自输入值中的日期/时间字段，并且未针对时区进行调整。

我希望这同样适用于强制转换，但如果是这种情况，则不太清楚为什么您的输出（timestamp值）包含 TZ 名称。但是您可能想要转换为timestamptz，或者创建一个单独的timestamptz列（以便可以索引值）。

---

有关日期/时间类型的PostgreSQL 文档是一个很好的参考。