Неправильное преобразование 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 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 преобразует timestamp without time zone какой-то странный способ и вместо этого убирает 5 часов вместо этого?

Ответ 1

Ключевые вещи для понимания

timestamp without time zone AT TIME ZONE повторно интерпретирует a timestamp как находящийся в этом часовом поясе с целью его преобразования в UTC.

timestamp with time zone AT TIME ZONE преобразует a timestamptz в timestamp в указанный часовой пояс.

PostgreSQL использует временные интервалы ISO-8601, которые указывают, что восток от Greenwich положительный... если вы не используете спецификатор часового пояса POSIX, и в этом случае он следует за POSIX. Наступает безумие.

Почему первый создает неожиданный результат

Временные метки и временные интервалы в SQL ужасны. Это:

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.

Эта временная метка, скорректированная на UTC-хранилище, затем настраивается для вашего сервера TimeZone для отображения, чтобы он отображался в локальное время.

Если вы хотите сказать "У меня есть эта метка времени в 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, рассматривайте ее как отметку времени в UTC при преобразовании в timestamptz, а затем конвертируйте этот timestamptz в локальное время в EST5EDT".

Ужасно, не так ли? Я хочу дать твердую беседу с тем, кто решил сумасшедшую семантику AT TIME ZONE - это действительно должно быть что-то вроде timestamp CONVERT FROM TIME ZONE '-5' и timestamptz CONVERT TO TIME ZONE '+5'. Кроме того, timestamp with time zone должен фактически нести свой часовой пояс с ним, не сохраняться в UTC и автоматически преобразовываться в локальное время.

Почему второй работает (пока TimeZone = UTC)

Ваша оригинальная версия "works":

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

будет корректным только в том случае, если TimeZone установлен в UTC, потому что при переходе от текста к timestamptz используется TimeZone, если он не указан.

Почему третий работает

Две проблемы отменяют друг друга.

Другая версия, которая, похоже, работает независимо от TimeZone, но работает только потому, что две проблемы отменяют себя. Во-первых, как объяснялось выше, timestamp without time zone AT TIME ZONE повторно интерпретирует временную метку как находящуюся в этом часовом поясе для преобразования в timestamptz UTC; это эффективно вычитает смещение часового пояса.

Тем не менее, по причинам, за пределами моего ken, PostgreSQL использует отметки времени с обратным знаком для того, что я привык видеть в большинстве мест. См. документацию:

Еще одна проблема, которая стоит иметь в виду, заключается в том, что в именах часовых поясов POSIX положительные смещения используются для местоположений к западу от Гринвича. В любом случае PostgreSQL следует за соглашением ISO-8601, что положительные сдвиги в часовом поясе находятся к востоку от Гринвича.

Это означает, что EST5EDT совпадает с +5, а не -5. Вот почему он работает: потому что вы вычитаете смещение tz, не добавляя его, но вы вычитаете отрицательное смещение!

Вместо этого вам нужно будет сделать это правильно:

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