Меня зовут Фролков Иван, я работаю программистом с 1993 года, и уже восемь лет — в Postgres Professional. Периодически выступаю на конференциях. В этой статье я расскажу вам про темпоральные типы данных в PostgreSQL — доклад о них я читал на PGConf.Russia 2022. Почему меня это заинтересовало? Мне много раз приходилось сталкиваться с тем, что из-за разницы часовых поясов не сходились отчёты за месяц или даже за сутки. Подобные проблемы возникают из-за неаккуратной обработки даты и времени, которой можно избежать.

В чём проблема?

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

У меня была ситуация, когда я работал в международной компании с серверами по всему миру. Паника из-за неверного построения отчётов там возникала дважды в сутки. Сначала поднимались московские менеджеры и ругались, что цифры получаются не те. Мы поправляли часовые пояса, и всё было хорошо до тех пор, пока не просыпались менеджеры в Сан-Франциско. Они тоже выдвигали претензии по цифрам, мы снова исправляли время, но после этого опять «уезжала» Москва.

Заря приходит с востока

С чем же связаны такие проблемы? С тем, что Земля круглая, и время наступает везде по-разному. Казалось бы, это тривиальное знание, но в реальности его мало кто учитывает. На востоке часовые пояса с плюсом, а на западе — с минусом. Где-то посередине располагается Гринвич, нулевой меридиан (кстати, в Лондоне есть летнее время, и оно не совпадает с гринвичским!). Есть ещё места вроде Непала и Бутана, где время сдвигается не на полные часы, а на 45 или 15 минут, и это может создать вам проблемы.

Postgres «знает» о существовании часовых поясов — там есть особая база, где эта информация хранится. Данные о часовых поясах там периодически обновляются. Если задействовать эту внутреннюю базу Postgres, количество проблем сократится. Они точно будут, но их станет меньше.

Это современная карта часовых поясов в мире:

Как видно из карты, в Китае время везде пекинское. РЖД, насколько мы знаем, пользуется московским временем, что тоже очень разумно, ведь отдельные области России меняли часовые пояса по несколько раз.

Как справиться с тем, что планета круглая?

Мы можем использовать единое время. Исторически сложилось, что это не пулковский меридиан, а гринвичский, поэтому мы используем универсальное координированное время, UTC.

Какие подходы можно использовать в связи с этим? Первый и самый простой — взять unixtime и хранить его в типе bigint, или просто int. Это вполне рабочий подход, но, к сожалению, у него есть свои недостатки.

Во-первых, вместо лет, месяцев и дней у нас получается просто число — миллиард с чем-то секунд, а пользователи к этому не привыкли.

Во-вторых, если надо прибавить час, проблем не возникает — прибавляем 3600 секунд. Сутки тоже несложно — это 86400 секунд. А если нам нужно прибавить 10 рабочих дней или год, уже возникают трудности. А если к 31 декабря прибавить 2 месяца, то какое у нас будет число? Может получиться и 1 марта. При использовании базы Postgres получится 28 февраля. Похожая ситуация и с кварталами; формулировка «отчет за месяц» может выливаться в витиеватые условия вроде «с первого рабочего понедельника месяца до первого рабочего понедельника следующего месяца» или «с 10 числа отчетного месяца по 10 число следующего». Разумеется, с unixtime это все реализуемо, но не сказать, чтобы слишком удобно. Впрочем, для учета рабочих/нерабочих дней усилия прилагать придется в любом случае.

В-третьих, можно использовать типы данных самой СУБД, в PostgreSQL это TIMESTAMP или TIMESTAMPTZ— их используют чаще всего, и в основном в данной статье я буду говорить о них.

Какие типы данных для работы со временем есть в Postgres?

DATE — может использоваться, к примеру, для даты рождения.

TIME — для обозначения момента начала чего-либо.

Есть тип TIME WITH TIMEZONE, он должен быть по стандарту, но не рекомендован к использованию. Возможно, у него есть свои сценарии использования — например, для работы магазинов или ресторанов, но мне не доводилось иметь с ним дело. Впрочем, с ресторанами, открывающимся в полдень и закрывающимся в два ночи, все будет, скорее всего, не так просто.

