Преобразование времени UTC в локальное в MySQL: функция CONVERT_TZ

Быстрый ответ

Да, MySQL может преобразовать время, хранящееся в формате UTC, в локальное время. За это отвечает функция CONVERT_TZ():

SELECT CONVERT_TZ(utc_column, '+00:00', 'Ваш/Часовой_Пояс') AS local_time FROM table;

Однако стоит помнить, что если информации о временных зонах в MySQL нет, функция CONVERT_TZ() вернёт NULL. В случаях, когда необходимо учесть переход на летнее/зимнее время (например, в EST, UTC-5), можно использовать:

SELECT TIMESTAMPADD(HOUR, -5, utc_column) AS local_time FROM table;

Не забывайте корректировать смещение часового пояса, соответствующее вашей географической локации.

Как точны данные о времени в вашей базе данных?

Для корректного использования функции CONVERT_TZ() важно иметь актуализированные данные о временных зонах. В противном случае, ваш MySQL будет работать некорректно, как часы, идущие не вовремя. Обновите данные о временных зонах с помощью mysql_tzinfo_to_sql, иначе вместо ожидаемых результатов вы получите "NULL".

Не считайте функцию CONVERT_TZ() за данность. Проверьте её работу с вашей версией MySQL до того, как начать использовать. Обратите внимание на возможное снижение производительности при работе с большим объемом данных.

Особые случаи использования

Как учесть переход на летнее время

Переход на летнее/зимнее время может внести неожиданность в преобразование времени. Чтобы избежать проблем, используйте названия часовых зон вместо учета количества часов от UTC. Это будет работать, как своего рода защита от ошибок!

Подробнее об этом расскажет наш спикер на видео

Если нет названий часовых зон, используйте смещения

Если нет возможности использовать названия часовых зон, можно использовать смещение от времени UTC. Напоминаем: '+00:00' — это и есть время UTC. Для других смещений используйте соответствующее смещение времени.

Учитывайте своих пользователей

С помощью функции CONVERT_TZ() вы можете предоставить данные во временной зоне ваших международных пользователей:

SET @user_timezone = 'Asia/Tokyo';
SELECT CONVERT_TZ(utc_column, '+00:00', @user_timezone) AS local_time FROM table;

Визуализация

Процесс преобразования временных зон в MySQL может быть описан следующим образом:

Время в формате UTC 🕛 (универсальный формат) -> Корректировка временной зоны ✂️ -> Локальное время 🕰️ (индивидуально для каждого пользователя)

Пример:

-- Подстроим время UTC под ваш ритм!
SELECT CONVERT_TZ(`utc_column`, '+00:00', 'Europe/Paris') AS `local_time` FROM `events`;

Получим:

| Время в формате UTC 🕛 | Локальное время 🕰️  |
| ------------------------- | ------------------------- |
| 2023-04-01 12:00:00       | 2023-04-01 14:00:00       |
| 2023-04-01 18:00:00       | 2023-04-01 20:00:00       |

Переводим время на ваш личный уровень. 🕰️✨

Ограничения и способы их обхода

Фильтрация данных по времени

Для фильтрации данных по времени лучше всего сравнивать их в формате UTC до их преобразования:

WHERE utc_column < UTC_TIMESTAMP() /* Наилучший формат для поиска данных — это формат UTC */

После осмотрите результаты, сконвертированные в локальное время:

SELECT CONVERT_TZ(utc_column, '+00:00', 'US/Pacific') AS local_time /* Смотрим результаты, представленные в вашем локальном времени */

Конвертация только времени. Почему бы и нет?

Преобразование только времени (без учета даты) возможно с помощью функции TIME в сочетании с CONVERT_TZ():

SELECT TIME(CONVERT_TZ(utc_time_column, '+00:00', 'US/Eastern')) AS local_time_only /* Так можно тоже */

Помните об ограничениях

• Использование функции CONVERT_TZ() в сложных запросах может замедлить выполнение.
• Строковые литералы часовых поясов могут создать путаницу. Используйте их осторожно.

Поддерживайте актуальность ваших данных о временных зонах

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

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql /* Время для обновления данных о временных зонах!*/

Используйте данные из серверного каталога /usr/share/zoneinfo, чтобы достигнуть нужной точности.

Полезные материалы

1. MySQL :: MySQL 8.0 Reference Manual :: 7.1.15 Поддержка временных зон в MySQL — подробное руководство по работе с временными зонами в MySQL.
2. Функция MySQL CONVERT_TZ() – w3resource — эффективное использование функции CONVERT_TZ().
3. MySQL :: MySQL 8.0 Reference Manual :: 14.7 Функции даты и времени — описание функций, работающих с датой и временем, в MySQL.
4. Ошибка MySQL #6882: Проблемы при репликации запросов в режиме master-slave — о проблемах, возникающих при преобразовании временных зон.