postgresql-time-zones/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важно! Функция timezone и at time zone также могут принимать часовой пояс в формате вроде UTC+3 . Однако это работает контринтуитивно и не рекомендуется к практическому использованию.

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

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

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

Fun fact! Если у времени в конкретном часовом поясе могут быть «дырки», то как timezone(zone, timestamp) обрабатывает невозможное время, попадающие в эти «дырки»? В данном сценарии функция завершается успешно, при этом невозможное время преобразуется в ближайшее возможное .

Для 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 как с обычным временем. Если нужно несколько часовых поясов, строим несколько агрегатов. Здесь предполагается, что вы не работаете с временем из далекого будущего и своевременно обновляетесь. В противном случае агрегаты рано или поздно сломаются, и починить их сможет только перестройка из сырых данных. Хранить последние — всегда хорошая мысль, как минимум, потому что требования к системе меняются, и возникает необходимость в новых агрегатах. Ну и баги в коде никто не отменял.