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

    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
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 8

      +1
      Ну поскольку упор на MySQL, то вставлю свои 5 копеек.
      Хранить и timestamp и использовать:

      use DateTime;

      my $t = time(); // immitate timestamp column from DB
      my $date1 = DateTime->from_epoch( epoch => $t, time_zone => 'Europe/Kiev');
      my $date2 = DateTime->from_epoch( epoch => $t, time_zone => 'Europe/Moscow');
      my $date3 = DateTime->from_epoch( epoch => $t, time_zone => 'Europe/Berlin');

      print join("\n", $date1->hms(), $date2->hms(), $date3->hms());

      и получим
      18:33:14
      19:33:14
      17:33:14
        –2
        Вы что делаете?
        date_default_timezone_set('Europe/Moscow');
        все!
          0
          А я храню дату и время в unsigned int (unix timestamp), это плохо?
            0
            Дык MySQL тип timestamp делает тоже самое, в статье поднимается вопрос, как потом оперировать часовыми поясами.
              0
              Относительно тематики поста — это даже лучше, так как не нужно писать в запросе функцию UNIX_TIMESTAMP() для получения сохраненного значения даты.
              И больше вероятность, что новый программист, который будет завтра помогать тебе с проектом, задаст вопрос: «Почему в int и как с этим работать?» прежде чем преобразовывать дату в читабельный формат средствами mysql.
              И 2038 год не страшен).

              Но я все же считаю это плохим тоном.
              К примеру: мы пишем умный редактор таблиц (phpMyAdmin) — и нужно предложить пользователю ввести значение в поле даты. Мы проверяем: если поле типа TIMESTAMP — выдаем ему календарик, иначе — поле для ввода числа.
              тут этот вопрос уже обсуждался
              0
              ТУТ этот вопрос уже обсуждался: habrahabr.ru/blogs/mysql/61391/#comment_1682725
                0
                По-этому лучшее решение, если вы используете PHP я вижу в следующем:
                1. Хранить дату в mysql в TIMESTAMP
                2. Всегда в PHP задавать временную зону пользователя функцией
                date_default_timezone_set()
                3. Доставать значение TIMESTAMP из БД и переводить его в нужный формат в PHP функцией
                date($format, $timestamp);

                Единственный недостаток, как по мне, в таком случае если нада сделать группировку по дате. Если не задать таймзону в mysql и именно не в формате "+02:00", а 'Europe/Kiev', то и группировка пойдет в UTC и соответственно некоторые пункты попадут не в те дни. И уже на стороне пхп с этим ничего не сделаешь. Как вариант, может именно для таких выборок задавать таймзону для mysql «SET time_zone = 'EST'; Select… GROUP BY `created_at`»
                  0
                  Прошу извинения за вопрос не совсем по теме: какой командой в PHPmyAdmin (на удаленном сервере) можно преобразовать множество дат типа «UNIX время» (из одного столбца) в дату типа «datetime» стандартного формата YYYY-MM-DD HH:MM:SS ?

                  Only users with full accounts can post comments. Log in, please.