Работа с датами и временем в MySQL

Используйте даты в SQL правильно

TL;DR

Работайте с датами в базе данных в UTC — это уменьшает ошибки и упрощает логику. Для преобразований используйте CONVERT_TZ(), для арифметики — DATE_ADD()/DATE_SUB(), для разборки дат — набор функций извлечения (MONTH, YEAR, DAY и т. д.). При выборках по периоду предпочитайте BETWEEN и диапазоны, а не обёртки функций вокруг колонок — так лучше используются индексы.

Почему даты и время важны

Даты и временные метки сопровождают почти любую бизнес‑логику: события, лимиты, отчёты, биллинги и т.д. Ошибки в обработке времени приводят к потерянным данным, неверным отчётам и труднонаходимым багам. Главное правило простое: централизуйте представление времени и делайте преобразования только на границах системы (ввод/вывод).

Работа с временными зонами

Всегда храните даты и время в UTC. Это делает данные детерминированными и позволяет избегать проблем с переходом на летнее/зимнее время и разными локалями.

Установите часовой пояс соединения при подключении к MySQL:

        `SET TIME_ZONE = '+0:00'`

Если нужно показать время в локальной зоне, используйте CONVERT_TZ(). Пример для перевода из UTC в PST (UTC-08:00):

        `SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');`

Аргументы CONVERT_TZ(): исходный timestamp, исходный часовой пояс (в нашем процессе это всегда ‘+0:00’), целевой часовой пояс. Для текущего времени используйте now().

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

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

Для арифметики дат используйте DATE_ADD() и DATE_SUB(). Они понятны и оптимальны для большинства случаев.

        `SELECT DATE_SUB(now(), interval 2 week);`

        `SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);`

Синтаксис: первый аргумент — исходная метка времени, второй аргумент — интервал в формате: interval <число> <единица>. Единицы: second, minute, hour, day, week, month, quarter, year.

Пример выборки логинов за последние 45 минут:

        `SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);`

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

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

Для получения разницы в днях используйте DATEDIFF():

        `SELECT DATEDIFF(now(), '2020-12-15');`

DATEDIFF возвращает количество дней между двумя датами (целое число).

Для секунд можно воспользоваться TO_SECONDS():

        `SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');`

Это удобно для вычисления прошедшего времени с точностью до секунды, но имейте в виду, что функции, основанные на абсолютных счетчиках (TO_SECONDS/TO_DAYS), чувствительны к историческим календарным аномалиям и не всегда нужны для бизнес‑логики.

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

MySQL предоставляет набор функций для получения сегментов даты (каждая принимает одну метку времени):

        `- SECOND()`
- MINUTE()
- HOUR()
- DAY()
- WEEK() - номер недели 0–52
- MONTH()
- QUARTER() - номер квартала 1–4
- YEAR()
- DAYOFYEAR() - день в году (например, 15 сент. = 258)
- LAST_DAY() - последний день месяца
- DATE() - дата в формате YYYY-MM-DD без времени
- TIME() - время в формате HH:MM:SS без даты
- TO_DAYS() - число дней с 1 января н.э.
- TO_SECONDS() - число секунд с 1 января н.э.
- UNIX_TIMESTAMP() - секунды с эпохи (1970-01-01)

Примеры:

        `SELECT MONTH('2021-02-11 15:27:52');`

        `SELECT HOUR(now());`

        `SELECT DAYOFYEAR('2021-07-15 12:00:00');`

Используйте эти функции для агрегации и группировок, но избегайте применения их в WHERE к индексируемым колонкам — это мешает использованию индекса.

Группировка по периодам

Группировка по месяцу/дню/кварталу часто нужна в отчётах. Пример сумм по месяцам за 2020 год:

        `SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);`

Почему BETWEEN предпочтительнее YEAR(created_at) = 2020: выражения над колонками обычно приводят к полному сканированию таблицы и не используют индекс. Всегда задавайте диапазон по самой колонке.

Частые ошибки и когда подходы не работают

Проблема: хранение локального времени в БД и независимые сервисы в разных часовых поясах. Результат: несогласованные временные шкалы.
Нельзя полагаться на системный час сервера без контроля timezone соединения — соединение может иметь свой TZ.
CONVERT_TZ() требует корректных таблиц часовых зон в MySQL (time_zone_name). На некоторых установках они могут быть пустыми — тогда функция вернёт NULL. Если это критично, держите список смещений и правил в приложении.
DATE_ADD/DATEDIFF не подходят для сложных бизнес‑правил с учётом рабочих дней, праздников или нестандартных календарей — для этого используйте библиотеку в приложении.

Альтернативные подходы и когда их применять

