Округлення та обрізка часових даних: DATE_TRUNC()

DATE_TRUNC() — це потужний інструмент, який дозволяє тобі обрізати часові значення до певної часової одиниці. Наприклад, з його допомогою можна округлити часову мітку до початку дня, місяця, року, години і так далі. Це особливо корисно при аналізі даних по періодах (наприклад, якщо треба згрупувати замовлення по дню, місяцю чи року).

Уяви собі дату і час як довгий рядок тексту, де у тебе є години, хвилини, секунди. Функція DATE_TRUNC() бере цей рядок і "обрізає" зайве, залишаючи тільки ту частину, яка тобі потрібна. Наприклад:

• Ти хочеш обрізати дату 2023-10-01 15:30:45 до початку дня. Результат буде 2023-10-01 00:00:00.
• Або ти хочеш залишити тільки першу секунду години, тобто 2023-10-01 15:00:00.

Синтаксис

Синтаксис функції DATE_TRUNC() виглядає так:

DATE_TRUNC(field, source)

• field — це одиниця часу, до якої треба "обрізати" дату. Наприклад, year, month, day, hour, minute.
• source — це часовий вираз, який ти хочеш обрізати. Це може бути стовпець типу TIMESTAMP або результат виклику іншої функції, наприклад NOW().

Приклад простого виклику:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- Результат: 2023-10-01 00:00:00

Підтримувані поля

Ось список деяких підтримуваних одиниць часу, які можна використовувати в DATE_TRUNC():

Чим менша одиниця часу, тим точніше буде результат обрізки. До речі, тиждень починається з неділі :)

Приклади використання DATE_TRUNC()

Обрізка до початку дня. У цьому прикладі ми візьмемо часову мітку і округлимо її до початку дня:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- Результат: 2023-10-01 00:00:00

Обрізка до початку місяця. Тепер ми обріжемо дату до початку місяця:

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- Результат: 2023-10-01 00:00:00

Обрізка до початку року. Спробуємо округлити дату до початку року:

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- Результат: 2023-01-01 00:00:00

Використання з поточним часом (NOW()). Якщо тобі треба завжди працювати з поточною датою і часом, ти можеш комбінувати DATE_TRUNC() і NOW():

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- Результат буде залежати від поточного часу, наприклад: 2023-10-01 15:00:00

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

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Результат:

Практичні кейси використання

Аналіз часових даних по періодах: хочеш дізнатися, скільки користувачів зареєструвалось кожен рік, місяць чи день? Використовуй DATE_TRUNC() для групування даних.

Побудова звітів: правильне округлення часової мітки допоможе зробити звіти більш зручними для читання.

Порівняння дати і часу: якщо у тебе є часові дані з високоточною міткою (наприклад, з мілісекундами), обріж їх до потрібного рівня для коректного порівняння.

Типові помилки при використанні DATE_TRUNC()

Використання непідтримуваних полів. Наприклад, поле millisecond не підтримується, і спроба його використання викличе помилку.

Невірний тип даних. Функція DATE_TRUNC() працює тільки з часовими типами даних, такими як TIMESTAMP. Якщо передати їй рядок, ти отримаєш помилку.

Помилка округлення. Пам’ятай, що DATE_TRUNC() завжди обрізає час до початку вказаної часової одиниці. Якщо ти хочеш округлити час, варто використовувати інші підходи.