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

Используйте дату и время правильно в SQL: календарь и часы на экране

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

Основные понятия

UTC — всемирное координированное время, рекомендованный стандарт для хранения временных значений. Коротко: храните в UTC, отображайте в локальном времени.
TIMESTAMP — тип данных MySQL, который хранит точки времени и учитывает смещение сессии при отображении (конвертируется из/в время с учётом временной зоны сервера/сессии). Используйте, если хотите, чтобы СУБД помогала с конвертацией между зонами.
DATETIME — хранит «сырую» дату и время без привязки к часовому поясу; остаётся неизменным при извлечении. Подходит для времён без привязки к UTC (например, расписание открытий).

Работа с часовыми поясами

Важно стандартизировать рабочий протокол: при каждом подключении к базе переключайте часовую зону на UTC.

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');

Результат: 2021-02-04 13:47:23.

Советы при работе с часовыми поясами:

Держите конфигурацию часовой зоны приложения и БД в документации проекта.
Для веб‑сервисов чаще всего проще сохранять в UTC и конвертировать в UI.
Если нужно вычислять интервалы «в локальном времени» (например, дневные отчёты по локальному дню пользователя), конвертируйте значения в локальную зону до группировок/фильтрации.

Important: В MySQL TIMESTAMP участвует в автоматической пересылке значений в соответствии с сессионной TZ, а DATETIME — нет. Выберите тип осознанно.

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

Для арифметики используйте DATE_ADD() и DATE_SUB(). Первый аргумент — исходная дата, второй — интервал в формате interval <число> <единица>.

Примеры:

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

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

Если нужно получить записи за последние 45 минут:

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

Хорошая практика: используйте интервалные функции для читабельности, но помните про оптимизацию (см. раздел про индексы).

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

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

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

Чтобы получить разницу в секундах, можно использовать TO_SECONDS():

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

Для других единиц (минуты, часы) можно комбинировать или использовать TIMESTAMPDIFF(unit, datetime1, datetime2).

SELECT TIMESTAMPDIFF(SECOND, '2021-02-05 11:56:41', now());

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

MySQL предоставляет набор функций для получения составляющих даты/времени. Все они принимают один аргумент — дату/время.

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

SELECT HOUR(now());

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

Список часто используемых функций:

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

Пример: получить месяц и год создания пользователей:

SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Группировка по периоду даты

Дата-функции полезны для агрегации. Пример: суммарная выручка по месяцам в 2020 году:

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

Важно: для сохранения производительности индексов лучше избегать применения YEAR() или других функций к индексируемым полям в WHERE. Вместо этого используйте BETWEEN или явные границы диапазонов.

Производительность: индексы и функции

Проблема: выражения вида WHERE DATE(created_at) = ‘2021-02-01’ делают индекс по created_at бесполезным, потому что функция применяется к столбцу. Решения:

Преобразуйте условие в диапазон:

WHERE created_at >= '2021-02-01 00:00:00' AND created_at < '2021-02-02 00:00:00'

Создайте функциональный индекс (если поддерживается вашей версией MySQL / MariaDB).
Храните отдельное поле с нормализованной датой (например, created_date DATE) и индексируйте его, если вы часто выполняете фильтрацию по дате без времени.

Ошибки и когда методы не подходят

Хранение локального времени в DATETIME без информации о временной зоне делает невозможным корректное восстановление локального времени, если пользователь переезжает.
Конвертировать все даты в локальную зону в БД неудобно при распределённой системе; лучше выполнять конвертацию в приложении.
Для исторических времён, затронутых переходом на летнее время или изменением правил TZ (например, правительственные изменения), хранение UTC + метаданных о локальной зоне полезнее простого локального времени.

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

Хранение UTC + временная зона пользователя: полный контроль, позволяет корректно восстанавливать локальное время и учитывать переходы DST.
Хранение UTC только: простая модель для большинства приложений, подходит когда важна последовательность событий.
Хранение DATETIME локально: подходит для расписаний (например, «магазин открывается в 09:00 по местному времени»), но требует аккуратного проектирования при миграциях.

