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

概述我今天上午遇到以下问题： 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 TIM 我今天上午遇到以下问题：

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)

任何人都有回答为什么postgresql转换时间戳没有时区一些奇怪的方式，而是取走5小时它增加？

关键事情要了解

没有时区的时间戳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';

总结

以上是内存溢出为你收集整理的PostgreSQL错误从没有时区的时间戳转换为时区的时间戳全部内容，希望文章能够帮你解决PostgreSQL错误从没有时区的时间戳转换为时区的时间戳所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。