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)

有没有人回答为什么 postgresqltimestamp without time zone某种奇怪的方式转换，而是占用了 5 个小时？

Answer 1

要了解的关键事项

timestamp without time zone AT TIME ZONE将timestamp重新解释为位于该时区，以便将其转换为 UTC。

timestamp with time zone AT TIME ZONE将指定时区的timestamptz转换为timestamp。

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'理解为timestamp without time zone，除非另有说明，否则Pg假定它在本地时区。当您使用AT TIME ZONE时，它(根据规范)被重新解释为时区中的timestamp with time zone，EST5EDT然后以 UTC 格式存储为绝对时间 - 因此它从EST5EDT转换为UTC，即减去时区偏移量。x - (-5)是x + 5.

此时间戳调整为 UTC 存储，然后针对服务器TimeZone显示设置进行调整，以便以本地时间显示。

如果你想说"我有这个UTC时间的时间戳，并希望看到EST5EDT的等效本地时间是多少"，如果你想独立于服务器时区设置，你需要写这样的东西：

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

这表示"给定时间戳 2011-12-30 00：30：00，在转换为 timestamptz 时将其视为 UTC 格式的时间戳，然后将该时间戳转换为 EST5EDT 中的本地时间"。

太可怕了，不是吗？我想与决定AT TIME ZONE疯狂语义的人进行坚定的交谈- 它真的应该是类似于timestamp CONVERT FROM TIME ZONE '-5'和timestamptz CONVERT TO TIME ZONE '+5'的东西。此外，timestamp with time zone实际上应该随身携带其时区，而不是以 UTC 格式存储并自动转换为本地时间。

为什么第二个有效(只要时区 = UTC)

您的原始"作品"版本：

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

仅当时区设置为 UTC 时，才会正确，因为文本到时间戳转换在未指定时区时采用时区。

为什么第三个有效

两个问题相互抵消。

另一个似乎有效的版本与时区无关，但它之所以有效，是因为两个问题自行抵消。首先，如上所述，timestamp without time zone AT TIME ZONE将时间戳重新解释为在该时区，以转换为 UTC 时间戳;这有效地减去了时区偏移量。

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

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

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

相反，您需要正确处理的是：

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