SQL TO_TIMESTAMP: Parsing Strings and Building Timestamps from Unix Epoch

TO_TIMESTAMP в PostgreSQL под одним именем выполняет две совершенно разные задачи: разобрать строку с датой-временем по шаблону формата и собрать момент времени из числа секунд Unix-эпохи. Первую форму берут, когда дата приходит текстом (импорт CSV, поля из внешних систем, нестандартный порядок дня и месяца); вторую — когда время хранится числом bigint из бэкенда или внешнего API. Обе формы возвращают тип timestamptz, и именно этот факт порождает почти всю путаницу с часовыми поясами вокруг функции.

Держите два режима раздельно с самого начала: число секунд задаёт абсолютный момент в UTC однозначно, а строка по шаблону всегда тянет за собой вопрос «в каком она поясе». Если разбор TO_TIMESTAMP влияет на ключ сортировки, биллинг, SLA или момент события, опишите выбранную трактовку пояса рядом с SQL и проверьте её на границах: конец месяца, високосный год, переход на летнее время.

Разбор строки по шаблону

Первый режим TO_TIMESTAMP принимает строку и шаблон формата. Шаблон описывает, как читать входной текст: YYYY — год, MM — месяц, DD — день, HH24 — часы в 24-часовом формате, MI — минуты, SS — секунды. Порядок полей задаёте вы, поэтому одна и та же функция читает и ISO-строку, и европейский формат DD/MM/YYYY.

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS ts;
-- 2024-03-15 14:30:00+00 (with server timezone UTC)

Классическое применение режима шаблона — импорт «грязных» данных, где дата приехала строкой в нестандартном виде, и TO_TIMESTAMP сразу превращает её в нужный тип колонки:

INSERT INTO users (id, email, name, country, created_at)
VALUES (1, 'kate@example.com', 'Kate', 'DE',
        TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI'));

Полезные свойства шаблонного режима:

TO_TIMESTAMP мягче, чем приведение через ::timestamptz: лишние пробелы он обычно прощает.
Результат — всегда timestamptz, привязанный к текущему TimeZone сессии.
Для голой даты без времени есть парный TO_DATE с тем же языком шаблонов.

Сборка времени из Unix-эпохи

Второй режим TO_TIMESTAMP принимает одно число — количество секунд, прошедших с 1970-01-01 00:00:00 UTC. Дробная часть задаёт доли секунды, так что миллисекундный таймстамп тоже разбирается одним вызовом.

SELECT TO_TIMESTAMP(1710513000)      AS from_epoch,
       TO_TIMESTAMP(1710513000.5)    AS with_millis;
-- 2024-03-15 14:30:00+00 | 2024-03-15 14:30:00.5+00

Этот режим выручает, когда время в базе хранится как bigint (типичный формат логов и событий из бэкенда): TO_TIMESTAMP(created_at) превращает числовую колонку в читаемый timestamptz прямо в SELECT:

SELECT id, user_id, amount,
       TO_TIMESTAMP(created_at) AS created_ts
FROM orders
WHERE created_at >= 1704067200;  -- since 2024-01-01 UTC

Ключевой момент: число всегда трактуется как секунды от UTC-эпохи, никакого пояса оно не несёт. Сама точка во времени абсолютна, а вот её текстовое отображение зависит от TimeZone сессии — поэтому здесь, в отличие от строкового режима, неоднозначности с поясом нет.

Часовые пояса: главная ловушка

Обе формы TO_TIMESTAMP возвращают timestamptz. Это значит, что внутри хранится абсолютный момент в UTC, а на экране он показывается в поясе вашей сессии. Тонкость в том, как именно строковый режим выбирает этот UTC-момент: цифры из шаблона он принимает за локальное время сессии. Один и тот же разбор строки даёт разный абсолютный момент при разном SET TIME ZONE.

SET TIME ZONE 'UTC';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00+00

SET TIME ZONE 'Europe/Moscow';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00+03

Ловушка: при разборе строки шаблон не содержит пояса, поэтому числа года-месяца-дня-часа считываются как локальное время сессии, а не как UTC. Под Europe/Moscow те же 14:30 остаются на стене часов 14:30, но получают смещение +03 — а значит, внутри это другой абсолютный момент, чем при UTC. Если строка на самом деле в UTC, выровняйте её явно через AT TIME ZONE 'UTC':

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
         AT TIME ZONE 'UTC' AS naive_utc;

Обратный путь через EXTRACT(EPOCH)

Обратный путь к TO_TIMESTAMP замыкает функция EXTRACT(EPOCH FROM ...): она возвращает число секунд Unix-эпохи из timestamptz. Пара TO_TIMESTAMP и EXTRACT(EPOCH ...) обратима, и это работает независимо от пояса сессии, потому что обе функции оперируют абсолютным UTC-моментом.

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-03-15 14:30:00+00') AS epoch;
-- 1710513000

SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM created_at)) = created_at AS same
FROM orders
LIMIT 1;
-- t

Это удобно для дельт и для хранения времени в числовых API, где timestamptz неудобен. Возраст заказа в часах считается одним вычитанием эпох, без оглядки на пояс:

SELECT id,
       (EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM created_at)) / 3600.0
         AS age_hours
FROM orders
WHERE status = 'paid';

Различия в MySQL и ClickHouse

Имя TO_TIMESTAMP принадлежит миру PostgreSQL и Oracle; в других СУБД обе задачи функции решаются иначе и под другими именами.

MySQL разбирает строку через STR_TO_DATE(str, format) с собственными кодами формата (%Y, %m, %d, %H, %i). Из эпохи момент собирает FROM_UNIXTIME(seconds), обратно — UNIX_TIMESTAMP(ts).

SELECT STR_TO_DATE('2024-03-15 14:30', '%Y-%m-%d %H:%i') AS parsed,
       FROM_UNIXTIME(1710513000)                          AS from_epoch;

ClickHouse использует parseDateTime (синтаксис, похожий на MySQL) и fromUnixTimestamp(seconds); обратно — toUnixTimestamp(ts).

SELECT fromUnixTimestamp(1710513000) AS from_epoch,
       toUnixTimestamp(now())        AS to_epoch;

При переносе между движками две вещи ломаются чаще всего. Первая — коды формата: шаблон 'YYYY-MM-DD HH24:MI' из PostgreSQL не понимает ни STR_TO_DATE, ни parseDateTime, там нужны %Y-%m-%d %H:%i. Вторая — трактовка пояса при разборе строки: TO_TIMESTAMP и FROM_UNIXTIME привязывают результат к поясу сессии, поэтому одна и та же строка на двух серверах с разным TimeZone даст разный абсолютный момент. Перед миграцией прогоните пограничные значения: NULL, пустую строку, отрицательную эпоху (до 1970 года) и дату на переходе летнего времени — именно там движки расходятся.

И помните про EXTRACT(EPOCH FROM column) в фильтрах: обёртка вокруг колонки в WHERE закрывает путь к индексу по этой колонке. Если фильтруете по времени события на большой таблице, сравнивайте саму timestamptz-колонку с границей, которую считает TO_TIMESTAMP от константы, а не оборачивайте колонку, и сверьтесь с планом выполнения.

Запомните разделение, на котором держится вся функция: одно число секунд — это абсолютный UTC-момент, а строка по шаблону всегда тянет за собой вопрос «в каком она поясе». Держите эти два режима TO_TIMESTAMP в голове раздельно, и функция перестанет преподносить сюрпризы.