postgresql PostgreSQL错误地从没有时区的时间戳转换为有时区的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21277170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone
提问by saicheg
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+00witch is wrong.
还我2011-12-30 05:30:00+00女巫错了。
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
我看到正确的日期 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 zonesome weird way and instead taking away 5 hours it adds instead?
有没有人知道为什么 postgresql 会转换timestamp without time zone一些奇怪的方式，而不是拿走它增加的 5 个小时？
回答by Craig Ringer
Key things to understand
需要了解的关键事项
timestamp without time zone AT TIME ZONEre-interpretsa timestampas being in that time zone for the purpose of converting it to UTC.
timestamp without time zone AT TIME ZONE将a重新解释timestamp为在该时区，以便将其转换为 UTC。
timestamp with time zone AT TIME ZONEconvertsa timestamptzinto a timestampat the specified timezone.
timestamp with time zone AT TIME ZONE在指定的时区将a转换timestamptz为 a timestamp。
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.
PostgreSQL 使用 ISO-8601 时区，它指定格林威治以东为正数……除非您使用 POSIX 时区说明符，在这种情况下它遵循 POSIX。疯狂随之而来。
Why the first one produces an unexpected result
为什么第一个会产生意想不到的结果
Timestamps and timezones in SQL are horrible. This:
SQL 中的时间戳和时区很糟糕。这个：
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-interpretedas a timestamp with time zonein the time zone EST5EDTthen stored as an absolute time in UTC - so it's converted fromEST5EDTtoUTC, i.e the timezone offset gets subtracted. x - (-5)is x + 5.
将未知类型的文字解释'2011-12-30 00:30:00'为timestamp without time zone，除非另有说明，否则 Pg 假定它在本地时区中。当您使用 时AT TIME ZONE，它（根据规范）被重新解释timestamp with time zone为时区中的a ，EST5EDT然后存储为 UTC 中的绝对时间 - 因此它从转换EST5EDT为UTC，即时区偏移量被减去。x - (-5)是x + 5。
This timestamp, adjusted to UTC storage, is then adjusted for your server TimeZonesetting for display so that it gets displayed in local time.
此时间戳已调整为 UTC 存储，然后针对您的服务器TimeZone设置进行调整以进行显示，以便以本地时间显示。
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:
如果您想说“我在 UTC 时间有这个时间戳，并希望查看 EST5EDT 中的等效本地时间是什么”，如果您想独立于服务器 TimeZone 设置，则需要编写如下内容：
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".
这表示“给定时间戳 2011-12-30 00:30:00，在转换为时间戳记时将其视为 UTC 中的时间戳，然后将该时间戳记转换为 EST5EDT 中的本地时间”。
Horrible, isn't it? I want to give a firm talking towhoever 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 zoneshould actually carry its timezone with it, not be stored in UTC and auto-converted to localtime.
太可怕了，不是吗？我想与决定疯狂语义的人进行坚定的交谈AT TIME ZONE- 它应该真的像timestamp CONVERT FROM TIME ZONE '-5'和timestamptz CONVERT TO TIME ZONE '+5'。此外，timestamp with time zone实际上应该随身携带它的时区，而不是存储在 UTC 中并自动转换为本地时间。
Why the second works (so long as TimeZone = UTC)
为什么第二个有效（只要 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.
仅当 TimeZone 设置为 UTC 时才正确，因为当未指定时，文本到时间戳转换假定 TimeZone。
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 ZONEre-interpretsthe timestamp as being in that time zone for conversion to a UTC timestamptz; this effectively subtractsthe timezone offset.
另一个似乎有效的版本与 TimeZone 无关，但它之所以有效，是因为两个问题相互抵消。首先，如上所述，将时间戳timestamp without time zone AT TIME ZONE重新解释为在该时区中以转换为 UTC 时间戳；这有效地减去了时区偏移。
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:
然而，出于我无法理解的原因，PostgreSQL 使用时间戳与我在大多数地方看到的相反符号。请参阅文档：
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.
要记住的另一个问题是，在 POSIX 时区名称中，格林威治以西的位置使用正偏移量。在其他任何地方，PostgreSQL 都遵循 ISO-8601 约定，即正时区偏移位于格林威治以东。
This means that EST5EDTis 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!
这意味着EST5EDT与 相同+5，而不是-5。这就是它起作用的原因：因为您减去的是 tz 偏移量而不是添加它，而是减去一个否定的偏移量！
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';