Хранить время в виде Unix‑timestamp (INT/ BIGINT). Плюсы: простота, компактность, кросс‑язычность. Минусы: теряется удобство SQL‑функций даты; требуется приведение для читаемого формата.
Хранить дату и отдельно часовой пояс (например, created_at_utc + client_tz). Это полезно, если важно восстановить локальное время пользователя.
Выполнять сложные расчёты по датам в бизнес‑логике (backend), а в SQL оставлять простые фильтры и агрегаты.

Ментальные модели и эвристики

Источник истины — UTC. Всё остальное — производное.
Преобразования выполняются на границах (вход/выход). Внутри системы используйте одно представление.
Для индексов: фильтруйте по сырой колонке, не по функции от колонки.
Для отчётов: заранее вычисляйте бэнчмарки (пороговые временные метки) и подставляйте их в запросы.

Факт‑бокс: ключевые числа и понятия

Epoch Unix: 1970-01-01 00:00:00 UTC — точка отсчёта для UNIX_TIMESTAMP().
TO_DAYS/TO_SECONDS считают от «A.D. 0» (внутреннее представление MySQL) — используйте с осторожностью.
Interval‑единицы: second, minute, hour, day, week, month, quarter, year.

Шпаргалка — полезные SQL-шаблоны

Установить соединение в UTC:

        `SET TIME_ZONE = '+0:00'`

Перевести время в локал:

        `SELECT CONVERT_TZ(created_at, '+0:00', '-8:00') FROM events;`

Логи за последние N минут (эффективнее с предварительным вычислением порога):

        `SET @threshold = DATE_SUB(NOW(), INTERVAL 45 MINUTE);
SELECT * FROM logins WHERE login_date >= @threshold;`

Агрегация по месяцу с диапазоном:

        `SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(amount) FROM orders
WHERE created_at BETWEEN '2020-01-01' AND '2020-12-31 23:59:59'
GROUP BY ym;`

Чек‑лист по ролям

Разработчик:
- Хранить все временные метки в UTC.
- Не оборачивать индексируемые колонки функциями в WHERE.
- Использовать DATE_ADD/DATE_SUB для простых интервалов.
DBA:
- Настроить серверные TZ‑таблицы для CONVERT_TZ().
- Мониторить запросы, которые приводят к полному сканированию из‑за функций.
Аналитик/отчётник:
- Форматировать даты при выборке (DATE_FORMAT) для читаемости.
- Использовать материализованные представления или предварительные агрегаты для больших объёмов данных.

Миграция и совместимость

Перед миграцией убедитесь, что все даты конвертированы в UTC. Проведите тестовую выборку и сравните контрольные суммы по таблицам.
Если используете UNIX_TIMESTAMP в прошлом, проверьте размер поля (INT32 vs BIGINT) — INT32 может переполниться в отдалённом будущем.
Обновление MySQL может поменять поведение некоторых функций; протестируйте DATE/TO_SECONDS/UNIX_TIMESTAMP на тестовых наборах.

Безопасность и конфиденциальность

Временные метки и журналирование могут быть частью персональных данных (например, время действия пользователя). Для GDPR/локальных требований:

Оценивайте необходимость хранения точных меток vs анонимизированных агрегатов.
Если храните локальную зону пользователя, документируйте это и защищайте доступ к данным.

Критерии приёмки

Все записи в базе хранятся в UTC.
Конвертации в локальную зону выполняются только в слое представления или явно в запросах с CONVERT_TZ().
Запросы по временным диапазонам используют BETWEEN или явный порог, а не YEAR()/MONTH() в WHERE для индексируемых колонок.
Unit‑тесты покрывают сценарии перехода на летнее время и граничные значения интервалов.

Быстрый план внедрения (мини‑методология)

Проверьте текущие форматы дат в таблицах и приложениях. 2. Обозначьте источники, где время хранится в локальной зоне. 3. Согласуйте политику: хранить UTC, логировать локальную зону отдельно при необходимости. 4. Обновите подключения: SET TIME_ZONE = ‘+0:00’. 5. Напишите миграции/скрипты для корректной конвертации исторических данных. 6. Добавьте тесты и мониторинг для аномалий временных данных.

Примеры тестовых случаев

Создать запись в UTC и прочитать её, преобразовав в несколько целевых зон, проверить ожидаемые смещения.
Запросить диапазон, включающий переход на летнее время, и сравнить количество строк с эталонным результатом.
Выполнить агрегацию по месяцу для случая конца/начала года и проверить, что подсчёт правильный.

Заключение

Чёткая политика хранения времени и правильное использование SQL‑функций значительно упрощают работу с датами. Храните в UTC, избегайте вызовов функций в WHERE для индексируемых колонок, и используйте встроенные инструменты MySQL для преобразований и арифметики. Это уменьшит количество багов и упростит масштабирование логики времени в приложении.

Важное: если вы новичок в SQL, начните с простых запросов и постепенно добавляйте проверки на временные зоны и границы.

Источники знаний: официальная документация MySQL по функциям даты/времени и опыт практических сценариев работы с временными метками.