Решение проблемы часовых поясов в веб-приложении



    Запуская наш проект в регионе, где часовой пояс был отличен от московского, мы столкнулись с проблемой разницы местного времени и времени сервера (московский часовой пояс). Надо сказать, что логика работы проекта сильно привязана к датам и времени и оставлять дату в московском времени было нельзя. Практически все даты у нас хранились в 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 и своевременно их обновлять.

    Комментарии 42

      +10
      Веб-приложение веб-приложению рознь. У нас тоже была подобная проблема. Один из супругов в командировке в Лондоне, супруга дома в Киеве. Оба одновременно делают запись о расходах в свой общий бюджетный аккаунт. Помимо вопроса «как хранить» есть еще вопрос «как отображать». В нашем случае веб-приложение — полностью на JS а данные приходят в браузер сыром виде. Поэтому хранятся и передаются данные в UTC, а уже в браузере преобразовываются во временную зону ОС. И наоборот: перед отправкой даты временные метки переводятся из локальных в UTC/
      Т.е. если супруги запишут одновременно операции (в 1:00 по Киеву и в 23:00 по Лондону) увидят эти операции одновременными, но у супруга они обе будут датированы допустим 17-тым июля, а у супруги — 18-тым.
        0
        Собственно по-моему самый логичный подход к проблеме — хранить данные даты и времени в UTC в любом формате, а ввод-вывод уже осуществлять через прямые и обратные преобразования. Что касается преобразований, то тут полагаться на какой-то конкретный инструментарий следует достаточно осторожно, так как данные о преобразованиях для временных зон периодически меняются (как в России в этом году например), а отражается это в изменениях функционала инструментов не всегда достаточно оперативно и адекватно. В частности PHP в этом плане сильно хромает.
          0
          В частности PHP в этом плане сильно хромает.

          Разве PHP пользуется не системными функциями?
            0
            Ну по идее было бы логично если бы он ими пользовался, но как я понял, что для работы с часовыми поясами всё же нет, в windows-варианте по крайней мере.
          0
          А можно поподробней про вашу систему преобразования времён в браузере? Как-то это автоматизированно?
            0
            Если речь о чем-то вроде date_default_timezone_set — то нет. Но, скажем так, при хорошо спроектированной архитектуре это ведь не проблема. Если за обработку полученных с сервера данных, и за подготовку их к отправке отвечает некоторая прослойка — то совершенно не проблема заставить ее, в случае дат — делать прямое/обратное преобразование с учетом Date().getTimezoneOffset(). А если асинхронность приложения сделана кусочно-гнездовым способом — то тут уж неча на зеркало пенять…
            • НЛО прилетело и опубликовало эту надпись здесь
                0
                Я писал, веб-приложение веб-приложению рознь. Если манипуляции (в том числе отображение) вынесены на клиентскую часть, то как там оно хранится — личное дело сервера. В том смысле, что данные можно (хотя нет — желательно) хранить в некотором унифицированном виде и никак не преобразовывать — только отдавать и записывать. Тогда и пример с супругами в Лондоне и Киеве будет работать логично.
                А вот если клиент тонкий, то естественно, часть (или все) подобных манипуляций с датами происходит на сервере. Что вызывает естественное желание спихнуть эту задачу на встроенные возможности того же СУБД.
          –3
          Ничего не мешает продолжать хранить в datestamp:
          SELECT DATE_ADD(date_field, INTERVAL diff HOUR) AS date_field, где diff — разница часовых поясов между пользовательским (в настройках пользователя, либо всего домена сайта) и серверным.
            +4
            Спасибо
              0
              С учетом того, что в России сейчас в плане часовых поясов вечное лето, будет ли по осени вызов
              date_default_timezone_set('Europe/Moscow')
              корректным?
              Или же php эти настройки жестко не вшиты и есть некая системная функция преобразования, которая выдаст корректное значение?
              'Europe/Moscow' => +4
                –1
                Насколько мне известно он и сейчас в разных версиях php является не всегда корректным. На php в этом плане надеяться не стоит.
                  +2
                  Кхм, ну как бэ… что тут сказать: довольно долго наблюдал некорректные данные зоны для Новосибирска в свежих версиях php.
                    0
                    А почему на MySQL стоит надеяться? У него принцип тот же самый. И у операционки. Смысл тот же везде пока что — закачиваем базы переходом на летнее/зимнее время и расчитываем сдвиг, исходя из этого.
                      0
                      Конечно принцип везде одинаков, только не всё есть возможность обновлять (виртуальный хостинг бывает в этом плане совсем запущен), не всё одинаково хорошо обновляется. Потому часто применяются «грабли» с использованием комбинации временного смещения и флага использования летнего времени.
                        0
                        Так если не обновлять базу, то откуда брать информацию, что время летнее или зимнее в определенный момент времени?
                          0
                          В смысле? Определить к какому времени относится временная отметка — арифметическая задача, а необходимость добавлять смещение для летнего времени определяется флагом. Не разу не видели реализации? Профиль пользователя имеет настройки часового пояса, но не в виде выбора готовых ID поясов, а в виде указания смещения от UTC и флага указывающего на необходимость автоматического перехода на летнее время.
                            0
                            Не совсем понимаю о каком профиле пользователя идет.
                            В базе хранится дата/время по UTC: 2011-03-13 00:00:00. Сколько это будет для Лос-Анжелеса? Сколько для Киева? Сколько для Москвы?
                              0
                              Если для известно смещение по времени относительно UTC, известно используется ли в данных местах переход на летнее время (эти данные задаются вручную), то мы легко можем определить относится ли данное время к летнему и вычислить результат, ведь так?
                              Если нет базы часовых поясов или нет возможности ей воспользоваться или в ней некорректные данные, то как иначе решать задачу? Задавать нужные данные вручную.
                                0
                                Зачем делать вручную то, что уже кто-то сделал и поддерживает?
                                  0
                                  Если всё работает правильно и адекватно и своевременно поддерживается, то не надо.
                    +1
                    Вызов date_default_timezone_set('Europe/Moscow') будет корректен всегда, пока вы вовремя обновляете пакет tzdata.
                      +2
                      обновлять надо с умом — у нас однажды после обновления полностью слетела зона Europe/Moscow — она стала идентична UTC
                        0
                        В чем была причина?
                          0
                          Не разобрался — насколько я понял — косячный билд
                      +2
                      Побуду я сегодня КО. Существует специальная база данных, хранящая как часовые пояса, так и смещения/переходы на летнее/зимнее время (также называемая базой данных Олсона, по имени автора), причем все из них, т.к. чтобы узнать корректный диапазон между двумя датами, нужно также знать прошлые переходы на летнее/зимнее время. Версия этой базы данных входит в поставку PHP и своевременно обновляется, статус можно посмотреть, например, здесь: www.php.net/manual/en/timezones.php
                        0
                        Россия не единственная такая страна. Многие пояса какое-то время попереключались между летними/зимними временами, а потом перестали. Все это хранится в спец.базах.
                        +5
                        Вы не постигли дзен. Смещение часового пояса, которое вы задает для mysql ничего не говорит об этом смещении час или год назад. А есть переходы на зимнее/летнее время, есть перенос часовых поясов. Ничего этого вы не учитываете. Задавать нужно не смещение а саму временную зону, предварительно загрузив в mysql tzdata.
                          +4
                          Если вы действительно хотите «избежать головной боли», то не нужно хранить timezone-aware даты в базе данных вообще. Конвертируйте всё в UTC, храните в базе «простую» дату, ничего не знающую о таймзонах, и все вычисления также выполняйте в UTC. И только лишь при отображении данных пользователю конвертируйте их в нужную ему таймзону, на основании пользовательских настроек, используя свежую tzdata естественно.

                          Да и серверное время, и все настройки PHP рекомендуется в UTC выставлять, дабы не было сюрпризов. Keep it stupid simple, как говорится.
                            0
                            Это не работает при обработке, например, календарных событий, которые почти всегда привязаны к локальному времени.
                            +2
                            Проблема еще в том, что поле TIMESTAMP не может быть одновременно не NULL и без ON UPDATE = CURRENT TIMESTAMP…
                              +1
                              Может. При создании поля надо явно прописывать DEFAULT. Если же этого не делать и опустить условие ON UPDATE, оно равносильно DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                                0
                                OK, не может быть не NULL, без DEFAULT и не CURRENT TIMESTAMP ;-)
                              0
                              Вчера с удивлением обнаружил что в Django вопрос с временными зонами тоже через задницу решен. Пришлось извращаться с кастомными полями ORM…

                              Могу, кстати, посоветовать статью очень подробную и с рецептами asvetlov.blogspot.com/2011/02/date-and-time.html если коротко — внутри приложения всегда(!!!) работаем с UTC, в БД храним в UTC, преобразуем в/из только в момент показа пользователю/получения данных из формы и бед не знаем.
                                0
                                Об этом кто только не писал, вот например последний пост небезызвестного Armin Ronacher о работе с тз в питоне — всё разложено по полочкам (правда на англ.), с какими там полями вам пришлось в джанге извращаться не знаю, если у вас там всё итак в UTC.
                                  0
                                  Ну это то да, на самом деле во временных зонах все очень просто. Но только если закладывать это с самого начала. Как и с локализацией и с юнит-тестами. Если же внедрять в уже готовое приложение, то затраты будут несравненно больше.

                                  Меня же удивило больше всего то, что даже в таких крупных и известных проектах эта проблема решается как-то никак практически.
                                    0
                                    Просто стандартная джанговская ORM DateTimeField возвращает naive время. Мне хочется работать с aware — временем, соответственно нужно сделать подкласс этого поля которое автоматом проставляет UTC… Казалось бы просто — ан нет — умная Django для MySQL выдает Exception если ему скормить datetime с проставленной tzinfo а для постгреса сохраняет ТЗ в соответствии с настройкой TIME_ZONE… В общем нужно прилично посидеть и поковыряться чтобы можно было более-менее комфортно работать.
                                    +1
                                    работаем с UTC, в БД храним в UTC, преобразуем в/из только в момент показа пользователю/получения данных из формы и бед не знаем.

                                    Не всегда так можно. Было дело… надо было выбирать из базы данные разбитые по дням. А дни — не по UTC, конечно же :) Проще таки группировать данные уже при выборке.
                                      +1
                                      > в Django вопрос с временными зонами тоже через задницу решен
                                      Согласен. Поэтому пришлось форкнуть django-timezones и серьезно переписать.
                                      github.com/homm/django-timezones/blob/test/timezones/fields.py
                                      Длинные комментарии почти к каждой строчке, чтобы самому не запутаться, что откуда берется.

                                      В моделях то выглядит так:
                                      class City(DomainAttributes):
                                          TIMEZONE_CHOICES = make_timezone_choices(pytz.country_timezones['ru'])
                                          timezone = models.CharField(u'Временная зона', max_length=30, choices=TIMEZONE_CHOICES, blank=True, null=True, default=None)
                                          created = tz_fields.LocalizedDateTimeField(u'Дата создания', auto_now_add=True, default=datetime.now, editable=False, timezone=u'timezone')
                                      
                                      class Restaurant(DomainAttributes):
                                          city = models.ForeignKey(City, verbose_name=u'Город')
                                          created = tz_fields.LocalizedDateTimeField(u'Дата создания', auto_now_add=True, default=datetime.now, editable=False, timezone=u'city__timezone')
                                      
                                      class Order(models.Model):
                                          restaurant = models.ForeignKey(Restaurant, verbose_name=u'Ресторан')
                                          created = tz_fields.LocalizedDateTimeField(u'Дата создания', auto_now_add=True, default=datetime.now, editable=False, timezone=u'restaurant__city__timezone')


                                      Поля возвращаются в указанной таймзоне и с привязанной таймзоной. Т.е. их можно безопасно вычитать и сравнивать. При выборке, например, Order не забывайте делать .select_related('restaurant__city'), иначе для каждого заказа LocalizedDateTimeField будет ходить к базе. Правда, вы все равно можете получать created_utc в сыром виде, тогда конечно select_related и поход в базу не нужен.
                                        0
                                        О, спасибо! Думаю в этом проекте оставлю свои костыли в последующих попробую ваши). Еще-бы README хотябы немного кто-нибудь заполнил ^_^.
                                        Просто у github.com/brosner/django-timezones столько форков, что все их просмотреть и выбрать наиболее доработанный довольно сложно, а основная ветка какая-то недопиленная тоже — как я понял, возвращает это поле все-равно naive datetime.

                                        > Поля возвращаются в указанной таймзоне
                                        В таймзоне проставленной в City.timezone?

                                        > вы все равно можете получать created_utc в сыром виде
                                        Ну вот это по-моему самый удачный вариант. В сочетании с фильтром для шаблона, умеющем приводить ТЗ к пользовательскому, должно довольно хорошо работать.
                                          0
                                          Если у вас таймзона привязана не к городам, а к пользователям, то вам для поля нужно задать не лукап timezone=u'restaurant__city__timezone', а колбак, который вернет что нужно. Одна проблема — в Джанге сложно получить request и пользователя в произвольной функции, но все же возможно.
                                      0
                                      1. Не создавайте себе проблем (хранение не UTC дат), тогда не нужно будет их преодолевать (масштабное переписывание кода и SQL запросов).
                                      2. Пост конечно нужный. Как раз для случая, когда первое не соблюдалось.

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

                                      Самое читаемое