TO_TIMESTAMP в PostgreSQL: как превратить строку или Unix-время в дату

TO_TIMESTAMP в PostgreSQL — функция с двойным характером. Под одним именем она умеет делать две разные вещи:

1. Разбирать строку с датой и временем по шаблону.
2. Превращать число секунд Unix-эпохи в нормальное время.

Из-за этого новички часто путаются: вроде функция одна, а поведение разное. Особенно много вопросов появляется вокруг часовых поясов, потому что результат обеих форм — тип timestamptz.

Разберём всё спокойно: где использовать каждый режим, почему результат может «съезжать» на несколько часов и как писать запросы так, чтобы время в отчётах, заказах и логах не превращалось в головоломку.

Что делает TO_TIMESTAMP

У функции TO_TIMESTAMP есть два популярных сценария.

Первый — у вас есть строка, например дата из CSV-файла, формы на сайте или внешней системы:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

Второй — у вас есть число секунд с начала Unix-эпохи, то есть с момента 1970-01-01 00:00:00 UTC:

SELECT TO_TIMESTAMP(1710513000) AS created_at;

Оба запроса возвращают значение типа timestamptz, то есть timestamp with time zone.

И вот здесь важно сразу разделить два смысла:

• число секунд Unix-эпохи уже описывает конкретный момент времени;
• строка без часового пояса требует ответа на вопрос: «В каком часовом поясе её читать?»

Именно это различие держит на себе всю тему.

Режим 1. Разбор строки по шаблону

Когда дата приходит текстом, PostgreSQL не всегда может сам понять, где день, где месяц, где часы, а где минуты. Например, строка 15/03/2024 09:05 для человека понятна, но базе нужно объяснить её структуру.

Для этого используется шаблон:

SELECT TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI') AS created_at;

Шаблон говорит PostgreSQL:

• DD — день;
• MM — месяц;
• YYYY — год;
• HH24 — часы в 24-часовом формате;
• MI — минуты.

То есть строка читается не «как получится», а строго по вашей инструкции.

Например, если дата пришла в привычном ISO-виде, шаблон будет другим:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

А если сначала идёт день, потом месяц, потом год — меняется только шаблон:

SELECT TO_TIMESTAMP('15.03.2024 14:30', 'DD.MM.YYYY HH24:MI') AS created_at;

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

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')
);

На входе была строка. В таблицу попало нормальное значение времени.

Основные элементы шаблона

Самые частые элементы формата стоит запомнить сразу:

Элемент	Что означает	Пример
YYYY	год из четырёх цифр	2024
MM	месяц	03
DD	день месяца	15
HH24	час от 0 до 23	14
MI	минуты	30
SS	секунды	45

Например:

SELECT TO_TIMESTAMP('2024-03-15 14:30:45', 'YYYY-MM-DD HH24:MI:SS') AS created_at;

Если в строке нет секунд, не добавляйте SS в шаблон:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

Шаблон должен соответствовать строке. Не идеально «по красоте», а именно по смыслу: где год, где месяц, где день, где время.

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

Второй режим проще: TO_TIMESTAMP получает одно число.

Это число — количество секунд, прошедших с 1970-01-01 00:00:00 UTC.

SELECT TO_TIMESTAMP(1710513000) AS created_at;

Если нужна дробная часть секунды, её тоже можно передать:

SELECT
    TO_TIMESTAMP(1710513000) AS from_epoch,
    TO_TIMESTAMP(1710513000.5) AS with_fraction;

Такой формат часто встречается в логах, событиях, аналитике и API. Например, бэкенд может хранить время создания заказа как bigint:

SELECT
    id,
    user_id,
    amount,
    TO_TIMESTAMP(created_at) AS created_at_ts
FROM orders;

В таблице лежит число вроде 1710513000, а в результате запроса вы видите нормальную дату и время.

Секунды, миллисекунды и типичная ошибка

Очень частая ошибка — перепутать секунды и миллисекунды.

Unix-время в секундах выглядит примерно так:

SELECT TO_TIMESTAMP(1710513000) AS created_at;

А Unix-время в миллисекундах выглядит примерно так:

SELECT 1710513000000 AS created_at_ms;

Если такое значение напрямую передать в TO_TIMESTAMP, получится дата где-то далеко в будущем. Поэтому миллисекунды нужно сначала разделить на 1000.0:

