Функции для работы с датой и временем

Даты, время, интервалы в PostgreSQL

Теория

Итак. В предыдущих разделах мы рассмотрели функции для обработки числовых и текстовых данных. Однако это лишь часть инструментов, необходимых для полноценного анализа. Важным дополнением являются функции для работы с датами и временем, которые играют ключевую роль в аналитических задачах.

С помощью этих функций в SQL-запросах можно получить ответы на такие вопросы: когда был создан аккаунт пользователя, в каком месяце совершён заказ, сколько лет сотрудник проработал в компании, и какова общая сумма заказов, сделанных клиентами нашей компании за последний месяц.

А также эти функции помогают нам:

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

В SQL дата и время — это не просто текстовые данные. Они представляют собой полноценные значения, которые можно использовать для вычислений, сравнений и преобразований.

В учебных таблицах присутствуют как стандартные даты, так и даты с указанием времени. Например, поля registration_date и hire_date имеют тип DATE, тогда как order_date относится к типу TIMESTAMP. Поэтому в результате в одних случаях мы будем оперировать временем напрямую, а в других, при необходимости, извлекать только дату с помощью оператора ::date.

Синтаксис

SELECT CURRENT_DATE,                             -- получаем текущую дату
       CURRENT_TIME,                             -- получаем текущее время
       CURRENT_TIMESTAMP,                        -- получаем текущую дату и время
       EXTRACT(YEAR FROM registration_date),     -- извлекаем год из даты регистрации
       DATE_TRUNC('month', order_date)           -- усекаем дату заказа до месяца
FROM table_name;                                 -- указываем таблицу, из которой берём данные

SELECT order_date + INTERVAL '7 days',           -- прибавляем к дате заказа 7 дней
       AGE(CURRENT_DATE::timestamp, hire_date::timestamp) -- считаем разницу между текущей датой и датой найма
FROM table_name;                                 -- указываем таблицу, из которой берём данные

SQL

Компактная шпаргалка по самым частым функциям и конструкциям.

Пример	Результат	Что делает
CURRENT_DATE	Текущая дата	Возвращает текущую дату.
CURRENT_TIME	Текущее время	Возвращает текущее время.
CURRENT_TIMESTAMP	Дата и время	Возвращает текущие дату и время вместе.
NOW()	Дата и время	Практически тот же результат, что и у CURRENT_TIMESTAMP.
EXTRACT(YEAR FROM order_date)	2024	Извлекает нужную часть из даты или времени.
DATE_TRUNC('month', CURRENT_TIMESTAMP)	Начало месяца	Усекает (обнуляет) дату до указанной части.
order_date + INTERVAL '7 days'	Дата и время через 7 дней	Прибавляет к дате нужный интервал.
AGE(CURRENT_DATE::timestamp, hire_date::timestamp)	Интервал между датами	Показывает разницу между двумя датами в более понятном виде.
order_date::date - DATE '2024-01-01'	число дней	Показывает разницу в днях между двумя датами.

Теперь по классике разберем важные моменты.

1. CURRENT_DATE, CURRENT_TIME и CURRENT_TIMESTAMP берут значение на текущий момент.
Это удобно, когда нужно сравнить данные с сегодняшней датой или просто показать текущее время прямо в запросе.

2. EXTRACT нужен, когда из даты или времени нужна только одна часть.
Например, только год, только месяц, только день или только час. Поэтому записи вроде EXTRACT(YEAR FROM order_date) и EXTRACT(MONTH FROM registration_date) встречаются достаточно часто.

3. DATE_TRUNC не вытаскивает часть даты, а усекает значение.
Например, если обрезать (усечь) до месяца, то всё внутри месяца сведётся к его началу. Это нужно для отчётов по дням, месяцам и годам.

4. Прибавление интервала.
Если нужно получить дату через 7 дней, через 1 месяц или через 2 часа, к дате или временем прибавляют INTERVAL. Например: order_date + INTERVAL '7 days'.

5. AGE и обычное вычитание даты — это не одно и то же.
Функция AGE возвращает интервал в более понятном виде: годы, месяцы, дни. А вычитание одной даты из другой выдаёт просто количество дней.

6. Дата и дата со временем — это не одно и то же.
Если поле хранится как timestamp, как у order_date, то при расчёте разницы именно в днях часто нужно явно взять только дату. Для этого необходимо выполнить преобразование типов order_date::date.

Примеры

1. Смотрим на текущую дату и текущее время

SELECT CURRENT_DATE AS today_date,
       CURRENT_TIME AS current_time,
       CURRENT_TIMESTAMP AS current_timestamp,
       NOW() AS now_value;

Этот пример показывает как вывести текущее значение даты, времени, даты со временем.

🔄 Попробуйте изменить запрос:

• Запустите запрос несколько раз подряд и посмотрите, как меняется время
• Вычтите из CURRENT_TIMESTAMP функцию NOW()

2. Достаём из даты год, месяц и день

SELECT order_id,
       order_date,
       EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(DAY FROM order_date) AS order_day
FROM orders
ORDER BY order_date DESC;

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

🔄 Попробуйте изменить запрос:

