Перетворення форматів дат

Світ баз даних і фронтенду часто не сходиться у поглядах на те, як саме мають виглядати дати. 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-запити більш читабельними. У реальному житті ці функції широко юзаються для візуалізації даних, створення звітів, інтеграції з іншими системами і підготовки юзерських інтерфейсів.