SELECT TO_TIMESTAMP(1710513000000 / 1000.0) AS created_at;

Обратите внимание на 1000.0, а не просто 1000. Так мы явно показываем, что хотим сохранить дробную часть, если она есть.

На практике это выглядит так:

SELECT
    id,
    TO_TIMESTAMP(created_at_ms / 1000.0) AS created_at
FROM events;

Если данные пришли из JavaScript, внешнего API или мобильного приложения, обязательно проверьте: там секунды или миллисекунды.

Главная ловушка: часовые пояса

Теперь самая важная часть.

Обе формы TO_TIMESTAMP возвращают timestamptz. Это не значит, что PostgreSQL хранит внутри строку с часовым поясом вроде +03 или +00. Смысл другой: PostgreSQL хранит конкретный момент времени, а показывает его в часовом поясе текущей сессии.

Посмотрим на пример:

SET TIME ZONE 'UTC';

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

Результат будет отображаться как время в UTC:

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') AS created_at;

Теперь результат будет выглядеть так:

2024-03-15 14:30:00+03

На первый взгляд кажется: «Ну и что? Время же то же самое — 14:30».

Но это не тот же самый абсолютный момент.

В первом случае 14:30+00 — это 14:30 по UTC.

Во втором случае 14:30+03 — это 11:30 по UTC.

То есть одна и та же строка без часового пояса может превратиться в разные моменты времени, если сессия работает в разных TimeZone.

Почему строка опаснее числа

С числом Unix-эпохи всё однозначно:

SELECT TO_TIMESTAMP(1710513000) AS created_at;

Число 1710513000 уже означает конкретный момент времени. PostgreSQL может показать его в разных часовых поясах, но сам момент не меняется.

А вот строка без часового пояса неоднозначна:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

Здесь PostgreSQL вынужден решить: 14:30 — это где? В UTC? В Москве? В Берлине? В Сингапуре?

Если в самой строке нет ответа, PostgreSQL берёт часовой пояс текущей сессии.

Поэтому правило простое:

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

Как безопасно разбирать строки с временем

Если входная строка действительно означает локальное время сервера или текущей сессии, можно использовать TO_TIMESTAMP напрямую:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

Но если вы импортируете важные данные — заказы, платежи, события, SLA, биллинг — лучше не надеяться на случайный TimeZone.

Перед импортом можно явно выставить часовой пояс сессии:

SET TIME ZONE 'UTC';

INSERT INTO events (id, created_at)
VALUES (
    1,
    TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
);

Так вы явно говорите: строки без часового пояса читаем как UTC.

Если строки приходят из конкретного региона, например из московской системы, можно сделать так:

SET TIME ZONE 'Europe/Moscow';

INSERT INTO events (id, created_at)
VALUES (
    1,
    TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
);

Главное — не оставлять это решение неявным.

Плохая ситуация: на локальной машине у разработчика один часовой пояс, на сервере другой, в CI третий, а импорт данных везде запускается одним и тем же SQL. В результате даты выглядят «почти правильно», но события оказываются сдвинуты на несколько часов.

Если в строке есть часовой пояс

Лучший вариант — когда внешний источник передаёт дату сразу со смещением:

SELECT TO_TIMESTAMP('2024-03-15 14:30 +00', 'YYYY-MM-DD HH24:MI TZH') AS created_at;

Или, например, с московским смещением:

SELECT TO_TIMESTAMP('2024-03-15 14:30 +03', 'YYYY-MM-DD HH24:MI TZH') AS created_at;

Так строка сама говорит, как её читать. PostgreSQL уже не нужно угадывать часовой пояс по настройкам сессии.

Для обмена между системами это самый спокойный вариант: передавать время либо в Unix-секундах, либо в строке с явным часовым поясом.

Обратный путь: EXTRACT(EPOCH FROM ...)

Если TO_TIMESTAMP превращает Unix-секунды в дату, то обратную операцию делает EXTRACT(EPOCH FROM ...).

Например:

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

Результат:

1710513000.000000

Это удобно, когда нужно отдать время во внешний API или посчитать разницу между моментами в секундах.

Например, возраст заказа в часах:

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

Здесь мы берём текущее время, вычитаем время создания заказа и делим разницу на 3600.0, потому что в одном часе 3600 секунд.

