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

问题描述

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

I faced with the following issue this morning:

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

防止您对我的本地时区提出问题:

Preventing your question about my local timezone:

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

有没有人回答为什么 postgresql 会以某种奇怪的方式转换 timestamp without time zone 而是花费 5 个小时来代替它添加?

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

推荐答案

要理解的关键事项

timestamp without time zone AT TIME ZONE 重新解释一个timestamp在那个时区为了转换它到 UTC.

timestamp with time zone AT TIME ZONE 转换一个 timestamptz 到一个 timestamp 在指定的时区.

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

PostgreSQL 使用 ISO-8601 时区，它指定格林威治以东为正数……除非您使用 POSIX 时区说明符，在这种情况下它遵循 POSIX.精神错乱随之而来.

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.

SQL 中的时间戳和时区非常糟糕.这个:

Timestamps and timezones in SQL are horrible. This:

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

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.

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

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

如果您希望说我有这个 UTC 时间的时间戳，并希望查看 EST5EDT 中的等效本地时间是多少"，如果您想独立于服务器 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:

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 中的本地时间".

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".

太可怕了，不是吗?我想与 公司交谈 谁决定了 AT TIME ZONE 的疯狂语义 - 它实际上应该类似于 timestamp CONVERT FROM TIME ZONE '-5' 和 timestamptz 转换为时区 '+5'.此外，timestamp with time zone 应该实际携带其时区，而不是存储在 UTC 中并自动转换为本地时间.

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.

你原来的作品"版本:

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

仅当 TimeZone 设置为 UTC 时才会正确，因为 text-to-timestamptz 转换假定 TimeZone 在未指定时.

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

两个问题相互抵消.

另一个似乎可以工作的版本是独立于 TimeZone 的，但它之所以有效，是因为两个问题相互抵消了.首先，如上所述，timestamp without time zone AT TIME ZONE 重新解释时间戳在该时区，以便转换为 UTC timestamptz；这有效地减去时区偏移.

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.

但是，由于我无法理解的原因，PostgreSQL 使用与我习惯在大多数地方看到的相反符号的时间戳.请参阅文档:

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:

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

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 偏移量而不是添加它，而是减去了一个否定的偏移量！

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!

您需要做的是:

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

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