• Замените YEAR на HOUR
• Попробуйте тот же приём столбцом registration_date из таблицы users

3. Округляем дату и время до начала месяца

SELECT order_id,
       order_date,
       DATE_TRUNC('month', order_date) AS order_month_start
FROM orders
ORDER BY order_date DESC;

Функция DATE_TRUNC сокращает дату до начала месяца, усекая все последующие данные. Это полезно, когда необходимо привести даты за один и тот же месяц к единому началу.

🔄 Попробуйте изменить запрос:

• Замените 'month' на 'day' и посмотрите, как изменится результат

4. Прибавляем к дате нужный интервал

SELECT order_id,
       order_date,
       order_date + INTERVAL '7 days' AS plus_7_days,
       order_date + INTERVAL '1 month' AS plus_1_month
FROM orders
ORDER BY order_date DESC;

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

🔄 Попробуйте изменить запрос:

• Замените '7 days' на '14 days'
• Попробуйте вместо '1 month' указать '2 months'

5. Считаем стаж работника от даты найма

SELECT worker_id,
       first_name,
       hire_date,
       AGE(CURRENT_DATE::timestamp, hire_date::timestamp) AS work_experience
FROM workers
WHERE hire_date IS NOT NULL
ORDER BY hire_date ASC;

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

🔄 Попробуйте изменить запрос:

• Замените CURRENT_DATE::timestamp на конкретную дату TIMESTAMP '2025-01-01 00:00:00'
• Попробуйте посчитать разницу между CURRENT_DATE::timestamp и registration_date::timestamp в таблице users

6. Считаем разницу между датами в днях

SELECT order_id,
       order_date,
       order_date::date - '2024-01-01'::date AS days_from_2024_start
FROM orders
WHERE order_date::date >= '2024-01-01'::date
ORDER BY order_date DESC;

Когда одна дата вычитается из другой, PostgreSQL возвращает количество дней.

🔄 Попробуйте изменить запрос:

• Замените дату '2024-01-01'::date на '2024-06-01'::date
• Попробуйте вычислить разницу через функцию AGE

7. Фильтруем заказы за последние 7 дней

SELECT order_id,
       order_date,
       status
FROM orders
WHERE order_date >= '2024-04-10 00:00:00'::timestamp - INTERVAL '7 days'
  AND order_date < '2024-05-10 00:00:00'::timestamp
ORDER BY order_date DESC;

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

🔄 Попробуйте изменить запрос:

• Замените '7 days' на '14 days'
• Попробуйте сдвинуть опорную дату с '2024-05-10 00:00:00'::timestamp на '2024-04-20 00:00:00'::timestamp

8. Используем часть даты прямо в фильтрации

SELECT user_id,
       first_name,
       registration_date,
       EXTRACT(MONTH FROM registration_date) AS registration_month
FROM users
WHERE EXTRACT(MONTH FROM registration_date) = 6
ORDER BY registration_date DESC;

В данном примере результат функции применяется в условии WHERE. Это позволяет, например, легко выявить пользователей, зарегистрировавшихся в определённом месяце.

🔄 Попробуйте изменить запрос:

• Замените 6 на 12
• Перепишите условие фильтрации для шестого месяца, не прибегая к функции EXTRACT

Типичные ошибки

Путают дату как значение и дату как текст Записи вроде CURRENT_DATE или DATE '2024-01-01' — это работа с датой, а не просто с текстом. Это важно для вычислений и сравнений. Путают DATE_TRUNC и EXTRACT EXTRACT достаёт одну часть из даты. DATE_TRUNC укрупняет всё значение до дня, месяца, года и так далее. Забывают, что у поля может быть и дата, и время Если поле хранится как timestamp, как например order_date, то при расчёте разницы в днях часто приходится явно привести тип order_date::date. Не понимают разницу между AGE и вычитанием дат AGE возвращает интервал в более человеческом виде, а простое вычитание выдаёт количество дней. Прибавляют к дате число вместо интервала и потом не понимают результат Если требуется смещение даты на определённый период — будь то дни, недели или месяцы, рекомендуется использовать конструкцию INTERVAL. Например, для сдвига даты на семь дней можно применить выражение order_date + INTERVAL '7 days'. Хотя прибавить количество дней к дате можно и просто с помощью знака плюс, но для работы с месяцами и другими временными интервалами всё же следует применять INTERVAL.

Функции для работы с датой и временем

Теория

Синтаксис

Примеры

1. Смотрим на текущую дату и текущее время

🔄 Попробуйте изменить запрос:

2. Достаём из даты год, месяц и день

🔄 Попробуйте изменить запрос:

3. Округляем дату и время до начала месяца

🔄 Попробуйте изменить запрос:

4. Прибавляем к дате нужный интервал

🔄 Попробуйте изменить запрос:

5. Считаем стаж работника от даты найма

🔄 Попробуйте изменить запрос:

6. Считаем разницу между датами в днях

🔄 Попробуйте изменить запрос:

7. Фильтруем заказы за последние 7 дней

🔄 Попробуйте изменить запрос:

8. Используем часть даты прямо в фильтрации

🔄 Попробуйте изменить запрос:

Типичные ошибки

Практика

Проверь себя