Работа с часовыми поясами в PostgreSQL

1 ноября 2021

Часовые пояса обычно ассоциируются с чем-то невероятно сложным. Однако, как это часто бывает, если сесть и спокойно во всем разобраться, то проблема оказывается не такой уж большой. Особенно если положиться на проверенные решения, где уже реализована вся логика.

PostgreSQL предлагает два типа для хранения даты и времени — timestamp и timestamptz. Внутреннее представление данных типов абсолютно одинаковое. Это 64-х битные знаковые целые, хранящие время в микросекундах относительно 1-го января 2000-го года 00:00:00 UTC.

Отличие только в том, как они отображаются:

=# select now() :: timestamp;
            now
----------------------------
 2021-10-01 12:34:56.721186

=# select now() :: timestamptz;
              now
-------------------------------
 2021-10-01 12:34:57.084779+03

С timestamp все понятно, это же просто время в UTC. Однако timestamptz отображается в часовом поясе текущей сессии:

=# show time zone;
   TimeZone
---------------
 Europe/Moscow

=# select '2021-10-01 12:34:56 Europe/Moscow' :: timestamptz;
      timestamptz
------------------------
 2021-10-01 12:34:56+03

=# set time zone '+00';
SET

=# select '2021-10-01 12:34:56 Europe/Moscow' :: timestamptz;
      timestamptz
------------------------
 2021-10-01 09:34:56+00

Еще раз, ключевой момент — значение одно и то же! Но отображается оно по-разному в зависимости от параметров сессии.

Часовой пояс, используемый по умолчанию, определяется параметром timezone в файле postgresql.conf. Этот параметр прописывается утилитой initdb во время инициализации СУБД. Значение параметра определяется окружением, в котором был запущен initdb. Если параметр не указан в postgresql.conf, используется часовой пояс GMT.

Получить информацию о доступных именах часовых поясов можно так:

=# select * from pg_timezone_names where abbrev = 'MSK';
       name        | abbrev | utc_offset | is_dst
-------------------+--------+------------+--------
 Europe/Moscow     | MSK    | 03:00:00   | f
 Europe/Simferopol | MSK    | 03:00:00   | f
 W-SU              | MSK    | 03:00:00   | f

Отсюда мы узнаем, что для Europe/Moscow есть сокращение MSK. Сокращения можно использовать при преобразовании строки в timestamptz:

=# select '2021-10-01 12:34:56 MSK' :: timestamptz;
      timestamptz
------------------------
 2021-10-01 12:34:56+03

Но команда set time zone принимает только полное имя часового пояса:

=# set time zone 'MSK';
ERROR:  invalid value for parameter "TimeZone": "MSK"

=# set time zone 'Europe/Moscow';
SET

Иногда нужно преобразовать timestamptz в timestamp или наоборот. Если делать это в лоб, то результат может получиться не тот, который вы ожидаете:

=# set time zone '+00';
SET

=# select ('2021-10-01 12:34:56 MSK' :: timestamptz) :: timestamp;
      timestamp
---------------------
 2021-10-01 09:34:56

Как же так? Ведь я хотел отрезать информацию о таймзоне и получить 12:34:56! Но вспомним, что внутри timestamptz хранится просто как время в UTC. В момент, когда timestamptz кастуется в timestamp у PostgreSQL нет никакой возможности узнать, что изначально время было в Europe/Moscow. Поэтому он преобразует время в часовой пояс текущий сессии.

Домашнее задание: Вызовите now() и now() :: timestamp. Смените часовой пояс сессии и повторите эксперимент. Объясните результат.

Для решения описанной проблемы есть альтернативный синтаксис:

=# select ('2021-10-01 12:34:56 Europe/Moscow' :: timestamptz) ⏎
   at time zone 'Europe/Moscow';
      timezone
---------------------
 2021-10-01 12:34:56

Или, что полностью эквивалентно, можно воспользоваться функцией timezone:

=# select timezone('Europe/Moscow', ⏎
   '2021-10-01 12:34:56 Europe/Moscow' :: timestamptz);
      timezone
---------------------
 2021-10-01 12:34:56

Для преобразования в обратную сторону есть перегруженная версия timezone, принимающая timestamp и возвращающая timestamptz:

