SQL работа с датами: полезные функции и примеры использования

Время и дата - важнейшие характеристики во многих системах и приложениях. SQL предоставляет развитый инструментарий для работы с датой-временем - специальные типы данных и множество функций. Давайте разберемся!

Типы данных даты и времени в SQL

SQL поддерживает несколько типов данных для хранения даты и времени:

• DATE - только дата
• TIME - только время
• DATETIME - дата и время
• TIMESTAMP - дата и время
• YEAR - только год

Давайте подробнее разберем особенности каждого.

DATE

Хранит только дату в формате ГГГГ-ММ-ДД от 1000-01-01 до 9999-12-31.

Занимает 3 байта. Подходит, если время не важно.

TIME

Хранит время в формате ЧЧ:ММ:СС от '-838:59:59' до '838:59:59'.

Занимает 3 байта. Используется со значениями меньше 24 часов.

DATETIME

Хранит дату и время от '1000-01-01 00:00:00' до '9999-12-31 23:59:59', точность - 1 секунда.

Занимает 8 байт. Самый распространенный тип данных даты-времени в SQL.

TIMESTAMP

Тот же диапазон значений, что и у DATETIME. Отличается тем, что при вставке и обновлении автоматически подставляется текущая дата-время.

YEAR

Хранит только год от 1901 до 2155. Занимает 1 байт.

Мало используется на практике из-за ограниченности.

Как видно из обзора, наиболее универсальный тип данных для хранения даты-времени в SQL - это DATETIME. А для хранения только даты подходит DATE.

Основные функции даты-времени в SQL

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

Рассмотрим самые полезные:

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

• NOW() - текущая дата и время
• CURDATE() - текущая дата
• CURTIME() - текущее время

Эти функции часто используются для подстановки актуального значения даты-времени в момент вставки или обновления записи:

INSERT INTO events (date) VALUES (NOW());

Выборка части даты

Функции для извлечения отдельных компонент даты-времени:

• YEAR(date) - год
• MONTH(date) - месяц
• DAY(date) - день
• HOUR(datetime) - часы
• MINUTE(datetime) - минуты
• SECOND(datetime) - секунды

Пример получения месяца из даты:

SELECT MONTH('2023-11-29') -> 11

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

Чтобы прибавить к дате интервал используется функция DATE_ADD:

DATE_ADD(date, INTERVAL value type)

Где value - количество, а type - тип интервала (DAY, WEEK, MONTH, YEAR и др.)

Например, прибавить 5 дней:

DATE_ADD('2023-11-29', INTERVAL 5 DAY) -> 2023-12-04

Аналогично для вычитания интервалов используется DATE_SUB.

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

Чтобы найти разницу между двумя датами в определенных единицах измерения, используется функция DATEDIFF:

DATEDIFF(unit, date1, date2)

Например, разница в днях:

DATEDIFF(DAY, '2023-11-25', '2023-11-29') -> 4

Единицы измерения могут быть DAY, WEEK, MONTH, YEAR и другими.

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

Функция DATE_FORMAT позволяет отформатировать дату-время в нужном виде с помощью специальныхспецификаторов:

DATE_FORMAT(date, 'format')

Например, вывести в формате "05 декабря 2023 г.":

DATE_FORMAT('2023-12-05', '%d %M %Y г.') 

Подробнее форматы даты-времени мы разберем в следующем разделе.

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

Дата и время в SQL могут выводиться в разных форматах. Формат задается с помощью специальных спецификаторов.

Спецификаторы для формата даты

• %d - день месяца (01 - 31)
• %m - номер месяца (01 - 12)
• %M - полное название месяца
• %Y - год в формате YYYY
• %y - год в формате YY
• %D - день недели в текстовом виде
• %d%m%Y - дд.мм.гггг (05.12.2023)

Спецификаторы для формата времени

• %H - часы (00-23)
• %h - часы (01-12)
• %i - минуты (00-59)
• %s - секунды (00-59)
• %H:%i:%s - ЧЧ:ММ:СС

С помощью этих спецификаторов можно сформировать дату и время в любом нужном виде.

Пользовательские форматы

Если требуется нестандартный формат даты-времени, можно создать свой с помощью конкатенации спецификаторов и других символов:

DATE_FORMAT(date, 'Формат: %d/%m/%Y')

Это позволяет получить полностью пользовательское форматирование.

Использование даты-времени в запросах

Рассмотрим основные варианты использования даты-времени в SQL запросах.

Выборка данных за период

Чтобы получить данные за конкретный период, используется оператор BETWEEN:

SELECT * FROM events 
 WHERE date BETWEEN '2023-12-01' AND '2023-12-31'

Это позволяет выбрать данные за декабрь 2023 года.

Вычисления с датами

Даты можно использовать в арифметических операциях:

SELECT DATE_ADD(date, INTERVAL 10 DAY) AS next_date, DATEDIFF(date2, date1) AS days_diff FROM events

Здесь мы прибавляем 10 дней и вычисляем разницу в днях между датами.

Сортировка по дате

SELECT * FROM events ORDER BY date DESC

Запрос отсортирует события по убыванию дат.

Группировка и агрегация по дате

Дату можно использовать для группировки данных в запросе:

SELECT DATE(date) AS date, COUNT(*) FROM events GROUP BY DATE(date)

Это позволит посчитать сколько событий было в каждую дату.

Фильтрация по дате

Условия фильтрации в WHERE позволяют отбирать данные по дате:

SELECT * FROM events WHERE MONTH(date) = 12

В результат попадут события только за декабрь.

Вычисление разницы между датами-временем

Рассмотрим подробнее функции для нахождения разницы между двумя значениями даты-времени.

DATEDIFF(unit, date1, date2)

Возвращает разницу в указанной единице измерения (день, месяц, год и т.д.):

DATEDIFF(MONTH, '2023-05-29', '2023-11-29') -> 6

TIMEDIFF(time1, time2)

Вычисляет разницу между временем time1 и time2 в часах, минутах и секундах:

TIMEDIFF('12:00:00', '13:55:00') -> '01:55:00'

Преобразование даты-времени

Также SQL предоставляет функции для преобразования:

• К нужному типу данных (CAST, CONVERT)
• К строковому представлению (DATE_FORMAT)
• Из строкового представления (STR_TO_DATE)

Это позволяет гибко конвертировать значения даты-времени между форматами и типами данных.

Приведение к другому типу данных

Для преобразования даты-времени к другому типу используются функции CAST и CONVERT:

SELECT CAST(NOW() AS DATE); SELECT CONVERT(datetime, '2023-12-05');

Это позволяет, например, конвертировать строку в тип DATETIME или наоборот.

Преобразование к строковому представлению

Чтобы получить строковое представление даты-времени, используется знакомая функция DATE_FORMAT:

SELECT DATE_FORMAT(NOW(), '%d.%m.%Y %H:%i') -> 05.12.2023 14:55

Формат задается с помощью спецификаторов.

Преобразование из строки в дату

Для конвертации строки в дату-время служит функция STR_TO_DATE():

SELECT STR_TO_DATE('05.12.2023 14:55', '%d.%m.%Y %H:%i');

Как видно из примеров, преобразования позволяют гибко оперировать значениями даты-времени в SQL.

Применение на практике

Рассмотрим несколько примеров использования функций даты-времени в реальных задачах.

Анализ истории и активности

С помощью дат можно анализировать историю действий пользователя, например:

SELECT YEAR(registration_date) AS reg_year, AVG(DATEDIFF(NOW(), login_time)) AS avg_inactive_days FROM users GROUP BY reg_year

Это позволит посчитать среднее время неактивности пользователей по годам регистрации.