TO_DATE in PostgreSQL: parsing a string into a date by template

Функция TO_DATE в PostgreSQL парсит текстовую строку в значение типа date, сопоставляя её с шаблоном формата, который вы задаёте сами. Она нужна, когда дата приходит строкой не в ISO-формате: простой каст '15/03/2024'::date на такой строке либо упадёт, либо поймёт её не так, как вы ждёте. Чаще всего TO_DATE встречается при импорте CSV, миграциях и разборе ручных выгрузок, где порядок дня, месяца и года известен заранее и его надо зафиксировать прямо в запросе.

Дальше разберём базовый синтаксис шаблонов, почему TO_DATE надёжнее каста ::date, главную ловушку — нестрогий разбор и его поведение в разных версиях PostgreSQL, правило двузначного года и разницу с TO_TIMESTAMP, когда в строке есть время.

Базовый синтаксис

TO_DATE принимает два аргумента: входную строку и шаблон формата, описывающий, как эта строка устроена.

-- Input string, then the format template that describes it
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d;
-- result: 2024-03-15

Шаблон собирается из кодов полей: YYYY — год из четырёх цифр, MM — месяц, DD — день, Mon — короткое имя месяца, Month — полное. Литералы вроде дефисов и слешей пишутся как есть:

SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY')   AS eu_style,
       TO_DATE('March 15, 2024', 'Month DD, YYYY') AS verbose,
       TO_DATE('20240315', 'YYYYMMDD')        AS compact;

Почему не просто ::date

Каст ::date полагается на настройку datestyle сервера и понимает в основном ISO-подобные строки. Для неоднозначных форматов он опасен:

-- Ambiguous: is this March 4 or April 3?
SELECT '03/04/2024'::date;        -- depends on server datestyle
SELECT TO_DATE('03/04/2024', 'MM/DD/YYYY') AS as_us;  -- explicit: March 4
SELECT TO_DATE('03/04/2024', 'DD/MM/YYYY') AS as_eu;  -- explicit: April 3

С TO_DATE намерение записано в самом запросе и не зависит от настроек сервера. Практический пример — загрузка дат рождения пользователей, пришедших в европейском формате:

-- Imagine a staging table with raw text dates
SELECT id, email, TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_day
FROM users_staging;

Преимущества явного шаблона:

• результат не зависит от datestyle и локали сервера;
• запрос самодокументируется: видно, какой формат ожидается;
• одинаково работает для US, EU и любых кастомных раскладок.

Главный подвох: нестрогий парсинг

Важнейшая деталь TO_DATE — её строгость зависит от версии PostgreSQL, и именно здесь чаще всего ломаются миграции. В комментариях ниже показано старое поведение до PostgreSQL 16: месяц 13 и день 32 не вызывали ошибку, а «переносились» вперёд (месяц 13 становился январём следующего года, день 32 — первым числом следующего месяца). Это и была главная причина тихих багов: невалидная дата молча превращалась в валидную.

-- 13 is not a month, but TO_DATE happily rolls it over
SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD');  -- yields 2025-01-01

-- Day 32 also rolls forward instead of erroring
SELECT TO_DATE('2024-01-32', 'YYYY-MM-DD');  -- yields 2024-02-01

-- Separators in data and template can even mismatch silently
SELECT TO_DATE('2024/03/15', 'YYYY-MM-DD');  -- still parses

Начиная с PostgreSQL 16, поведение изменили: первые два запроса теперь не «переносят» лишнее, а падают с ошибкой вида date/time field value out of range для месяца 13 и date out of range для дня 32 — значения 2025-01-01 и 2024-02-01 из комментариев получаются только на PostgreSQL 15 и старше. А вот третий запрос лоялен в любой версии: расхождение разделителей в данных и шаблоне (/ против -) всё так же проходит без жалоб. Поэтому, если вам нужна гарантированная строгость на любой версии, не полагайтесь на встроенную проверку диапазонов, а сверяйте результат обратным форматированием через TO_CHAR:

-- Reject rows where round-trip does not match the input
SELECT raw_signup
FROM users_staging
WHERE TO_CHAR(TO_DATE(raw_signup, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> raw_signup;

Двузначный год

Код YY включает правило окна: PostgreSQL достраивает двузначный год относительно текущего, что почти всегда не то, что нужно для исторических дат.

-- YY uses a sliding window around the current year
SELECT TO_DATE('15-03-49', 'DD-MM-YY');  -- 2049, maybe fine
SELECT TO_DATE('15-03-99', 'DD-MM-YY');  -- 1999, sliding-window guess

-- Force the century explicitly with RR or just demand 4 digits
SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY');  -- no guessing

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

TO_DATE против TO_TIMESTAMP

TO_DATE отбрасывает время — результат всегда тип date. Если в строке есть часы, минуты и секунды, берите TO_TIMESTAMP, иначе вы молча потеряете точность.

-- TO_DATE drops the time portion entirely
SELECT TO_DATE('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');  -- 2024-03-15

-- TO_TIMESTAMP keeps hours, minutes, seconds
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00

Различия по СУБД:

• В MySQL нет TO_DATE; используйте STR_TO_DATE('15/03/2024', '%d/%m/%Y') с процентными кодами, и она в строгом режиме вернёт NULL на невалидной дате.
• В ClickHouse есть parseDateTimeBestEffort и строгая parseDateTime с Java-подобным шаблоном; для безопасности — суффикс OrNull.

-- MySQL equivalent uses percent-style format codes
SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;

При переносе разбора дат между PostgreSQL, MySQL и ClickHouse главная опасность — не сам синтаксис TO_DATE, а расхождение в обработке невалидных значений. PostgreSQL до 16 версии молча «переносит» месяц 13 и день 32, PostgreSQL 16+ на них падает, MySQL в строгом режиме (STR_TO_DATE) возвращает NULL, а ClickHouse зависит от того, взяли вы parseDateTime или вариант с суффиксом OrNull. Поэтому перед миграцией прогоните по всем движкам одну и ту же небольшую таблицу с граничными случаями: NULL, пустая строка, месяц 13, день 32, двузначный год и расходящиеся разделители. На «счастливом пути» из чистых ISO-дат все движки совпадают и расходятся именно на этих строках.

Отдельно учтите, что TO_DATE(column, ...) в условии WHERE — это функция над колонкой, и она закрывает путь к обычному индексу по этой колонке. Если по разобранной дате идёт фильтрация на больших таблицах, разбирайте строки один раз на этапе загрузки в колонку типа date и стройте индекс уже по ней, а не вызывайте TO_DATE в каждом запросе.

Итог: используйте TO_DATE с явным шаблоном для неоднозначных форматов, не доверяйте ::date на не-ISO строках, помните про нестрогий разбор до PostgreSQL 16 и правило двузначного года, а для значений со временем берите TO_TIMESTAMP.