Уровни зрелости №:

Начальный: хранить в локальном времени (риск).
Базовый: хранить в UTC, конвертировать на клиенте.
Продвинутый: хранить UTC + зону пользователя, логировать исходный offset и использовать TZ-базы (IANA) при расчётах.

Быстрая памятка — cheat sheet функций

-- Переключить сессию на UTC
SET TIME_ZONE = '+0:00';

-- Перевести время в другой часовой пояс
SELECT CONVERT_TZ(now(), '+0:00', '+03:00');

-- Добавить/вычесть интервалы
SELECT DATE_ADD(now(), interval 1 day);
SELECT DATE_SUB(now(), interval 2 week);

-- Разница
SELECT DATEDIFF('2021-02-10', '2021-02-01');
SELECT TIMESTAMPDIFF(SECOND, '2021-02-05 11:56:41', now());

-- Извлечь часть
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM events;

Проверки и критерии приёмки

Результаты выборки за границы дня совпадают с ожидаемым (проверить на нескольких временных зонах).
Индекс по created_at используется (подтвердить через EXPLAIN) при запросах по диапазону.
Конвертация времени для UI даёт те же значения, что и тестовые примеры для DST и стандартного времени.

Ролевые чек-листы

Разработчик:

Сохранять время в UTC.
Использовать DATE_ADD/DATE_SUB и TIMESTAMPDIFF.
Не применять функции к индексным столбцам в WHERE.

DBA:

Проверять использование индексов через EXPLAIN.
Настроить системную временную зону и инструкцию по переключению сессий.
Обеспечить корректные timezone tables (LOAD DATA) если требуется CONVERT_TZ по имени зоны.

Аналитик/BI:

Использовать BETWEEN для фильтрации по периодам.
Проверять смещение времён при агрегации по дням/месяцам.

Мини‑методология для миграции времени в UTC

Проинвентаризировать все таблицы со временными полями.
Определить, какие поля уже в UTC, а какие в локальном времени (по коду и документации).
Для локальных DATETIME: определить смещения, конвертировать в UTC и сохранять обратно, пометив операцию миграции в changelog.
Обновить код, чтобы при подключении выполнять SET TIME_ZONE = ‘+0:00’.
Прогнать тесты на выборки границ (DST, переход года).

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

Отметьте, что временные метки могут считаться персональными данными, если по ним можно идентифицировать поведение пользователя. При требованиях GDPR/локального законодательства:

Минимизируйте хранение времени, если оно не нужно.
Установите политики удаления и ретеншна для логов.

Примеры отказов и крайние случаи

Если приложение работает в нескольких странах и важно сохранять местное время события (напр., отметка присутствия), храните UTC + original_offset или IANA timezone, чтобы можно было корректно восстановить локальное представление.
Для финансовых расчётов используйте фиксированные интервалы и тестируйте на границах кварталов/лет.

Визуальное руководство для выбора подхода (decision tree)

flowchart TD
  A[Нужно ли сохранять локальное временное представление события?] -->|Да| B[Хранить UTC + временную зону пользователя]
  A -->|Нет| C[Хранить UTC]
  B --> D{Требуется автоматическая конверсия в БД?}
  D -->|Да| E[Использовать TIMESTAMP и CONVERT_TZ]
  D -->|Нет| F[Хранить DATETIME UTC и конвертировать в приложении]
  C --> G{Нужна агрегированная аналитика по локальным дням?}
  G -->|Да| H[Перед агрегацией конвертировать в локальную зону]
  G -->|Нет| I[Оставить в UTC]

Краткое резюме

Всегда выбирать и документировать модель хранения времени (рекомендуется UTC).
Понимать разницу между TIMESTAMP и DATETIME.
Использовать DATE_ADD/DATE_SUB, CONVERT_TZ и TIMESTAMPDIFF для операций с временем.
Не применять функции к индексируемым столбцам в WHERE — вместо этого использовать диапазоны.

Notes: Регулярно проверяйте поведение запросов на границах времени (переходы на летнее/зимнее время, изменение правил TZ) и включайте тесты для таких сценариев.