PostgreSQL错误地从无时区的时间戳转换为有时区的时间戳 [英] PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone

问题描述

今天早上我遇到了以下问题：

 选择'2011-12-30 00:30:00'： ：在时区 EST5EDT不带时区的时间戳； 
  

还给我 2011-12-30 05：30：00 + 00 巫婆是错的。

但是下面的下一个查询：

 选择 2011-12-30 00:30:00 :: timestamp，不带时区AT时区 UTC-5； 
选择 2011-12-30 00:30:00在时区 EST5EDT； 
  

我看到正确的日期 2011-12-29 19:30:00

防止出现有关我的本地时区的问题：

 选择current_setting（'TIMEZONE'）; 
 current_setting 
 ----------------- 
 UTC 
（1行）
  

有人能回答为什么PostgreSQL转换没有时区的时间戳有点奇怪的方法，而不是拿走5

解决方案

要理解的关键内容

<没有时区的code>时间戳 重新解释 时间戳处于该时区出于将其转换为UTC的目的。

带有时区AT时区的时间戳 在指定时区将时间戳转换为时间戳。

PostgreSQL使用ISO-8601时区，该时区指定格林威治以东为正...除非您使用POSIX时区说明符，在这种情况下，它遵循POSIX。

为什么第一个产生意外结果

SQL中的时间戳和时区太可怕了。这：

 选择'2011-12-30 00:30:00':: timestamp不带时区AT时区'EST5EDT '; 
  

表示未知类型的文字'2011-12-30 00:30 ：00'作为不带时区的时间戳，除非另有说明，否则Pg假定它位于本地TimeZone中。当您使用 AT TIME ZONE 时，（按规范）它被重新解释为带有时区的时间戳 / code>在时区 EST5EDT 中，然后作为绝对时间存储在UTC中-因此将其转换为 from EST5EDT 到 UTC，即时区偏移量被减去。 x-（-5）是 x + 5 。

此时间戳已调整为UTC存储，然后针对您的服务器 TimeZone 设置进行了调整以进行显示，以使其在本地时间显示。

如果您想说我有UTC时间的此时间戳，并希望了解EST5EDT中的等效本地时间是多少，那么如果要独立于服务器的TimeZone设置，则需要可以这样写：

 选择TIMESTAMP'2011-12-30 00:30:00'在时区'UTC'
时区 EST5EDT； 
  

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

糟糕，不是吗？我想让公司与交谈，无论谁决定 AT时区的疯狂语义，它实际上应该类似于 timestamp从时区'-5'和 timestamptz转换为时区'+5'。另外，带有时区的时间戳应该实际上带有其时区，而不是存储在UTC中并自动转换为本地时间。

第二个为什么工作（只要TimeZone = UTC）

您原来的作品版本：

 在时区 EST5EDT中选择 2011-12-30 00:30:00； 
  

仅在将TimeZone设置为UTC时才是正确的，因为文本到timestamptz的转换假定为TimeZone

第三个为什么起作用

两个问题相互抵消。

另一个似乎起作用的版本是TimeZone独立的，但是它只起作用是因为有两个问题使它们自己消失了。首先，如上所述，不带时区的时间戳在时区 将重新解释为位于该时区中，以转换为UTC时间戳;有效地减去时区偏移。

但是，出于我无法理解的原因，PostgreSQL使用时间戳和带有反向符号的时间戳来显示我经常看到的地方。请参见文档：

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

这意味着 EST5EDT 与 +5 相同，而不是 -5 。这就是为什么它起作用的原因：因为您要减去不加的tz偏移量，而是要减去一个负的偏移量！

您需要得到什么相反，正确的是：

 选择TIMESTAMP'2011-12-30 00:30:00'在时区'UTC'
 AT TIME ZONE'+5'; 
  

I faced with the following issue this morning:

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

returns me 2011-12-30 05:30:00+00 witch is wrong.

But next queries below:

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';

i see right date 2011-12-29 19:30:00

Preventing your question about my local timezone:

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

Do anyone have answer why postgresql converts timestamp without time zone some weird way and instead taking away 5 hours it adds instead?

解决方案

Key things to understand

timestamp without time zone AT TIME ZONE re-interprets a timestamp as being in that time zone for the purpose of converting it to UTC.

timestamp with time zone AT TIME ZONE converts a timestamptz into a timestamp at the specified timezone.

PostgreSQL uses ISO-8601 timezones, which specify that east of Greenwich is positive ... unless you use a POSIX timezone specifier, in which case it follows POSIX. Insanity ensues.

Why the first one produces an unexpected result

Timestamps and timezones in SQL are horrible. This:

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

inteprets the unknown-typed literal '2011-12-30 00:30:00' as timestamp without time zone, which Pg assumes is in the local TimeZone unless told otherwise. When you use AT TIME ZONE, it is (per the spec) re-interpreted as a timestamp with time zone in the time zone EST5EDT then stored as an absolute time in UTC - so it's converted from EST5EDT to UTC, i.e the timezone offset gets subtracted. x - (-5) is x + 5.

This timestamp, adjusted to UTC storage, is then adjusted for your server TimeZone setting for display so that it gets displayed in local time.

If you instead wish to say "I have this timestamp in UTC time, and wish to see what the equivalent local time in EST5EDT is", if you want to be independent of the server TimeZone setting, you need to write something like:

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

This says "Given timestamp 2011-12-30 00:30:00, treat it as a timestamp in UTC when converting to timestamptz, then convert that timestamptz to a local time in EST5EDT".

Horrible, isn't it? I want to give a firm talking to whoever decided on the crazy semantics of AT TIME ZONE - it should really be something like timestamp CONVERT FROM TIME ZONE '-5' and timestamptz CONVERT TO TIME ZONE '+5'. Also, timestamp with time zone should actually carry its timezone with it, not be stored in UTC and auto-converted to localtime.

Why the second works (so long as TimeZone = UTC)

Your original "works" version:

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

will only be correct if TimeZone is set to UTC, because the text-to-timestamptz cast assumes TimeZone when one isn't specified.

Why the third one works

Two problems cancel each other out.

The other version that appears to work is TimeZone independent, but it only works because two problems cancel themselves out. First, as explained above, timestamp without time zone AT TIME ZONE re-interprets the timestamp as being in that time zone for conversion to a UTC timestamptz; this effectively subtracts the timezone offset.

However, for reasons I beyond my ken, PostgreSQL uses timestamps with the reverse sign to what I'm used to seeing most places. See 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.

This means that EST5EDT is the same as +5, not -5. Which is why it works: because you're subtracting the tz offset not adding it, but you're subtracting a negated offset!

What you'd need to get it correct is instead:

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

这篇关于PostgreSQL错误地从无时区的时间戳转换为有时区的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！