Есть TIMESTAMP или TIMESTAMP WITHOUT TIMEZONE, время «вообще».

TIMESTAMP WITH TIMEZONE, сокращённо TIMESTAMPTZ, это время с часовым поясом, приведённое к UTC.

Наконец, есть тип INTERVAL для обозначения времени между событиями с достаточно неудобным, на мой взгляд, синтаксисом (он стандартный, но есть нестандартные функции).

TIMESTAMP и TIMESTAMPTZ

Итак, у нас есть TIMESTAMP и TIMESTAMPTZ.

TIMESTAMP — это просто 8 байт, в которых хранится некое время с точностью до микросекунды.

TIMESTAMPTZ— это те же 8 байт, в которых хранится время, приведенное к UTC. Параметр сессий timezone используется для преобразования из литерала в значение UTC и отображения. При преобразовании литерала с указанным часовым поясом в UTC информация о часовом поясе может быть отброшена, и тогда цифры в отчётах будут не те, если есть хотя бы час разницы.

Разница между типами TIMESTAMP и TIMESTAMPTZ минимальная. Её суть в том, использует ли тип данных часовой пояс при сохранении и отображении сессии, либо не использует.

Что если мы не можем контролировать часовой пояс сессии? Для таких случаев есть оператор timestamp AT TIME ZONE (о нём ниже) и функция timezone(tz,ts), которые позволяют преобразовывать часовые пояса туда и обратно.

Функции make_timestamptz() и now()

На мой взгляд, лучше явно использовать функцию make_timestamptz( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ), тогда вы получите в отчётах нужный результат. Если вы пишете на JAVA, как я, то в сложных функциях надо обязательно указывать часовой пояс, и это будет правильно — тогда станет понятно, где именно происходит действие, по какому времени.

Если вы получаете данные из какого-то внешнего источника, то надо использовать тот тип, который использует источник. Приходит к вам оттуда TIMESTAMP without time zone – ну что же, его и используйте. Вызывайте функцию now() для получения текущего времени – она возвращает TIMESTAMPTZ, и вы, не мудря, храните её значение в TIMESTAMPTZ.

Какие часовые пояса бывают и как они обозначаются?

Иногда именами собственными — Europe/Moscow, America/Chicago, Africa/Cairo и т.д. Postgres «понимает» такую форму, умеет определять зимнее и летнее время, с этим всё хорошо.

Иногда удобнее использовать сокращения — MSK, PST, и т.д.

Также можно использовать цифру — смещение относительно UTC, но тогда будут проблемы с зимним и летним временем; впрочем, для них тоже есть достаточно неудобоваримый синтаксис, гурманы могут попробовать.

TIMESTAMP или TIMESTAMPTZ?

Если у вас всё происходит в пределах одного сервера, можно обойтись TIMESTAMP, правда, тогда надо пользоваться не now() или current_timestamp, а localtimestamp (не могу не отметить очаровательную непоследовательность в использовании нижнего подчеркивания. Когда в следующий раз обнаружите, что допустили разнобой в именовании, вспомните об этой милой особенности Postgres и, может быть, вам станет чуточку легче!)

Если вы храните и обрабатываете данные распределённой системы в пределах России или мира, TIMESTAMP уже недостаточно, лучше использовать TIMESTAMPTZ.

С опытом приходит понимание того, что надо использовать оба этих типа, но в разных случаях. Всегда нужно помнить про главное отличие TIMESTAMP от TIMESTAMPTZ: при передаче на клиента и обратно происходит преобразование в/из UTC в соответствии с часовым поясом сессии.

$ psql -c "set time zone 'UTC'; select now()"
SET              
now              
-------------------------------
2023-10-12 10:03:05.469559+00
(1 строка)

$ psql -c "select now()"
now
-------------------------------
2023-10-12 13:03:25.853661+03
(1 строка)

По объёму это все те же 8 байт.  В перспективе было бы неплохо иметь особый тип данных TIMESTAMP с включённым часовым поясом, но пока его не сделали.

