Pull to refresh

Хранение даты в mysql с учетом часового пояса

Reading time 2 min
Views 35K
image
Статья о том, как избежать путаницы с датами, хранимыми в mysql.
Эти путаницы возникают по двум причинам:
1. Разные территории нашей планеты имеют разный сдвиг времени.
2. Некоторые страны переходят на летнее время и обратно(карта со странами, переходящими на летнее время).

Многие решают эти проблемы по-разному. Кто-то делает сдвиг даты в SQL запросах, кто-то в php. Кто-то хранит даты в TIMESTAMP, кто-то в DATETIME. Я переискал много источников, но нигде не нашел верного решения данной проблемы на русском. В родной документации по mysql нашел информацию о том, как добиться корректной автоматической конвертации TIMESTAMP в локальное время, но тут тоже свои подводные камни.

Если в php настройка временной зоны делается просто, в mysql возникают сложности, особенно, если доступ к mysql серверу вам ограничен и там еще не установлены некоторые таблицы.

Чем отличаются форматы хранения даты TIMESTAMP и DATETIME, я надеюсь вы знаете.
Значение TIMESTAMP является абсолютным значением времени, которое не зависит от локальных настроек. В любой стране, на любом компьютере оно одно и тоже. По-этому, в большинстве случаев лучше хранить дату именно в TIMESTAMP.

Если вы сделаете запрос
SELECT `timestamp_field` FROM table
вы получите дату в формете «гггг-мм-дд чч: мм: сс».
Казалось бы, все просто. И эта простота слишком соблазнительна и именно из за нее могут возникать проблемы, ведь нужно указать какая временная зона у пользователя, для которого достается дата.
И решение есть: можно задавать зону запросом

SET time_zone='+03:00'

где '+03:00' — это текущий сдвиг даты от лондонского нулевого времени.
Но после этого запроса mysql начинает неверно обрабатывать переход на летнее время.
Если значение time_zone установлено в SYSTEM (по умолчанию), переход на летнее время обрабатывается верно.
Например, сейчас лето и действует летнее время.
Нам нужно перевести в локальное время TIMESTAMP, хранимый в БД. Его значение — 946681261 (это '2000-01-01 01:01:01' по Киевскому времени)
Делаем запрос:

SET time_zone = 'SYSTEM';
SELECT NOW(), FROM_UNIXTIME(946681261);


Получаем результат:
NOW() FROM_UNIXTIME(946681261)
2009-09-14 16:00:40 2000-01-01 01:01:01


Теперь изменим временную зону
SET time_zone = '+03:00';
SELECT NOW(), FROM_UNIXTIME(946681261);


NOW() FROM_UNIXTIME(946681261)
2009-09-14 16:00:40 2000-01-01 02:01:01

Как видите, вместо ожидаемого '2000-01-01 01:01:01' мы получили время на час больше. Тогда как текщее время отображается правильно.
Можно настроить mysql нужным образом (хорошо, если есть полный доступ к БД) и задавать временную зону в формате

SET time_zone = 'America/Toronto';

После этого может показаться, что все проблемы исчезнут. Но тут тоже могут быть сюрпризы.
Например, в 2007 году в США, Мексике и Канаде произошли некоторые изменения в правилах перехода на дневное время. А так как данные о временных зонах вносятся вручную в mysql, то вы должны следить за актуальностью данных. Хуже того: например, если вы используете язык PHP, вы должны следить за тем, что бы данные о временных зонах в mysql соответствовали данным в PHP — а это уже гораздо сложнее.

По-этому лучшее решение, если вы используете PHP я вижу в следующем:
1. Хранить дату в mysql в TIMESTAMP
2. Всегда в PHP задавать временную зону пользователя функцией
date_default_timezone_set()
3. Доставать значение TIMESTAMP из БД и переводить его в нужный формат в PHP функцией
date($format, $timestamp);

По крайней мере до 2038 года вы сможете быть уверены в том, что путаницы с датами нет.

Update: перенес в блог MySQL
Tags:
Hubs:
+5
Comments 8
Comments Comments 8

Articles