PostgreSQL错误从没有时区的时间戳转换为时区的时间戳

没有时区的时间戳AT TIME ZONE将时间戳重新解释为处于该时区，以便将其转换为UTC。

具有时区的时间戳AT TIME ZONE将timestamptz转换为指定时区的时间戳。

PostgreSQL使用ISO-8601时区，它指定格林威治以东是正的…除非你使用POSIX时区说明符，在这种情况下它遵循POSIX。疯狂随之而来。

为什么第一个产生意想不到的结果

SQL中的时间戳和时区是可怕的。这个：

select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'EST5EDT';

将未知类型的文本“2011-12-30 00:30:00”作为没有时区的时间戳，其中Pg假设是在本地时区，除非另有说明。当您使用AT TIME ZONE时，它(根据规范)重新解释为时区EST5EDT中的时区，然后存储为UTC中的绝对时间，因此它从EST5EDT转换为UTC，即时区偏移获取减去。 x – ( – 5)为x 5。

然后根据您的服务器TimeZone设置调整此UTC时间戳，调整为UTC存储，以便显示，以便以本地时间显示。

如果你想说“我有UTC时间的时间戳，并希望看到EST5EDT中的等效本地时间是什么”，如果你想独立于服务器TimeZone设置，你需要写如下：

select TIMESTAMP '2011-12-30 00:30:00' AT TIME ZONE 'UTC'
       AT TIME ZONE 'EST5EDT';

这说明“给定时间戳2011-12-30 00:30:00，在转换为timestamptz时将其视为UTC中的时间戳，然后将该timestamptz转换为EST5EDT中的本地时间”。

可怕，不是吗？我想给一个坚决的谈论谁决定的时间区域的疯狂的语义 – 它应该是像时间戳CONVERT从时间区域-5’和timestamptz CONVERT到时间区域5’。此外，具有时区的时间戳实际上应该带有它的时区，不存储在UTC和自动转换为本地时间。

为什么第二个工作(只要TimeZone = UTC)

您的原创“作品”版本：

select '2011-12-30 00:30:00' AT TIME ZONE 'EST5EDT';

仅当TimeZone设置为UTC时才是正确的，因为text-to-timestamptz强制转换在没有指定时会假定TimeZone。

为什么第三个工程

两个问题彼此抵消。

看起来工作的另一个版本是TimeZone独立的，但它只工作，因为两个问题自己取消。首先，如上所述，没有时区AT TIME ZONE的时间戳将该时间戳重新解释为在该时间区域中，以便转换为UTC时间戳。这有效地减去时区偏移。

然而，由于我超越了我的原因，PostgreSQL使用与我用来看到大多数地方的时间戳相反的符号。见the documentation：

Another issue to keep in mind is that in POSIX time zone names,positive offsets are used for locations west of Greenwich. Everywhere else,PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

这意味着EST5EDT与5相同，而不是-5。这就是为什么它工作原因：因为你减去tz偏移量不添加它，但你减去一个否定的偏移量！

你需要得到正确的是：

select TIMESTAMP '2011-12-30 00:30:00' AT TIME ZONE 'UTC'
       AT TIME ZONE '+5';