Извлечение частей даты: EXTRACT() и AGE()

Сегодня мы опять углубимся в манипуляцию временными данными, научимся извлекать из них конкретные части (например, год, месяц или день недели) с помощью функции EXTRACT() и изучим, как вычислять возраст или временные интервалы между датами с функцией AGE().

Работа с временными данными в реальных проектах часто требует выделения конкретных частей даты или времени. Например:

• Разделение заказов по годам или месяцам;
• Подсчет количества пользователей, зарегистрировавшихся в определенный день недели;
• Анализ длительности времени между двумя событиями.

Для решения таких задач мы используем функции EXTRACT() и AGE().

Что такое EXTRACT()?

Функция EXTRACT() позволяет извлекать конкретные части даты или временной метки. Например, можно выделить год из даты рождения, определить номер месяца или даже извлечь день недели.

Синтаксис:

EXTRACT(part FROM source)

• part: часть даты, которую нужно извлечь. Это может быть YEAR, MONTH, DAY, HOUR, MINUTE, SECOND и другие.
• source: временной тип данных, из которого извлекается информация. Это может быть столбец, константа или результат функции.

Пример 1: извлечение года, месяца и дня

SELECT
    EXTRACT(YEAR FROM '2024-11-15'::DATE) AS year_part,
    EXTRACT(MONTH FROM '2024-11-15'::DATE) AS month_part,
    EXTRACT(DAY FROM '2024-11-15'::DATE) AS day_part;

Результат:

Здесь мы извлекли год, месяц и день из даты 2024-11-15. Этот подход полезен, когда нужно сгруппировать данные по конкретным частям даты.

Пример 2: день недели и час из времени

SELECT
    EXTRACT(DOW FROM '2024-11-15'::DATE) AS day_of_week,
    EXTRACT(HOUR FROM '15:30:00'::TIME) AS hour_part;

Результат:

• DOW (Day of Week) возвращает номер дня недели: воскресенье — 0, понедельник — 1, и так далее.
• HOUR извлекает часы из времени.

Пример 3: применение к столбцам

Если у нас есть таблица с датами, мы можем извлекать части дат для анализа. Допустим, у нас есть таблица orders:

SELECT
    order_id,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;

Результат:

Что такое AGE()?

Функция AGE() используется для вычисления разницы между двумя временными метками. Например, она позволяет рассчитать возраст клиента на основе его даты рождения или определить, сколько времени прошло с момента заказа.

Синтаксис:

AGE(timestamp1, timestamp2)

• timestamp1: Более поздняя временная метка.
• timestamp2: Более ранняя временная метка.
• Если указать только один параметр, PostgreSQL автоматически сравнит его с текущей датой (NOW()).

Пример 1: расчет возраста

SELECT AGE('2025-11-15'::DATE, '1990-05-12'::DATE) AS age;

Результат:

Этот пример показывает возраст человека, родившегося 12 мая 1990 года, на дату 15 ноября 2025 года.

Пример 2: временной интервал между событиями

SELECT AGE('2023-06-01 15:00'::TIMESTAMP, '2023-05-20 10:30'::TIMESTAMP) AS duration;

Результат:

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

Пример 3: возраст клиента

Допустим, у нас есть таблица customers:

Мы можем рассчитать возраст клиентов:

SELECT
    customer_id,
    AGE(NOW(), birth_date) AS age
FROM customers;

Результат на 13 июня 2025 года:

Разумеется, у вас будет своё значение NOW() и свой результат.

Практические примеры использования EXTRACT() и AGE()

Теперь объединяем функции в реальных сценариях.

Пример 1: группировка данных по месяцам

Допустим, у нас есть таблица заказов с датами. Чтобы подсчитать заказы по месяцам, используем следующую инструкцию:

SELECT
    EXTRACT(MONTH FROM order_date) AS order_month,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Пример 2: дни до истечения срока

Представьте, что есть таблица subscriptions:

Мы хотим узнать, сколько дней осталось до истечения срока подписки:

SELECT
    subscription_id,
    AGE(expiry_date, NOW()) AS time_remaining
FROM subscriptions;

Результат:

Типичные ошибки и как их избежать

При использовании EXTRACT() и AGE() новички иногда сталкиваются с подводными камнями:

• Попытка извлечь недопустимую часть, например, месяцы из типа TIME. Запомните: части YEAR, MONTH и DAY работают с DATE, TIMESTAMP, но не с TIME.
• Проблемы с разными форматами временных данных. Например, строка 2023/11/15 не считается датой. Используйте приведение типов с ::DATE или TO_DATE().
• Разница между AGE() и вычитанием временных меток. Если вам нужен точный интервал (в месяцах, днях, секундах) — используйте AGE(), а для простого количества дней подойдут арифметические операции.

Теперь вы вооружены всеми необходимыми навыками, чтобы извлекать и анализировать части временных данных в PostgreSQL. Попробуйте поиграть с EXTRACT() и AGE() в своих проектах!