Почему вам стоит отказаться от использования 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), в которой хранится актуальное соответствие географических идентификаторов часовым поясам. Идентификаторы, например, бывают такими: America/Buenos_Aires, Europe/Paris, Europe/Moscow и т. д. Каждому идентификатору соответствует набор правил, по которым можно вычислить смещение от GMT на какую-то дату. И когда какой-то город переходит в другой часовой пояс, в базу вносятся изменения.

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

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

Здесь мы рассмотрим 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), а при отображении вычисляется итоговое время на основании “текущей таймзоны сессии”. Обратимся к документации постгри:

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

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

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 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). Поскольку в файле 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)

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

В результате 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
https://www.postgresql.org/docs/current/datatype-datetime.html
https://www.postgresql.org/docs/current/functions-datetime.html
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/