PostgreSQL从不带时区的时间戳转换为带时区的时间戳错误

Question

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

返回我2011-12-30 05:30:00+00女巫是错误的。

但接下来的查询如下：

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

我看对日期2011-12-29 19:30:00

SELECT  current_setting('TIMEZONE');
current_setting
-----------------
     UTC
(1 row)

Answer 1

timestamp在时区中没有时区将timestamp重新解释为在该时区中，以便将其转换为UTC。

TimeStamp with time zone将TimeStamptz转换为指定时区的TimeStampt。

PostgreSQL使用ISO-8601时区，它指定格林威治以东为正值……除非您使用POSIX时区说明符，在这种情况下，它遵循POSIX。精神错乱随之而来。

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

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

相反，如果您希望说“我有UTC时间的时间戳，并希望查看EST5EDT中等效的本地时间是多少”，如果您希望独立于服务器时区设置，则需要编写如下内容：

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

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

很可怕，不是吗？我想给那些决定在时区使用疯狂语义的人一个坚定的谈话--它实际上应该是类似于timestamp从时区'-5'转换为时区'+5'和timestamptz转换为时区'+5'。此外，带有时区的时间戳实际上应该携带它的时区，而不是存储在UTC中并自动转换为LocalTime。

你最初的“作品”版本：

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

只有当TimeZone设置为UTC时才是正确的，因为当没有指定text-to-timestamptz时，将假定TimeZone为TimeZone。

另一个似乎有效的版本是独立于时区的，但它只是因为两个问题抵消了自己而有效。首先，如上所述，timestamp没有时区在时区将时间戳重新解释为在该时区中，以便转换为UTC时间戳；这有效地减去了时区偏移量。

然而，出于我无法理解的原因，PostgreSQL使用的时间戳与我习惯看到的大多数地方的时间戳相反。请参阅文档：

另一个需要记住的问题是，在POSIX时区名称中，正偏移量用于格林威治以西的位置。在其他地方，PostgreSQL遵循ISO-8601的约定，即正时区偏移位于格林威治以东。

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