Для timestamptz эта логика хорошо работает: мы считаем разницу между абсолютными моментами времени, а не между красивыми строками на экране.

TO_TIMESTAMP и фильтры по датам

Допустим, в таблице orders время хранится числом Unix-эпохи:

SELECT
    id,
    TO_TIMESTAMP(created_at) AS created_at
FROM orders
WHERE created_at >= 1704067200;

Такой фильтр нормальный, потому что сравнивается сама числовая колонка.

А если в таблице уже есть колонка типа timestamptz, например created_at, лучше фильтровать её как дату:

SELECT
    id,
    user_id,
    amount,
    created_at
FROM orders
WHERE created_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00';

Не стоит без необходимости делать так:

SELECT
    id,
    user_id,
    amount,
    created_at
FROM orders
WHERE EXTRACT(EPOCH FROM created_at) >= 1704067200;

Почему? Потому что вы оборачиваете колонку created_at в функцию. На большой таблице это может помешать PostgreSQL эффективно использовать обычный индекс по created_at.

Хороший принцип: функцию лучше применять к константе, а не к колонке.

Например:

SELECT
    id,
    user_id,
    amount,
    created_at
FROM orders
WHERE created_at >= TO_TIMESTAMP(1704067200);

Так колонка остаётся «чистой», и у оптимизатора больше шансов использовать индекс.

TO_TIMESTAMP или TO_DATE

Если вам нужна только дата без времени, часто лучше использовать TO_DATE.

Например:

SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY') AS created_date;

TO_DATE возвращает тип date.

А TO_TIMESTAMP возвращает дату и время:

SELECT TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI') AS created_at;

Простое правило:

• нужна только календарная дата — берите TO_DATE;
• нужны дата и время — берите TO_TIMESTAMP;
• есть Unix-секунды — тоже берите TO_TIMESTAMP.

Отличия от MySQL

В MySQL похожие задачи решаются другими функциями.

Чтобы разобрать строку по шаблону, используется STR_TO_DATE:

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

Чтобы получить дату из Unix-секунд, используется FROM_UNIXTIME:

SELECT FROM_UNIXTIME(1710513000) AS created_at;

Чтобы сделать обратную операцию, есть UNIX_TIMESTAMP:

SELECT UNIX_TIMESTAMP('2024-03-15 14:30:00') AS epoch;

Главное отличие для новичка — другие коды формата.

В PostgreSQL:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS created_at;

В MySQL:

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

Шаблон YYYY-MM-DD HH24:MI из PostgreSQL нельзя просто перенести в MySQL. Там нужны %Y, %m, %d, %H, %i.

Отличия от ClickHouse

В ClickHouse для Unix-времени используются функции вроде fromUnixTimestamp и toUnixTimestamp.

SELECT fromUnixTimestamp(1710513000) AS created_at;

Обратный путь:

SELECT toUnixTimestamp(now()) AS epoch;

Для разбора строк в ClickHouse есть свои функции семейства parseDateTime.

Общая идея похожа, но названия функций, шаблоны и поведение на пограничных случаях отличаются. Поэтому при переносе SQL между PostgreSQL, MySQL и ClickHouse особенно внимательно проверяйте:

• формат шаблона;
• часовой пояс сессии;
• секунды или миллисекунды на входе;
• NULL;
• пустые строки;
• даты до 1970 года;
• даты на переходах летнего и зимнего времени.

Именно на таких значениях чаще всего всплывают неприятные отличия.

Главное

TO_TIMESTAMP в PostgreSQL нужно воспринимать как две функции под одним именем.

Если передаёте строку и шаблон, PostgreSQL разбирает текстовую дату:

SELECT TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI') AS created_at;

Если передаёте число, PostgreSQL считает его секундами Unix-эпохи:

SELECT TO_TIMESTAMP(1710513000) AS created_at;

Самое важное различие такое:

• Unix-секунды задают абсолютный момент времени;
• строка без часового пояса читается в часовом поясе текущей сессии.

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

Хорошая привычка: рядом с SQL всегда явно понимать, в каком часовом поясе живут входные данные. Тогда TO_TIMESTAMP будет не источником странных сдвигов на три часа, а полезным инструментом для чистого и понятного времени в базе.