TO_DATE в PostgreSQL: как превратить строку в дату без сюрпризов

TO_DATE в PostgreSQL превращает текстовую строку в значение типа date.

Она нужна, когда дата приходит не в привычном ISO-формате YYYY-MM-DD, а, например, так:

15/03/2024
15.03.2024
March 15, 2024
20240315

В таких случаях простой каст вроде '15/03/2024'::date может либо упасть, либо понять дату не так, как вы ожидали. Особенно если формат неоднозначный: 03/04/2024 — это 3 апреля или 4 марта?

TO_DATE решает эту проблему: вы прямо говорите PostgreSQL, в каком порядке идут день, месяц и год.

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

Результат:

parsed_date
-----------
2024-03-15

То есть TO_DATE полезна в первую очередь при импорте CSV, миграциях, ручных выгрузках из Excel, CRM, старых систем и любых местах, где дата приехала обычным текстом.

Главная идея TO_DATE

У TO_DATE два аргумента:

TO_DATE(input_text, format_template)

Первый аргумент — строка с датой.

Второй аргумент — шаблон, который объясняет PostgreSQL, как эту строку читать.

Простой пример:

SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d;

Результат:

d
----------
2024-03-15

Шаблон YYYY-MM-DD говорит:

• сначала идут четыре цифры года;
• потом дефис;
• потом две цифры месяца;
• потом дефис;
• потом две цифры дня.

PostgreSQL берёт строку, сопоставляет её с шаблоном и собирает нормальное значение типа date.

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

Шаблон формата состоит из специальных кодов.

Код	Что означает	Пример
YYYY	год из четырёх цифр	2024
YY	год из двух цифр	24
MM	номер месяца	03
DD	день месяца	15
Mon	короткое название месяца	Mar
Month	полное название месяца	March

Разделители вроде дефиса, точки или слеша пишутся прямо в шаблоне.

SELECT
  TO_DATE('15/03/2024', 'DD/MM/YYYY') AS d1,
  TO_DATE('15.03.2024', 'DD.MM.YYYY') AS d2,
  TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d3,
  TO_DATE('20240315', 'YYYYMMDD') AS d4;

Все эти варианты дадут одну и ту же дату:

2024-03-15

Разница только в том, как дата была записана в исходной строке.

Пример с названием месяца

TO_DATE умеет разбирать и даты с текстовым названием месяца.

SELECT TO_DATE('March 15, 2024', 'Month DD, YYYY') AS d;

Результат:

d
----------
2024-03-15

Здесь шаблон Month DD, YYYY говорит:

• сначала полное название месяца;
• потом день;
• потом запятая;
• потом год.

Такой формат часто встречается в выгрузках из внешних сервисов или старых отчётах.

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

Иногда дата в строке легко приводится к date напрямую:

SELECT '2024-03-15'::date AS d;

С ISO-форматом YYYY-MM-DD обычно всё хорошо. Но как только формат становится неоднозначным, простой каст превращается в риск.

Посмотрим на строку:

03/04/2024

Что это?

• 3 апреля 2024 года?
• 4 марта 2024 года?

Зависит от того, какую привычку использует источник данных: европейскую или американскую.

С TO_DATE такой неопределённости нет:

SELECT
  TO_DATE('03/04/2024', 'MM/DD/YYYY') AS us_style,
  TO_DATE('03/04/2024', 'DD/MM/YYYY') AS eu_style;

Результат:

us_style   | eu_style
-----------+-----------
2024-03-04 | 2024-04-03

Одна и та же строка дала две разные даты, потому что мы явно указали два разных шаблона.

Именно поэтому TO_DATE лучше для импорта данных: намерение видно прямо в запросе.

TO_DATE самодокументирует запрос

Допустим, есть staging-таблица после загрузки CSV:

CREATE TABLE users_staging (
  id bigint,
  email text,
  raw_signup text
);

В колонке raw_signup дата лежит текстом:

15.03.2024

Разобрать её можно так:

SELECT
  id,
  email,
  TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_date
FROM users_staging;

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

Это лучше, чем надеяться, что сервер сам угадает формат.

Где TO_DATE особенно полезна

TO_DATE чаще всего встречается в задачах на подготовку данных.

Например:

• импорт пользователей из CSV;
• миграция из старой базы;
• разбор выгрузки из CRM;
• обработка Excel-отчёта;
• приведение дат перед загрузкой в основную таблицу;
• чистка исторических данных.

Типичный сценарий выглядит так:

