Почему вам стоит отказаться от использования timestamp в PostgreSQL

Не секрет, что работа с часовыми поясами — боль, и многие разработчики объяснимо стараются ее избегать. Тем более что в каждом языке программирования / СУБД работа с часовыми поясами реализована по-разному.

Среди тех, кто работает с PostgreSQL, есть очень распространенное заблуждение про типы данных timestamp (который также именуется timestamp without time zone) и timestamptz (или timestamp with time zone). Вкратце его можно сформулировать так:

Мне не нужен тип timestamp with time zone, т.к. у меня все находится в одном часовом поясе — и сервер, и клиенты.

В статье я постараюсь объяснить, почему даже в таком довольно простом сценарии можно запросто напороться на проблемы. А в более сложных (которые на самом деле чаще встречаются на практике, чем может показаться) баги при использовании timestamp практически гарантированы.

Статья не претендует на полноту, но надеюсь, что поможет получше разобраться в этой не самой тривиальной теме :)

Про часовые пояса (time zones)

Концепция часовых поясов — на самом деле сравнительно недавнее изобретение человечества. Когда в XIX веке появились железные дороги и телеграф, люди пришли к тому, что настраивать часы в каждом городе на местное солнечное время крайне неудобно. В конечном счете планета оказалась разделена на 24 “полосы”, время в каждой из которых отстоит от ”нулевого пояса” (который обозначается как GMT или UTC) на целое количество часов. Есть, конечно, исключения вроде Индии и Ирана, но про них не в этой статье.

Казалось бы, тогда часовой пояс каждого стационарного места на Земле мог бы быть идентифицирован одним числом (смещением в часах относительно Гринвича). К сожалению, это не работает по двум причинам:

• во многих странах мира (например, в Европе и США) применяется летнее время, и 2 раза в год смещение от GMT меняется;

• из-за законодательных изменений города или страны могут перманентно менять часовой пояс.

Поэтому была разработана база данных tz database (https://ru.wikipedia.org/wiki/Tz_database ^[1]), в которой хранится актуальное соответствие географических идентификаторов часовым поясам. Идентификаторы, например, бывают такими: America/Buenos_Aires, Europe/Paris, Europe/Moscow и т. д. Каждому идентификатору соответствует набор правил, по которым можно вычислить смещение от GMT на какую-то дату. И когда какой-то город переходит в другой часовой пояс, в базу вносятся изменения.

Как PostgreSQL работает с часовыми поясами?

В PG есть несколько типов данных, использующихся для обозначения времени: https://www.postgresql.org/docs/current/datatype-datetime.html ^[2]

Здесь мы рассмотрим 2 наиболее часто встречающихся типа — timestamp и timestamptz.

В целом (по смыслу) timestamp соответствует локальному времени без учета часовых поясов, а timestamptz — времени с учетом часового пояса. Проще всего понять это, попробовав вывести текущее время с помощью функции now() без часового пояса и с ним:

public=> select now()::timestamp, now();

            now             |              now
----------------------------+-------------------------------
 2023-06-28 21:49:56.417841 | 2023-06-28 21:49:56.417841+03
(1 row)

Функция now() возвращает таймстемп в формате timestamptz, с учетом часового пояса (обратите внимание на +03 в конце).

Может сложиться впечатление, что тип timestamptz хранит “таймстемп плюс таймзону”. Но это не так — на самом деле для типа данных timestamptz хранится только время в UTC (18:49:56.417841), а при отображении вычисляется итоговое время на основании “текущей таймзоны сессии”. Обратимся к документации постгри ^[3]:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone ^[4] configuration parameter before being displayed to the client.

Тут говорится, что текущая таймзона определяется значением системного параметра TimeZone: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE ^[4]

TimeZone (string)

Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 ^[3] for more information.

Вкратце: дефолтное значение параметра TimeZone в PG, как правило, проставляется процессом initdb (который инициализирует свежий кластер БД) равным часовому поясу хоста, на котором крутится база. Это можно проверить в файле postgresql.conf: grep '^timezone' $PGDATA/postgresql.conf. Затем каждый клиент может переопределить параметр TimeZone по своему усмотрению в рамках сессии.

Важно, что поскольку часовой пояс никогда не сохраняется вместе со значением таймстемпа, тип данных timestamptz занимает в памяти ровно столько же места (8 байт), сколько и timestamp. Поэтому аргумент про то, что использование timestamp экономит место в памяти, совершенно несостоятелен.

Хьюстон, у нас проблема

Сложности чаще всего случаются тогда, когда клиенты БД могут находиться в разных часовых поясах.

Предположим, у ООО “Рога и Копыта” есть сервер, где в postgresql.conf стоит настройка TimeZone='Europe/Moscow' (то есть UTC+3). Иными словами, в компании есть договоренность, что все “локальное” время понимается как московское. Пусть на сервере есть таблица data с двумя колонками: created timestamp и created_tz timestamptz.

Однажды к базе подключился клиент Вася из Челябинска, и у него в PG клиенте оказался проставлен TimeZone=UTC+5 (например, забыл поменять пояс на московский). Пусть в момент времени 2023-10-22 18:47:41.962110 +05:00 он решил выполнить запрос

INSERT INTO data(created, created_tz) VALUES (now(), now());

Поскольку now() возвращает timestamptz, при записи в created произойдет конвертация в timestamp в текущей таймзоне сессии. Поэтому в него запишется 18:47, а в created_tz - время в UTC (13:47).

Когда Вася решит сделать SELECT и получить результат обратно, ему вернется

SELECT created, created_tz FROM data;
2023-10-22 18:47:41.962110, 2023-10-22 18:47:41.962110 +05:00

Обратите внимание, что во второй колонке при отображении произошла обратная конвертация из UTC в локальное время.

А теперь предположим, что пришел злой админ Вова, у которого стоит московская таймзона (SET TimeZone=’Europe/Moscow’). И ему нужно понять, во сколько Вася добавил строчку в таблицу. Если он сделает аналогичный SELECT, то получит:

SELECT created, created_tz FROM data;
2023-10-22 18:47:41.962110, 2023-10-22 16:47:41.962110 +03:00

Для created_tz UTC сконвертировалось в локальную таймзону Вовы (московскую), а вот created показывается как есть. Глядя исключительно на это поле, невозможно понять, когда же реально была произведена запись! Информация о таймзоне Васи утеряна, вообще ни разу не очевидно, что клиент находился в челябинском часовом поясе.

Хотите еще проблем — не вопрос! Зоопарк из клиентов PostgreSQL

Выше была рассмотрена ситуация, когда клиенты находятся в разных часовых поясах и из-за использования timestamp without time zone пропадает возможность определить время события.

Но многие тут резонно заметят: у меня и сервер, и все клиенты в одном часовом поясе, зачем мне думать о каких-то таймзонах?

Если бы все было так просто :) То, с чем я столкнулся при работе с разными клиентами PG, меня поразило.

