Запуская наш проект в регионе, где часовой пояс был отличен от московского, мы столкнулись с проблемой разницы местного времени и времени сервера (московский часовой пояс). Надо сказать, что логика работы проекта сильно привязана к датам и времени и оставлять дату в московском времени было нельзя. Практически все даты у нас хранились в MySQL базе в формате DATETIME, что, как в последствии оказалось, не лучшим образом подходит для организации работы приложения в нескольких часовых поясах.
Тип данных DATETIME служит для хранения даты и времени в диапазоне 1000-01-01 00:00:00 — 9999-12-31 23:59:59. При выборке дата извлекается точно та же, что была записана, вне зависимости от временных настроек базы. Конвертировать дату в определенный часовой пояс можно функцией CONVERT_TZ или корректировать вручную другими способами.
Другой тип для хранения дат, TIMESTAMP, является единственным типом для хранения даты в MySQL, зависящим от временной зоны. Этот тип данных при сохранении конвертирует время из местного в UTC, а при извлечении обратно с учетом зоны. Что важно, все операции и вывод аналогичны типу DATETIME (начиная с MySQL 5.0).
Также этот тип имеет очень удобные особенности — позволяет устанавливать NOW() как значение по умолчанию, а также как значение при обновлении записи.
Недостатком TIMESTAMP является ограниченный диапазон дат (1970 — 2038 год). По этой причине он не подходит для хранения исторических событий или событий далекого будущего, но тут и часовые пояса не критичны.
Итак нам требовалось найти решение, не требующее масштабного переписывания кода и SQL запросов, поэтому варианты корректировать время в запросах или средствами PHP не выглядели удачными.
В результате было сделано следующее:
— Все даты в базе были конвертированы в TIMESTAMP
— При инициализации пользовательской сессии добавился примерно такой код, устанавливающий локаль для MySQL и PHP:
<?php
date_default_timezone_set($user->timezone);
db::q("SET `time_zone`='".date('P')."'");
?>
Функция date_default_timezone_set() принимает параметром строку-идентификатор временной зоны, например «Europe/Moscow» и устанавливает ее для всех функций дат и времени.
SQL запрос же устанавливает временную зону для всех запросов в рамках текущего подключения ( подробнее в официальной документации).
В итоге данное решение работает замечательно в разных часовых поясах и является рабочим способом решения проблемы.
Выводы этого поста простые: для проектов с широким географическим охватом для хранения временных отметок следует всегда использовать тип TIMESTAMP, что позволит в дальнейшем избежать головной боли.
UPD: Как справедливо заметил homm, для базы правильнее задавать время не смещением (как в коде выше), а идентификатором временной зоной, иначе не учитываются переходы на летнее/зимнее время и другие исторические изменения для данной зоны. В этом случае надо загрузить в базу соответствующие данные с помощью mysql_tzinfo_to_sql и своевременно их обновлять.