AT TIME ZONE

Оператор AT TIME ZONE может преобразовывать TIMESTAMP без указания часового пояса в TIMESTAMP с указанием часового пояса и наоборот. Он работает очень изящно — TIMESTAMP преобразует в TIMESTAMPTZ, а TIMESTAMPTZ— в TIMESTAMP, и через пару таких преобразований ум заходит за разум.

work=# select now(), now()::timestamp, now() at time zone 'UTC';
              now              |            now             |          timezone          
-------------------------------+----------------------------+----------------------------
 2023-10-12 13:05:41.478308+03 | 2023-10-12 13:05:41.478308 | 2023-10-12 10:05:41.478308
(1 строка)

Допустим, вы осуществили преобразование. Если в вашем отчёте по-прежнему какие-то 300 рублей уходят то в плюс, то в минус, причём и сама денежная сумма постоянно меняется, а вы забыли изменить часовой пояс сессии, где это происходит, лучше прерваться и взять паузу на несколько часов. Тем не менее, про оператор AT TIME ZONE полезно помнить.

При использовании часовых поясов мы сохраняем то, что получили, и отображаем именно это. Берём часовой пояс сессии и кладём его в базу. При чтении из базы мы берём часовой пояс сессии и отображаем клиенту правильное время.

О чём ещё стоит помнить, работая со временем в PostgreSQL?

– CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, transaction_timestamp() возвращают TIMESTAMPTZ на момент начала транзакции (а транзакции бывают и долгими), например:

if@work=#* select n, now(), statement_timestamp(), clock_timestamp() from generate_series(1,2) as n where  pg_sleep(0.1)::text='' order by 1 desc limit 1;
 n |              now              |      statement_timestamp      |        clock_timestamp        
---+-------------------------------+-------------------------------+-------------------------------
 2 | 2023-09-12 12:21:55.513476+03 | 2023-09-12 12:22:01.808398+03 | 2023-09-12 12:22:02.028594+03
(1 row)

А вот LOCALTIME, LOCALTIMESTAMP на выходе дают просто TIMESTAMP.

Функция now() возвращает TIMESTAMPTZ, как уже отмечалось выше.

Функция clock_timestamp() возвращает TIMESTAMPTZ и показывает истинное время, но, к сожалению, требует syscall, что является достаточно дорогой операцией.

Ещё есть statement_timestamp() возвращающий TIMESTAMPTZ и отображающий время начала выполнения запроса (он также может длиться несколько суток, поэтому стоит быть аккуратнее).

Таким образом, мы можем получить три времени — время на момент вызова функции, время начала транзакции и время начала выполнения запроса.

Не только TIMESTAMP

Как уже было сказано выше, у нас в Postgres есть тип DATE, который в 4-х байтах хранит просто дату без указания часового пояса. Это год-месяц-день, 2022-02-02 и т.п. Есть удобная функция, которая возвращает дату — make_date(year int,month int,day int), она более гибкая и читабельная, в отличие от стандартного синтаксиса. Лучше пользоваться ей и другими подобными функциями.

У типа INTERVAL синтаксис тоже не очень, лучше использовать функцию make_interval().

Интервалы можно прибавлять и вычитать, это прекрасно описано в документации.

Джавистам на заметку

Небольшое дополнение от меня как JAVA-разработчика. На JAVA тип TIMESTAMP прекрасно отображается в java.time.LocalDateTime, а тип TIMESTAMPTZ— в java.time.OffsetDateTime.

А вот тип INTERVAL в java.time.Duration не отображается, надо писать вручную:

var i = (org.postgresql.util.PGInterval) rs.getObject("interval_column");

и потом уже преобразовывать в java.time.Duration.

Выводы

Если вы должны выдавать некий общий отчёт по данным из нескольких локаций, часовые пояса нужно указывать явно. Отслеживание активности пользователя со странным часовым поясом непонятных островов может служить дополнительной защитой для ваших данных. Также советую посмотреть видео моего доклада — там есть ответы на вопросы, которые могут быть актуальны и для вашего случая. Видео: https://pgconf.ru/2022/316344#video