Сценарий, от которого и смешно, и грустно одновременно

Воспроизвести ситуацию очень просто. Возьмем PostgreSQL, у которого таймзона сервера стоит в America/Buenos_Aires (это UTC-3):

docker run --name pgdemo -p 5432:5432 -e POSTGRES_USER=pguser -e POSTGRES_PASSWORD=pgpasswd -e TZ=America/Buenos_Aires -d postgres

Тут мы запустили чистый инстанс базы Postgres с названием pgdemo, а также проставили в контейнере часовой пояс с помощью переменной TZ (https://www.gnu.org/software/libc/manual/html_node/TZ-Variable.html ^[5]). Поскольку в файле postgresql.conf мы ничего явно не задавали, Postgres использует часовой пояс из docker-контейнера.

Также предположим, что к базе мы подсоединяемся из московского часового пояса (UTC+3). Возьмем 3 распространенных клиента:

• старый добрый psql (подключаемся через psql postgresql://pguser@localhost:5432/postgres);

• встроенный клиент в IntelliJ IDEA (также используется в других продуктах Jetbrains: PyCharm, DataGrip и так далее);

• DBeaver (популярный свободный десктопный клиент на основе JDBC).

Создадим таблицу (из любого клиента):

create table person (
  id integer primary key,
  name text not null,
  created timestamp not null default now(),
  created_tz timestamptz not null default now()
);

Выполним из каждого клиента по запросу:

(psql)
insert into person (id, name) values (1, 'Vasya_psql');

(intellij)
insert into person (id, name) values (2, 'Kolya_intellij');

(dbeaver)
insert into person (id, name) values (3, 'Natasha_dbeaver');

Таймстемпы в таком случае будут заполняться текущим временем. Пусть все клиенты физически находятся в московском часовом поясе (UTC+3). Тогда если они выполнят select * from person, то получится:

Обратите внимание - в колонку created (у которой тип timestamp) все клиенты проставили разные значения!

Почему так случилось? Функция now() возвращает текущее время в формате timestamptz. Затем происходит конвертация в timestamp. Результат определяется значением параметра TimeZone в каждой клиентской сессии и каждый клиент заполняет его по-своему. Самая боль в том, что в зависимости от реализации клиента дефолтное значение этого параметра может быть практически каким угодно:

• Для psql это таймзона сервера (в нашем случае UTC-3)

• Для intellij / datagrip — просто UTC (вот тут можно найти объяснение, почему так сделано: https://youtrack.jetbrains.com/issue/DBE-2996 ^[6])

• Для dbeaver — таймзона клиента (UTC+3), как и в целом для большинства JDBC-based клиентов: http://github.com/pgjdbc/pgjdbc/issues/576 ^[7].

В результате 3 клиента, которые физически находятся на одном компьютере, заполняют поле с типом timestamp разными значениями:

Из этого следует, что даже если сервер находился бы в клиентском часовом поясе (UTC+3), как минимум intellij проигнорировал бы это и записал бы время в UTC.

Вывод: даже если у вас и сервер, и все клиенты физически находятся в одном часовом поясе, вы не застрахованы от потери данных при использовании timestamp! Запросто могут найтись клиенты, у которых TimeZone “неожиданный” и есть риск, что в поле с типом timestamp может записаться время в неправильной таймзоне. Конечно, на клиенте дефолтную таймзону можно переопределить на нужную, но это проще простого забыть сделать (да и просто неудобно подобным заниматься).

Напротив, для колонки с типом timestamptz все в порядке. Значение now() напрямую сохраняется в поле без конвертаций и проблем не возникает. Клиенты отображают значение в соответствии со своими настройками (по времени сервера, клиента или в UTC), но во всех случаях верно и с указанием таймзоны.

Как можно было бы решить проблемы?

Самый правильный подход

В подавляющем большинстве случаев стоит отказаться от использования timestamp и перейти на timestamptz.

Это позволит (а) устранить неоднозначность в интерпретации таймстемпов, которые уже есть в базе и (б) избавиться от риска, что клиент подключается с “неожиданной” таймзоной и ломает данные (как выше в случае с Вовой). При этом timestamptz не занимает больше места в памяти, чем timestamp.

Сначала имеет смысл использовать timestamptz для всех новых таблиц, затем (в рамках технического долга) мигрировать существующие таблицы с timestamp на timestamptz.

Альтернативные варианты

Бывает, что в компании исторически сложилось использовать timestamp и понимать под ним время в каком-то выделенном часовом поясе (например, в UTC либо в MSK). Также код, который работает с БД, может иметь сложности с поддержкой timestamptz.

Какие есть альтернативы, если быстро перейти на timestamptz затруднительно?

1. Клиент может генерировать таймстемп сам и передавать его в команде INSERT в явном виде (с часами, минутами и секундами), а не использовать встроенную функции PG now() (и аналогичные, которые возвращают timestamptz). Тогда не будет производиться неявная конвертация из timestamptz в timestamp, поэтому таймзона клиента не будет влиять на результат. Если таймстемп генерируется вручную — то можно пользоваться функцией make_timestamp: INSERT INTO data(created) VALUES (make_timestamp(2023, 10, 22, 9, 30, 0)); Также локальный таймстемп может генерироваться в коде приложения явно (например, через LocalDateTime.now() в Java).

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

2. Если необходимо вычислять текущее время на стороне БД, можно выполнять конвертацию в timestamp явно, используя AT TIME ZONE: INSERT INTO data(created) VALUES (now() AT TIME ZONE 'Europe/Moscow');

   Минус подхода — это требует действий в каждом скрипте, и каждый скриптописатель сам должен следить за тем, какую таймзону указывает (и что она соответствует его актуальной). Забыть указать AT TIME ZONE или указать неправильную таймзону — проще простого. А поскольку после вставки в базу информация о таймзоне теряется, раскопать потом что-то может быть невозможно.

Исключение: когда все же стоит использовать тип данных timestamp (without time zone)?

На практике из любого правила бывают исключения. С timestamp это тоже так — есть сценарий, когда других вариантов по сути нет: когда вам нужно задать какое-то время в неопределенном часовом поясе. Например, время для напоминания в будущем, для будильника или другого действия по расписанию. Чаще всего вам важно, чтобы будильник сработал, условно, в 9 утра по местному времени в какой-то конкретный день, при этом не важно, какой у вас при этом будет часовой пояс. Вы вообще можете уехать в другое место или часовой пояс в вашем городе может законодательно поменяться. В таких случаях наиболее логично применять именно timestamp [without time zone].

Подведем итоги: почему timestamptz предпочтительнее?

• Тип данных timestamptz занимает столько же места в памяти, сколько и timestamp, при этом информации фактически содержит больше

• При использовании now(), current_timestamp, localtimestamp и т.п. результат того, что запишется в базу, не зависит от того, в каком часовом поясе клиент

• Даже если сервер и все клиенты в одном часовом поясе — нет проблем с разным дефолтным значением параметра TimeZone в разных PG-клиентах (например, из-за дефолтного UTC в intellij)

Исключение по сути одно — время с неизвестным / неопределенным часовым поясом (например, в будущем). В таком случае timestamp действительно является оптимальным выбором.

Ссылки для дальнейшего изучения:

https://www.iso.org/iso-8601-date-and-time-format.html ^[8]
https://www.postgresql.org/docs/current/datatype-datetime.html ^[2]
https://www.postgresql.org/docs/current/functions-datetime.html ^[9]
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29 ^[10]
https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ ^[11]