Преобразование форматов дат

Мир баз данных и фронтенда часто не сходится во взглядах на то, как именно должны выглядеть даты. PostgreSQL может хранить даты как DATE, TIMESTAMP или даже TIMESTAMPTZ, но этот формат не всегда подходит для отображения пользователю. Например, вместо стандартного 2023-10-01 12:30:45, дизайнеры могут захотеть увидеть 01 октября 2023 года, 12:30. А где-то нужно форматировать дату для отчетов или API.

Для преобразования дат в строковый формат и обратно в PostgreSQL существуют функции TO_CHAR() и TO_DATE().

Функция TO_CHAR()

TO_CHAR() — это ваш лучший друг, когда нужно из временных данных сделать человекочитаемый строковый формат. Она принимает дату или временную метку и форматирует её в соответствии с заданным форматом.

Синтаксис

TO_CHAR(value, format)

• value — дата или временная метка, которую нужно преобразовать.
• format — строка с шаблоном формата, как именно вы хотите отобразить дату.

Примеры форматов

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

Примеры использования TO_CHAR()

Форматирование даты для отчета

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- Результат: '09.10.2023'

Отображение времени в 12-часовом формате

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- Результат: '03:45 PM'

Вывод месяца словами

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- Результат: 'October '

Обратите внимание: PostgreSQL добавляет пробел в конце. Это фича, а не баг! Чтобы убрать пробелы, используйте функцию TRIM():

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

Создание кастомного формата

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- Результат: '2023/10/09 15:45:30'

Форматирование для интерфейса пользователя

SELECT TO_CHAR(NOW(), 'DD "октября" YYYY года') AS user_friendly_date;
-- Результат: '09 октября 2023 года'

Функция TO_DATE()

TO_DATE() делает обратное: она берет строку и преобразует её в тип данных DATE. Зачем это нужно? Например, пользователь может ввести дату в формате 01-10-2023, и PostgreSQL нужно "понять", что это за дата.

Синтаксис

TO_DATE(value, format)

• value — строка, содержащая дату.
• format — строка с шаблоном, описывающим формат строки.<

Примеры использования TO_DATE()

Преобразование строки в дату

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- Результат: '2023-10-01' (тип данных: DATE)

Сравнение строковой даты с датой в таблице

Предположим, у нас есть таблица appointments с колонкой appointment_date типа DATE. Пользователь вводит дату в виде строки:

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

Неправильный формат

Важно: если формат строки не совпадает с заданным шаблоном, будет ошибка! Например:

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- Ошибка: неверный входной формат

Проверка пользовательских данных

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

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- Вставка данных с преобразованием строки в дату
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

Практические примеры

Форматирование отчета. В таблице sales хранится дата продаж в колонке sale_date (тип TIMESTAMP). Требуется вывести отчет, где даты будут в формате DD.MM.YYYY.

-- Пример данных
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- Отчет
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

Преобразование пользовательских данных. Пусть пользователь вводит дату в строковом формате MM/DD/YYYY. Нужно преобразовать её в DATE, чтобы сохранить в системе.

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

Типичные ошибки и рекомендации

Неправильный формат. Часто встречается ошибка, когда формат строки не совпадает с шаблоном. Например, если пользователь ввел дату как 01-10-2023, а формат указан как MM/DD/YYYY, PostgreSQL выдаст ошибку. Рекомендация: всегда валидируйте пользовательский ввод перед передачей в SQL.

Пробелы в форматах TO_CHAR(). Некоторые форматы, такие как MONTH, добавляют пробелы. Если это проблема, используйте функцию TRIM().

Ошибки при парсинге строк. Если строка содержит неожиданные символы или формат, PostgreSQL не сможет преобразовать её. Рекомендация: используйте регулярные выражения или дополнительные проверки данных перед вставкой в базу.

Некорректное использование форматов времени. Например, попытка обработать временную метку TIMESTAMP с помощью шаблона для DATE. Рекомендация: убедитесь, что используемые типы данных соответствуют вашим задачам.

Функции TO_CHAR() и TO_DATE() открывают огромные возможности для работы с временными данными. Вы можете создавать удобные форматы для отчетов, преобразовывать пользовательский ввод и делать ваши SQL-запросы более читабельными. В реальной жизни эти функции широко применяются для визуализации данных, создания отчетов, интеграции с другими системами и подготовки пользовательских интерфейсов.