timestamptz vs timestamp in SQL: storing time correctly

timestamptz в PostgreSQL хранит абсолютный момент времени как UTC и применяет часовой пояс сессии только при выводе — именно его берут для событий, аудита и любых меток «когда это случилось». Его сосед timestamp (без зоны) хранит просто число на стене без привязки к поясу. Выбор между ними определяет, совпадут ли ваши данные у пользователей из разных часовых поясов или тихо разъедутся. Разберём практично.

На уровне SQL разница в одной букве z оборачивается разной семантикой: timestamptz фиксирует точку на оси времени, а timestamp — показания циферблата, к которым ещё надо угадать зону. Поэтому первый вопрос при выборе типа — не «как хранить», а «храню ли я абсолютный момент или зоно-независимое локальное время». От ответа зависит, можно ли сравнивать две метки напрямую, корректно ли отработают диапазоны в WHERE и не сломается ли группировка по дню при переходе на летнее время.

Момент времени против стенных часов

timestamptz (он же timestamp with time zone) хранит абсолютный момент: внутри это UTC, а зона сессии применяется только при выводе. timestamp (без зоны) — это «наивные стенные часы»: число 2024-03-15 10:00 без ответа на вопрос «10 утра где именно».

SET timezone = 'UTC';
SELECT
  now()::timestamptz                    AS instant,   -- absolute, stored as UTC
  '2024-03-15 10:00'::timestamp         AS wall_time; -- naive, no zone

Один и тот же timestamptz покажется по-разному в разных сессиях, но это один момент:

SET timezone = 'America/New_York';
SELECT '2024-03-15 10:00+00'::timestamptz;  -- shows 2024-03-15 06:00:00-04

SET timezone = 'Europe/Moscow';
SELECT '2024-03-15 10:00+00'::timestamptz;  -- shows 2024-03-15 13:00:00+03

Почему события — это почти всегда timestamptz

Любое поле вида «когда это случилось» — регистрация, оплата, лог — это момент истории. Если хранить его в timestamp, вы теряете зону, и две записи из Лиссабона и Москвы становятся несравнимыми.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text,
  country    text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         bigserial PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  amount     numeric(12,2) NOT NULL,
  status     text NOT NULL DEFAULT 'new',
  created_at timestamptz NOT NULL DEFAULT now()
);

Запросы по диапазону тогда корректны независимо от зоны сервера:

SELECT count(*)
FROM orders
WHERE created_at >= '2024-03-01 00:00+00'
  AND created_at <  '2024-04-01 00:00+00';

timestamp без зоны оправдан редко: «дата дня рождения», «время открытия магазина 09:00» — то, что одинаково в любой зоне и не описывает конкретный момент.

Тихий баг при смешивании типов

Самая опасная ловушка — присвоить «голую» строку без зоны колонке timestamptz. PostgreSQL не ошибётся: он молча достроит зону из текущей сессии. Поменяется сессия — поменяется смысл данных.

SET timezone = 'America/New_York';
INSERT INTO orders (user_id, amount, created_at)
VALUES (1, 50.00, '2024-03-15 10:00');   -- interpreted as 10:00 New York

SET timezone = 'UTC';
INSERT INTO orders (user_id, amount, created_at)
VALUES (1, 50.00, '2024-03-15 10:00');   -- interpreted as 10:00 UTC

Две строки выглядят одинаково, но это разные моменты с разницей в часы.

Гоча: каст timestamptz::timestamp (и наоборот) не конвертирует часы — он просто срезает или достраивает зону по правилам сессии. Сравнение timestamptz с timestamp тоже тихо приводит один к другому через зону сессии. Всегда задавайте зону явно в литерале ('... +00') или фиксируйте SET timezone = 'UTC'.

Хранить в UTC, выводить в зоне клиента

Рабочее правило: храните всё в timestamptz, а конвертацию для отчёта делайте оператором AT TIME ZONE на выводе.

SELECT
  id,
  created_at,                                  -- instant (UTC under the hood)
  created_at AT TIME ZONE 'Europe/Berlin' AS berlin_wall_time
FROM orders
ORDER BY created_at DESC
LIMIT 5;

AT TIME ZONE — это переключатель типа:

• timestamptz AT TIME ZONE 'zone' даёт timestamp (стенные часы в этой зоне);
• timestamp AT TIME ZONE 'zone' даёт timestamptz (трактует наивное время как локальное для зоны).

Группировка по «локальному дню» сотрудника делается так:

SELECT
  e.dept,
  date_trunc('day', o.created_at AT TIME ZONE 'Europe/Berlin') AS local_day,
  sum(o.amount) AS revenue
FROM orders o
JOIN employees e ON e.id = o.user_id
GROUP BY e.dept, local_day
ORDER BY local_day;

Схема employees(id, name, manager_id, dept, salary) здесь даёт привязку заказа к команде.

Отличия в MySQL и ClickHouse

• MySQL: тип TIMESTAMP хранит UTC и конвертирует по @@session.time_zone — это ближе к timestamptz, но диапазон ограничен 1970–2038. Тип DATETIME зону не хранит вообще (аналог наивного timestamp). Для событий обычно берут TIMESTAMP или DATETIME плюс отдельную колонку зоны.
• ClickHouse: DateTime хранит Unix-время (UTC) и может нести имя зоны как параметр типа — DateTime('Europe/Berlin'); зона влияет на парсинг и отображение, не на хранимое значение. Для долей секунды используйте DateTime64.

Главные грабли с timestamptz лежат не в самой функции, а в крайних случаях: наивный литерал без зоны, попавший в колонку с зоной; группировка по локальному дню в момент перехода на DST; ошибочное предположение, что в timestamptz хранится имя зоны, а не точка на оси UTC. Перед миграцией между PostgreSQL, MySQL и ClickHouse прогоните пограничные даты — конец месяца, переход DST, начало эпохи Unix 1970 и предел 2038 для TIMESTAMP — чтобы поймать расхождение в трактовке зоны, а не на «счастливом пути».

Итог: события — в timestamptz, наивный timestamp — только для зоно-независимых данных, конвертация — через AT TIME ZONE на выводе, а зону в литералах задавайте явно, чтобы не словить тихий сдвиг.