Postgres AT TIME ZONE 'UTC' делает не то, что вы думаете
Footguns with Postgres "at time zone 'UTC'"
В Postgres выражение AT TIME ZONE 'UTC' не просто переводит время в UTC, а меняет тип данных с timestamptz на timestamp без часового пояса. Это приводит к неочевидным ошибкам: сравнение timestamp и timestamptz всегда ложно, а добавление интервала вроде '1 month' зависит от часового пояса сессии. Автор показывает, как исправить такие запросы, применяя AT TIME ZONE 'UTC' дважды — до и после арифметики с датами.
Сравнение timestamp и timestamptz всегда даст false. Это логично, ведь timestamp не является реальной точкой во времени — такое сравнение технически абсурдно.
- heurekamala
В тексте сказано: "Сравнение timestamp и timestamptz всегда даст false."
Это неверно. Postgres очень тихо в фоне преобразует timestamp в timestamptz.
При этом он использует часовой пояс сессии.
Будет ли результат true или false, зависит от настройки TimeZone. Это хуже, чем "всегда false".
В продакшене с UTC это работает. На ноутбуке разработчика из Калифорнии — нет.
Только что проверил:
SET TIME ZONE 'America/Los_Angeles';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* f */
SET TIME ZONE 'UTC';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* t */
Если можете, используйте Postgres 16.
Двойной AT TIME ZONE 'UTC' из текста там не нужен. Там есть date_add с часовым поясом в качестве третьего аргумента:
date_add(b.month_start, interval '1 month', 'UTC')
Это добавляет месяц в UTC, и результат остаётся timestamptz.
- ulrikrasmussen
Стандарт SQL, к сожалению, действительно ужасен в обращении со временем. Тип `timestamp` — вовсе не timestamp, потому что он не кодирует уникальный момент времени, он просто хранит дату и время, которые нужно интерпретировать относительно часового пояса. Его следовало бы назвать "datetime".
Перенос Java Instant туда-сюда между базой данных — тоже на удивление сложная задача, которую трудно сделать правильно, и не помогает то, что JDBC обрабатывает это совершенно неправильно, если вы используете его методы setTimestamp/getTimestamp. Не потому, что это плохой дизайн с подводными камнями, а потому, что реализация просто-напросто неверна и испортит ваши данные, если вы имеете дело с instant, чья календарная дата достаточно далеко в прошлом, из-за использования устаревшего API даты/времени, который переключается на григорианский календарь для дат в прошлом.
Название `timestamp with time zone` тоже вводит в заблуждение, потому что на самом деле он не хранит часовой пояс, он хранит количество секунд с эпохи, как java.time.Instant (хотя с другим разрешением). Часть "with time zone" относится только к текстовому формату, в котором вы обозначаете значения, включающему часовой пояс после даты/времени для однозначной идентификации момента времени, но часовой пояс отбрасывается и не сохраняется после разбора значения. Это отличается, например, от `ZonedDateTime` в Java, который действительно хранит смещение и поэтому соответствует паре (Instant, TimeZone).
- 1a527dd5
Моя библия: https://wiki.postgresql.org/wiki/Don't_Do_This
- Macha
Мой опыт таков (несмотря на совет разработчиков Postgres использовать его): timestamptz — по большей части бесполезный тип данных, поскольку это всего лишь обёртка вокруг преобразования в UTC.
- Храните прошедшие события? Просто храните их в UTC. Может, вы используете timestamptz, чтобы он сделал это за вас, но на самом деле это более громоздкий интерфейс для этого, чем timestamp
- Храните будущие времена в UTC? Отлично, просто используйте UTC, см. выше
- Храните будущие человеческие времена? Тогда timestamptz активно вреден, потому что он нетерпеливо преобразует в UTC, так что даже если вы получите обновлённую tzdb вовремя к моменту наступления события, вы не знаете, что было при записи, и теперь ваш datetime неоднозначен. Менее сломанный подход — использовать обычный timestamp + строковый столбец часового пояса (если нужно сортировать по нему, возможно, ещё и денормализованный столбец _utc, с пониманием, что вам придётся его перегенерировать или принять небольшие ошибки на единицу при обновлении tzdb, но по крайней мере вы можете сделать это, когда знаете, каким было входное значение, в отличие от timestamptz)
- layer8
> Добавление месяца с помощью + INTERVAL '1 months' зависит от часового пояса. […]
Добавление месяцев в любом случае плохо определено, даже при использовании date, для дней месяца > 28. Я считаю ошибкой, что системы в общем случае позволяют такое вычисление (в отличие от кода приложения, реализующего бизнес-правила конкретной предметной области).