Функции даты и времени в SQL

Урок 3.4 · Время чтения: ~8 мин · Задачи для практики ↓

Функции даты и времени в SQL возвращают текущую дату, извлекают части даты, прибавляют и вычитают интервалы, вычисляют разницу между датами и форматируют даты для вывода. В этом уроке вы изучите самые используемые функции на примере данных об аренде из базы Sakila и увидите, как их названия различаются в разных СУБД.

Почти в каждой бизнес-таблице есть даты: когда оформлен заказ, когда взят фильм, когда прошёл платёж. На вопросы «сколько длилась аренда?», «когда нужно вернуть фильм?» или «сколько аренд было в июле?» отвечают функции дат.

Примеры используют синтаксис MySQL и MariaDB. Функции дат различаются между СУБД сильнее, чем любые другие, поэтому в конце урока приведена сравнительная таблица.

Текущая дата и время

SELECT CURRENT_DATE AS today,
       CURRENT_TIME AS now_time,
       NOW() AS now_datetime;

Результат: одна строка с текущей датой, временем и датой со временем на сервере базы данных.

CURRENT_DATE, CURRENT_TIME и CURRENT_TIMESTAMP записываются без скобок: это выражения стандарта SQL. Значение фиксируется в начале выполнения оператора, поэтому все строки одного запроса получают одно и то же время.

Извлечение частей даты

DATE() и TIME() разделяют значение даты со временем на дату и время, а YEAR(), MONTH() и DAY() возвращают отдельные числа:

SELECT rental_date,
       DATE(rental_date) AS rental_day,
       TIME(rental_date) AS rental_time,
       YEAR(rental_date) AS rental_year,
       MONTH(rental_date) AS rental_month,
       DAY(rental_date) AS day_of_month
FROM rental
LIMIT 3;

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

Стандартная функция EXTRACT() делает то же самое и работает в большинстве СУБД:

SELECT rental_date,
       EXTRACT(YEAR FROM rental_date) AS rental_year,
       EXTRACT(MONTH FROM rental_date) AS rental_month
FROM rental
LIMIT 3;

Извлечённые части удобно использовать в фильтрах:

SELECT rental_id, rental_date
FROM rental
WHERE YEAR(rental_date) = 2005
  AND MONTH(rental_date) = 7
LIMIT 5;

Результат: пять аренд, оформленных в июле 2005 года.

Функция над столбцом мешает базе данных использовать индекс по нему. На больших таблицах диапазон дат работает быстрее: WHERE rental_date >= '2005-07-01' AND rental_date < '2005-08-01'. Почему - вы узнаете в уроках об индексах.

Добавление и вычитание интервалов

DATE_ADD() и DATE_SUB() сдвигают дату на интервал. Единицей может быть SECOND, MINUTE, HOUR, DAY, WEEK, MONTH или YEAR.

SELECT rental_id,
       rental_date,
       DATE_ADD(rental_date, INTERVAL 7 DAY) AS due_date,
       DATE_SUB(rental_date, INTERVAL 1 MONTH) AS month_before
FROM rental
LIMIT 3;

Результат: дата каждой аренды, дата через семь дней и дата на месяц раньше.

MySQL и MariaDB принимают также краткую запись rental_date + INTERVAL 7 DAY.

Разница между двумя датами

DATEDIFF(end, start) возвращает количество дней между двумя датами без учёта времени:

SELECT rental_id,
       rental_date,
       return_date,
       DATEDIFF(return_date, rental_date) AS days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 5;

Результат: сколько календарных дней длилась каждая из пяти завершённых аренд.

TIMESTAMPDIFF(unit, start, end) считает полные единицы любого размера; обратите внимание, что здесь начальная дата идёт первой:

SELECT rental_id,
       TIMESTAMPDIFF(HOUR, rental_date, return_date) AS hours_rented,
       TIMESTAMPDIFF(DAY, rental_date, return_date) AS full_days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 5;

Результат: длительность аренды в часах и в полных сутках. full_days_rented может быть меньше, чем DATEDIFF, который считает календарные дни.

Форматирование дат

DATE_FORMAT(date, format) превращает дату в текст в заданном формате:

SELECT rental_date,
       DATE_FORMAT(rental_date, '%d.%m.%Y') AS european_date,
       DATE_FORMAT(rental_date, '%M %e, %Y %H:%i') AS long_date
FROM rental
LIMIT 3;

Результат: дата в виде 24.05.2005 и May 24, 2005 22:53.

Результат DATE_FORMAT() - текст, а не дата. Форматируйте даты только для вывода, а сравнивайте и сортируйте исходные значения дат.

Функции дат в других СУБД

EXTRACT(part FROM date) и CURRENT_DATE / CURRENT_TIMESTAMP входят в стандарт и работают почти везде.

Ключевые выводы:

• CURRENT_DATE, CURRENT_TIMESTAMP и NOW() возвращают текущие дату и время.
• DATE(), YEAR(), MONTH(), DAY() и EXTRACT() извлекают части даты.
• DATE_ADD() и DATE_SUB() сдвигают дату на интервал.
• DATEDIFF() считает календарные дни, TIMESTAMPDIFF() - полные единицы любого размера.
• DATE_FORMAT() возвращает текст: используйте его для вывода, а не для сравнений.
• Названия функций дат сильно различаются в разных СУБД; при переходе сверяйтесь с таблицей выше.

Часто задаваемые вопросы

Как получить текущую дату в SQL?

Используйте CURRENT_DATE для даты без времени и CURRENT_TIMESTAMP или NOW() для даты со временем. В SQL Server - GETDATE() или CAST(GETDATE() AS DATE).

Как вычислить количество дней между двумя датами в SQL?

В MySQL и MariaDB используйте DATEDIFF(end_date, start_date). В SQL Server - DATEDIFF(day, start_date, end_date). В PostgreSQL вычтите одну дату из другой: end_date::date - start_date::date.

Как прибавить дни к дате в SQL?

В MySQL и MariaDB используйте DATE_ADD(date, INTERVAL 7 DAY) или date + INTERVAL 7 DAY. В PostgreSQL - date + INTERVAL '7 days', в SQL Server - DATEADD(day, 7, date).