1. Сначала данные загружают как текст в промежуточную таблицу.
2. Потом проверяют и очищают строки.
3. Затем через TO_DATE превращают текст в настоящий тип date.
4. После этого сохраняют результат в нормальную колонку.

Например:

INSERT INTO users_clean (id, email, signup_date)
SELECT
  id,
  email,
  TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_date
FROM users_staging
WHERE raw_signup IS NOT NULL;

Главная мысль: не храните важные даты как текст дольше, чем нужно. Разберите их один раз и положите в колонку типа date.

TO_DATE возвращает именно date

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

То есть только дату: год, месяц и день.

Времени суток в результате нет.

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

Результат:

d
----------
2024-03-15

Часы и минуты из строки были прочитаны, но в итоговом значении они не сохраняются, потому что тип результата — date.

Это важная ловушка.

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

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

Результат:

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

Итак:

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

TO_DATE и TO_TIMESTAMP: короткое сравнение

Задача	Что использовать
Разобрать 15/03/2024 как дату	TO_DATE
Разобрать 2024-03-15 14:30 как момент времени	TO_TIMESTAMP
Сохранить день рождения	TO_DATE
Сохранить время создания заказа	TO_TIMESTAMP или сразу timestamptz
Сохранить дату регистрации без времени	TO_DATE
Сохранить точное время события	TO_TIMESTAMP

Пример для даты рождения:

SELECT TO_DATE('26.03.1991', 'DD.MM.YYYY') AS birth_date;

Пример для события с временем:

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

Главная ловушка: TO_DATE может быть не такой строгой, как кажется

На первый взгляд кажется: если строка плохая, TO_DATE должна упасть с ошибкой.

Но исторически в PostgreSQL поведение было мягче. В версиях до PostgreSQL 16 некоторые невалидные даты могли «переноситься» вперёд.

Например, в старых версиях:

SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD') AS d;

Могло получиться:

d
----------
2025-01-01

Логика такая: 13-й месяц превращался в январь следующего года.

Похожая история с 32-м днём:

SELECT TO_DATE('2024-01-32', 'YYYY-MM-DD') AS d;

В старом поведении это могло превратиться в:

d
----------
2024-02-01

Для импорта данных это опасно. В исходнике была ошибка, а база молча сделала из неё вроде бы нормальную дату.

Начиная с PostgreSQL 16 такие значения обрабатываются строже: месяц 13 и день 32 приводят к ошибке, а не к тихому переносу.

Но при поддержке старых проектов важно помнить: поведение зависит от версии PostgreSQL.

Разделители могут быть мягкими

Даже в новых версиях PostgreSQL не всегда требует идеального совпадения разделителей.

Например:

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

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

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

Если бизнес-правило говорит «дата должна быть строго в формате YYYY-MM-DD», одного TO_DATE может быть недостаточно.

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

Практичный способ проверки — разобрать дату, а потом отформатировать её обратно через TO_CHAR и сравнить с исходной строкой.

SELECT
  raw_signup
