Время и дата - важнейшие характеристики во многих системах и приложениях. 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') -> 6TIMEDIFF(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Это позволит посчитать среднее время неактивности пользователей по годам регистрации.
