关键事情要了解
没有时区的时间戳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';