=# set time zone 'Europe/Moscow';
SET

=# select timezone('Europe/Moscow', '2021-10-01 12:34:56' :: timestamp);
        timezone
------------------------
 2021-10-01 12:34:56+03

Синтаксис at time zone также работает для timestamp:

=# select ('2021-10-01 12:34:56' :: timestamp) ⏎
   at time zone 'Europe/Moscow';
        timezone
------------------------
 2021-10-01 12:34:56+03

Когда вы работаете в UTC или каком-нибудь UTC+3, все просто и понятно. Но при использовании часового пояса вроде MSK все интереснее. Вот пример:

=# set time zone 'Europe/Moscow';
SET

=# select (timestamptz '2014 Oct 26 01:00:00 MSK');
      timestamptz
------------------------
 2014-10-26 01:00:00+03

=# select (timestamptz '2014 Oct 26 01:00:00 MSK') - interval '1 hour';
        ?column?
------------------------
 2014-10-26 01:00:00+04

Был час ночи. Отняли один час, и получили час ночи. Удобно, не правда ли?

Дело в том, что московское время несколько раз менялось в прошлом, и наверняка еще изменится в будущем. 26 октября 2014-го года часовой пояс MSK как раз изменился с UTC+4 на UTC+3, о чем PostgreSQL прямым текстом и говорит — обратите внимание на +03 и +04 в выводе. Аналогичные приколы вас ждут в часовых поясах, где есть перевод часов на летнее и зимнее время (daylight saving time, DST). В Москве сейчас не переводят часы, но до марта 2011-го года переводили.

Для timestamptz из прошлого PostgreSQL применяет правила для часового пояса, которые были актуальны на тот момент времени. Приведенный выше пример наглядно это демонстрирует. Другими словами, если сейчас в вашем часовом поясе время T, оно всегда будет временем T в этом часовом поясе, даже если в будущем правила изменятся. Для времени из будущего применяются последние известные правила. Это означает, что при обновлении PostgreSQL и/или системы могут появится новые правила для часовых поясов, и функции, использующие timestamptz, начнут возвращать другой результат. Впрочем, это касается не только времени из будущего. Также были прецеденты исторических корректировок часовых поясов.

Несмотря на написанное выше, volatility функций, работающих с timestamptz, не обязательно является STABLE. В качестве STABLE-функции, работающей с timestamptz, можно привести в пример date_trunc(). Дело в том, что ее работа зависит от параметров сессии. Кастинг timestamptz в timestamp является STABLE по той же причине. Однако функция timezone() не смотрит на параметры сессии. Ее возвращаемое значение зависит только от переданных аргументов, и потому функция является IMMUTABLE. PostgreSQL не будет возражать, если вы решите использовать ее в функциональных индексах. И при обновлении базы часовых поясов индексы могут разъехаться.

Fun fact! PostgreSQL имеет собственную базу часовых поясов. В исходном коде эта база называется tzdata.zi. Но также PostgreSQL можно собрать с флагом --with-system-tzdata=. Он говорит использовать вместо собственной базы системные TZif-файлы (RFC 8536). Эти файлы обычно живут в /usr/share/zoneinfo. Чтобы узнать, какую базу использует PostgreSQL, нужно проверить, с какими флагами был собран используемый вами пакет. Эти флаги отображает команда pg_config --configure.

Все перечисленное важно понимать для решения практических задач.

Допустим, вам нужно строить агрегаты по дням или месяцам в конкретном часовом поясе. Оказывается, что самое простое и надежное решение — это преобразовать время в timestamp при помощи at time zone, и затем обращаться с timestamp как с обычным временем. Если нужно несколько часовых поясов, строим несколько агрегатов. Здесь предполагается, что вы не работаете с временем из далекого будущего и своевременно обновляетесь. В противном случае агрегаты рано или поздно сломаются, и починить их сможет только перестройка из сырых данных. Хранить последние — всегда хорошая мысль, как минимум, потому что требования к системе меняются, и возникает необходимость в новых агрегатах. Ну и баги в коде никто не отменял.

Это все, о чем я хотел сегодня рассказать. Если у вас есть вопросы, или интересный опыт работы с часовыми поясами, буду рад вашим комментариям.

Метки: , .