FROM users_staging
WHERE TO_CHAR(TO_DATE(raw_signup, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> raw_signup;

Идея такая:

1. Берём исходную строку.
2. Превращаем её в дату через TO_DATE.
3. Превращаем дату обратно в строку через TO_CHAR.
4. Сравниваем с оригиналом.

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

Если были лишние переносы, странные разделители или непредвиденный формат, можно поймать проблему.

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

Например, сначала отбирают строки, которые хотя бы похожи на нужный формат:

SELECT
  raw_signup
FROM users_staging
WHERE raw_signup ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

А уже потом применяют TO_DATE.

Безопасный разбор через предварительную проверку

Допустим, мы принимаем только формат YYYY-MM-DD.

Тогда можно сделать так:

SELECT
  id,
  email,
  TO_DATE(raw_signup, 'YYYY-MM-DD') AS signup_date
FROM users_staging
WHERE raw_signup ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

Этот запрос отсекает строки вроде:

15.03.2024
2024/03/15
March 15, 2024

Они могут быть понятны человеку, но не соответствуют выбранному формату.

Если нужно найти плохие строки, условие можно перевернуть:

SELECT
  id,
  email,
  raw_signup
FROM users_staging
WHERE raw_signup !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
   OR raw_signup IS NULL;

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

Двузначный год: почему YY опасен

В шаблонах можно использовать YY, то есть год из двух цифр.

SELECT TO_DATE('15-03-24', 'DD-MM-YY') AS d;

Результат будет:

d
----------
2024-03-15

Но с двузначным годом есть проблема: база должна угадать век.

Что значит 49?

SELECT TO_DATE('15-03-49', 'DD-MM-YY') AS d;

Это 2049 или 1949?

А что значит 99?

SELECT TO_DATE('15-03-99', 'DD-MM-YY') AS d;

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

Лучший совет простой: по возможности требуйте год из четырёх цифр.

SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY') AS d;

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

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

NULL и пустые строки

Если входное значение NULL, результат тоже будет NULL.

SELECT TO_DATE(NULL, 'YYYY-MM-DD') AS d;

Результат:

d
------
null

А вот пустая строка — это уже не то же самое, что NULL.

SELECT TO_DATE('', 'YYYY-MM-DD') AS d;

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

При импорте часто полезно превращать пустые строки в NULL через NULLIF.

SELECT
  TO_DATE(NULLIF(raw_signup, ''), 'YYYY-MM-DD') AS signup_date
FROM users_staging;

Здесь:

NULLIF(raw_signup, '')

означает: если raw_signup равен пустой строке, вернуть NULL.

Это аккуратнее, чем пытаться разобрать пустоту как дату.

TO_DATE в WHERE и индексы

Допустим, в таблице есть текстовая колонка raw_signup, и вы пишете фильтр:

SELECT
  id,
  email
FROM users_staging
WHERE TO_DATE(raw_signup, 'DD.MM.YYYY') >= DATE '2024-01-01';

Запрос понятный, но для большой таблицы это плохой рабочий вариант.

Почему?

Потому что PostgreSQL должен применить TO_DATE к каждой строке, чтобы понять, подходит она или нет. Обычный индекс по raw_signup здесь почти не поможет: дата вычисляется на лету.

Правильнее разобрать дату один раз при загрузке и сохранить её в отдельную колонку типа date.

Например:

CREATE TABLE users_clean (
  id bigint PRIMARY KEY,
  email text NOT NULL,
  signup_date date
);

Затем загрузить очищенные данные:

INSERT INTO users_clean (id, email, signup_date)
SELECT
  id,
  email,
  TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_date
FROM users_staging;

И уже по нормальной дате строить индекс:

CREATE INDEX users_clean_signup_date_idx
ON users_clean (signup_date);

После этого фильтровать так:

SELECT
  id,
  email
FROM users_clean
WHERE signup_date >= DATE '2024-01-01';

Так быстрее, надёжнее и понятнее.

Хороший шаблон импорта дат

На практике импорт лучше делать в два слоя.

Сначала staging-таблица, где всё лежит как текст:

CREATE TABLE users_staging (
  id bigint,
  email text,
  raw_signup text
);

Потом чистая таблица с нормальными типами:

CREATE TABLE users_clean (
  id bigint PRIMARY KEY,
  email text NOT NULL,
  signup_date date
);

Загрузка:

INSERT INTO users_clean (id, email, signup_date)
SELECT
  id,
  email,
  TO_DATE(NULLIF(raw_signup, ''), 'DD.MM.YYYY') AS signup_date
FROM users_staging
WHERE raw_signup ~ '^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$'
   OR raw_signup = '';

Такой подход даёт понятный контроль:

• исходные данные не теряются;
• дата разбирается по явному шаблону;
• пустые строки превращаются в NULL;
• в чистой таблице дата хранится как date, а не как текст.

Когда TO_DATE не нужна

TO_DATE не нужно использовать везде подряд.

Если строка уже в ISO-формате, часто достаточно обычного приведения:

SELECT '2024-03-15'::date AS d;

Если значение уже хранится в колонке типа date, ничего парсить не надо:

SELECT
  signup_date
FROM users_clean;

Если в строке есть время и его нужно сохранить, нужен не TO_DATE, а TO_TIMESTAMP.

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

Если задача — красиво вывести дату, нужен TO_CHAR, а не TO_DATE.

SELECT TO_CHAR(DATE '2024-03-15', 'DD.MM.YYYY') AS formatted_date;

Запомнить можно так:

• TO_DATE — из текста в дату;
• TO_TIMESTAMP — из текста в дату и время;
• TO_CHAR — из даты или времени в текст.

MySQL: аналог STR_TO_DATE

В MySQL нет функции TO_DATE с таким синтаксисом, как в PostgreSQL.

Для похожей задачи используют STR_TO_DATE.

SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;

В MySQL шаблоны другие: вместо DD/MM/YYYY используются коды с процентом.

Например:

PostgreSQL	MySQL	Значение
YYYY	%Y	год из четырёх цифр
MM	%m	месяц
DD	%d	день
HH24	%H	час в формате 0-23
MI	%i	минуты
SS	%s	секунды

То есть при переносе запроса из PostgreSQL в MySQL нельзя просто заменить имя функции. Нужно переписать и шаблон.

PostgreSQL:

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

MySQL:

SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;

ClickHouse: parseDateTime и варианты OrNull

В ClickHouse для разбора дат и времени часто используют функции семейства parseDateTime.

Например:

SELECT parseDateTimeBestEffort('2024-03-15 14:30:00') AS ts;

Для более безопасной обработки грязных данных в ClickHouse часто используют варианты с суффиксом OrNull.

Идея такая: если строка не разобралась, вернуть NULL, а не ломать весь запрос.

SELECT parseDateTimeBestEffortOrNull('bad value') AS ts;

При переносе логики между PostgreSQL, MySQL и ClickHouse главная опасность не в названии функции, а в том, как база обрабатывает плохие даты.

Одна СУБД может упасть с ошибкой, другая вернуть NULL, третья попытаться угадать значение.

Поэтому перед миграцией полезно прогнать маленький набор тестовых строк:

2024-03-15
15/03/2024

2024-13-01
2024-01-32
15-03-99
2024/03/15

Так вы сразу увидите, где поведение отличается.

Частые ошибки с TO_DATE

Надеяться на ::date для неоднозначной строки

Опасно:

SELECT '03/04/2024'::date AS d;

Лучше явно указать формат:

SELECT TO_DATE('03/04/2024', 'DD/MM/YYYY') AS d;

или так:

SELECT TO_DATE('03/04/2024', 'MM/DD/YYYY') AS d;

Второй аргумент сразу показывает, что вы имели в виду.

Использовать TO_DATE для строки со временем

Если время важно, это ошибка:

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

Результат будет только датой.

Лучше:

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

Использовать YY вместо YYYY

Нежелательно для важных данных:

SELECT TO_DATE('15-03-99', 'DD-MM-YY') AS d;

Лучше требовать полный год:

SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY') AS d;

Разбирать дату в каждом WHERE

Плохо для больших таблиц:

SELECT
  id
FROM users_staging
WHERE TO_DATE(raw_signup, 'DD.MM.YYYY') >= DATE '2024-01-01';

Лучше один раз сохранить результат в колонку типа date и фильтровать уже по ней.

Не проверять грязные данные перед импортом

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

Сначала найдите подозрительные строки:

SELECT
  id,
  raw_signup
FROM users_staging
WHERE raw_signup IS NULL
   OR raw_signup = ''
   OR raw_signup !~ '^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$';

Потом отдельно решите, что с ними делать: исправить, пропустить, положить NULL или отправить на ручную проверку.

Мини-шпаргалка

Нужно сделать	Инструмент
Превратить текст 15.03.2024 в дату	TO_DATE
Превратить текст с датой и временем в timestamp	TO_TIMESTAMP
Превратить дату в красивую строку	TO_CHAR
Разобрать ISO-дату 2024-03-15	можно ::date
Разобрать неоднозначную дату 03/04/2024	лучше TO_DATE
Хранить дату после импорта	колонка типа date
Фильтровать большую таблицу по дате	индекс по колонке типа date

Главное из статьи

TO_DATE в PostgreSQL превращает текстовую строку в дату по явно заданному шаблону.

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

TO_DATE(input_text, format_template)

Пример:

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

TO_DATE особенно полезна, когда дата пришла в не-ISO-формате: из CSV, Excel, CRM, старой базы или ручной выгрузки.

Главные правила:

• для неоднозначных строк не полагайтесь на ::date;
• явно указывайте формат через DD, MM, YYYY;
• по возможности требуйте четырёхзначный год YYYY, а не YY;
• помните, что TO_DATE возвращает только date и отбрасывает время;
• если нужно сохранить часы и минуты, используйте TO_TIMESTAMP;
• на старых версиях PostgreSQL проверяйте невалидные даты особенно внимательно;
• для строгого импорта добавляйте предварительную проверку формата;
• не вызывайте TO_DATE в каждом WHERE на большой таблице — лучше разобрать дату один раз и сохранить в колонку типа date.

Если коротко: TO_DATE — это переводчик между «датой как текстом» и нормальной SQL-датой. Чем точнее вы зададите шаблон, тем меньше шансов, что база неправильно поймёт день, месяц или год.