Особенности работы со временем в различных временных зонах

    В связи с тем, что накопилось несколько вопросов и решений по работе со временем, решил сделать небольшой обзор.


    Работа с различными типа данных в базах данных


    MYSQL

    В mysql существует несколько стандартных типов данных для представления времени, мы рассмотрим TIMESTAMP и DATETIME.
    В документации говорится, что к некоторым типам данных применяется политика конвертирования, а к некоторым — нет.
    На практике все намного интереснее. Рассмотрим несколько примеров:
    Создадим таблицу:
    create table xxxDate(ts TIMESTAMP NOT NULL, dt DATETIME NOT NULL);

    Установим текущую зону для Москвы (в Москве с недавних пор нет перехода на летнее время, и время UTC+4):
    set time_zone='Europe/Moscow';

    Создадим две записи с летним и зимним временем соответственно:
    insert into xxxDate values('2012-06-10 15:08:05', '2012-06-10 15:08:05');
    insert into xxxDate values('2012-12-10 15:08:05', '2012-12-10 15:08:05');

    Посмотрим, что показывает выборка этих дат из базы данных:
    select * from xxxDate;
    +---------------------+---------------------+
    | ts                  | dt                  |
    +---------------------+---------------------+
    | 2012-06-10 15:08:05 | 2012-06-10 15:08:05 |
    | 2012-12-10 15:08:05 | 2012-12-10 15:08:05 |
    +---------------------+---------------------+
    select UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(dt) from xxxDate;
    +--------------------+--------------------+
    | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(dt) |
    +--------------------+--------------------+
    |         1339326485 |         1339326485 |
    |         1355137685 |         1355137685 |
    +--------------------+--------------------+

    Видим, что в обоих колонках значения одинаковые, это происходит потому что функция UNIX_TIMESTAMP рассматривает значение аргумента в текущей зоне и конвертирует его в UTC. Очевидно, что одинаковые значения одинаково сконвертируются в одно и то же значение Mon, 10 Dec 2012 11:08:05 UTC.
    Теперь переезжаем в Лондон!
    set time_zone='Europe/London';
    select * from xxxDate;
    +---------------------+---------------------+
    | ts                  | dt                  |
    +---------------------+---------------------+
    | 2012-06-10 12:08:05 | 2012-06-10 15:08:05 |
    | 2012-12-10 11:08:05 | 2012-12-10 15:08:05 |
    +---------------------+---------------------+

    Тут нет ничего удивительного, согласно документации, TIMESTAMP, перед тем как вставиться в базу конвертируется в UTC, поэтому после того как мы сменили текущую зону база данных нам выдает значение этого времени в текущей зоне. Значения типа DATETIME не изменились.
    Теперь рассмотрим более детально работу алгоритма для Москвы. Значения для ts при вставке сконвертировались в UTC, и при выборке переводились в значения в соответствии с текущей зоной (как и для Лондона) 15 часов, а при выборе UNIX_TIMESTAMP — они просто отображались как они сохранены в базе.
    Теперь уже ожидаемый результат для Лондона:
    select UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(dt) from xxxDate;
    +--------------------+--------------------+
    | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(dt) |
    +--------------------+--------------------+
    |         1339326485 |         1339337285 | // 14h (dt)
    |         1355137685 |         1355152085 | // 15h (dt)
    +--------------------+--------------------+

    Значения ts не изменились, а значения dt рассматриваются как значения в текущий зоне, поэтому летнее время (первая запись) 1339337285 = Sun, 10 Jun 2012 14:08:05 GMT, а зимнее время (нижняя запись) 1355152085 = Mon, 10 Dec 2012 15:08:05 GMT.
    На всякий случай проверим поведение для UTC.
    set time_zone='UTC';
    select * from xxxDate;
    +---------------------+---------------------+
    | ts                  | dt                  |
    +---------------------+---------------------+
    | 2012-06-10 11:08:05 | 2012-06-10 15:08:05 |
    | 2012-12-10 11:08:05 | 2012-12-10 15:08:05 |
    +---------------------+---------------------+
    select UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(dt) from xxxDate;
    +--------------------+--------------------+
    | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(dt) |
    +--------------------+--------------------+
    |         1339326485 |         1339340885 | // 15h (dt)
    |         1355137685 |         1355152085 | // 15h (dt)
    +--------------------+--------------------+
    

    Все согласно прежнему описанию, значения ts не изменились, значения dt рассматриваются в текущей зоне, поэтому тоже не меняются (1339340885 = Sun, 10 Jun 2012 15:08:05 GMT; 1355152085 = Mon, 10 Dec 2012 15:08:05 GMT).
    Вывод:
    • При работе с DATETIME и переезде сервера (неправильной настройке временной зоны во время вставки или импорта данных) с потерей информации о времени смены временной зоны сервера/соединения вы потеряете информации о действительном времени событий. Например, мы создали записи в 15 часов по московскому времени (импортировали данные в базу из backup’а), потом настроили наш сервер на UTC и не заметили, что до этого временная зона была московской. В результате вместо 11 часов по UTC оба наших заказа теперь сделаны на 4 часа позже — в 15 часов, а могли бы быть и в другой день. Поэтому на мой взгляд работать надо с TIMESTAMP.
    • Так же, чтобы не возникало лишних проблем при отладке на сервере лучше иметь зону UTC, и работать с данными в UTC, а на клиентской части отображайте в той зоне, в которой хочет клиент.
    • Так же хороший пример в конце статьи feedbee.
    • Чтобы избежать проблем с leap second так же стоит работать с unix epochs в UTC (см. раздел про Leap second).


    SQLite3

    Рассмотрим ситуацию с sqlite3. Согласно документации в sqlite нет типа данных для сохранения времени, но есть функции для работы со временем, сохраненным в виде текста, числа с плавающей точкой и в виде целого числа. В целом эти представления принципиально ничем не отличается. Можно считать, что в sqlite текущая временная зона не используется, если вы не используете модификаторы localtime и utc. Например, вне зависимости от настроек системы, CURRENT_TIMESTAMP имеет значение в UTC.
    $ date
    Mon Dec 10 22:05:50 MSK 2012
    $ sqlite3
    sqlite> select CURRENT_TIMESTAMP;
    2012-12-10 18:06:05
    sqlite> select datetime(CURRENT_TIMESTAMP, 'localtime');
    2012-12-10 22:06:35
    

    Поэтому конвертируйте свои данные в вашей программе в utc и используйте unix epochs, чтобы не искать ошибки при парсинге строк.
    Функции для отладки:
    select strftime('%s', CURRENT_TIMESTAMP);
    1355162582
    select datetime(1355152085, 'unixepoch');
    2012-12-10 15:08:05
    

    Как пользователь видит время


    Если вы работаете с типом datetime и не конвертируете его, то пользователи запутаются во времени. Например, если два пользователя живут в разных временных зонах, то, видя одну и ту же строку времени без указания зоны, они будут думать о разных временах. Чтобы не повторяться, вот ссылка с примером.

    С-функции по работе со временем


    Во-первых, очень полезно ознакомиться с описанием работы со временем в glibc. Мы же рассмотрим несколько примеров, касающихся результатов работы нескольких функций в разных временных зонах. Оказывается, даже в документации говорится, что struct tm (далее broken-down time) обычно используется только для отображаения пользователям (из-за наглядности), т.е. в вашей программе лучше использовать другие более подходящие типы данных.
    Рассмотрим несколько примеров:
    Function: struct tm * localtime_r(const time_t *time, struct tm *resultp)

    Конвертирует simple time в broken-down time, выраженное относительно пользовательской зоны.
     time_t t = 1339326485; // 2012-06-10 11:08:05 (UTC)
     struct tm bdt;
     localtime_r (&t, &bdt);
     cout << bdt.tm_hour  << endl;
     cout << bdt.tm_isdst << endl;
     cout << bdt.tm_zone  << endl;

    зона в системе UTC Europe/Moscow Europe/London
    вывод hour 11 15 12
    вывод isdst 0 0 1
    вывод zone UTC MSK BST

     time_t t = 1355137685; // 2012-12-10 11:08:05 (UTC)

    зона в системе UTC Europe/Moscow Europe/London
    вывод hour 11 15 11
    вывод isdst 0 0 0
    вывод zone UTC MSK GMT

    Function: struct tm * gmtime_r(const time_t *time, struct tm *resultp)

    Возвращает значение для зоны UTC вне зависимости от зоны пользователя.
     time_t t = 1339326485; // 2012-06-10 11:08:05 (UTC)
     struct tm bdt;
     gmtime_r (&t, &bdt);
     cout << bdt.tm_hour  << endl;
     cout << bdt.tm_isdst << endl;
     cout << bdt.tm_zone  << endl;

    зона в системе UTC Europe/Moscow Europe/London
    вывод hour 11 11 11
    вывод isdst 0 0 0
    вывод zone GMT GMT GMT

     time_t t = 1355137685; // 2012-12-10 11:08:05 (UTC)

    зона в системе UTC Europe/Moscow Europe/London
    вывод hour 11 11 11
    вывод isdst 0 0 0
    вывод zone GMT GMT GMT

    Function: time_t mktime(struct tm *brokentime)

    (синоним timelocal, но редко встречается)
    Конвертирует broken-down time в simple time.
    Внимание: выставляет у аргумента текущую зону.
    Поле tm_zone не рассматривается как аргумент, считается, что время задано в текущей временной зоне и возвращается время в UTC.
     struct tm bdt;
     bdt.tm_sec  =  5; // 05 sec
     bdt.tm_min  =  8; // 08 min
     bdt.tm_hour = 11; // 11 h
     bdt.tm_mday = 10; // 10
     bdt.tm_mon  =  5; // 6th mon - Jun
     bdt.tm_year = 112;// 2012 - 1900
     bdt.tm_wday =  0; // ignored
     bdt.tm_yday =  0; // ignored
     bdt.tm_isdst=  0;
     bdt.tm_gmtoff= 0;
     bdt.tm_zone = "UTC";
     time_t t = mktime(&bdt);
     cout << t << endl;
     cout << bdt.tm_hour   << endl;
     cout << bdt.tm_isdst  << endl;
     cout << bdt.tm_gmtoff << endl;
     cout << bdt.tm_zone   << endl;

    зона в системе UTC Europe/Moscow Europe/London
    вывод t 1339326485 (Sun, 10 Jun 2012 11:08:05 GMT) 1339312085 (Sun, 10 Jun 2012 07:08:05 GMT) 1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)
    вывод hour 11 11 12
    вывод isdst 0 0 1
    вывод gmtoff 0 14400 (4*60*60) 3600 (1*60*60)
    вывод zone UTC MSK BST

    Обратите внимение на то, что поля tm_hour и tm_isdst изменились для Лондона, это часть процесса нормализации полей структуры broken-down time.
    теперь для
    bdt.tm_mon  = 11; // 11th mon - Dec

    зона в системе UTC Europe/Moscow Europe/London
    вывод t 1355137685 (Mon, 10 Dec 2012 11:08:05 GMT) 1355123285 (Mon, 10 Dec 2012 07:08:05 GMT) 1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)
    вывод hour 11 11 11
    вывод isdst 0 0 0
    вывод gmtoff 0 14400 (4*60*60) 0
    вывод zone UTC MSK GMT

    Function: time_t timegm(struct tm *brokentime)

    Работает в UTC.
    зона в системе UTC Europe/Moscow Europe/London
    вывод t 1339326485 (Sun, 10 Jun 2012 11:08:05 GMT) 1339326485 (Sun, 10 Jun 2012 11:08:05 GMT) 1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)
    вывод hour 11 11 11
    вывод isdst 0 0 0
    вывод gmtoff 0 0 0
    вывод zone GMT GMT GMT
    теперь для
    bdt.tm_mon  = 11; // 11th mon - Dec

    зона в системе UTC Europe/Moscow Europe/London
    вывод t 1355137685 (Mon, 10 Dec 2012 11:08:05 GMT) 1355137685 (Mon, 10 Dec 2012 11:08:05 GMT) 1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)
    вывод hour 11 11 11
    вывод isdst 0 0 0
    вывод gmtoff 0 0 0
    вывод zone GMT GMT GMT

    Вывод:
    Если вы хотите отобразить время пользователю в вашей программе на пользовательском компьютере, то используйте функции timelocal/localtime, если вы работает на сервере, то используйте функции timegm/gmtime. Так же, устанавливайте на сервере зону UTC, на случай, если вдруг кто-то из ваших коллег или в сторонней библиотеке использует *local* функции. Даже на компьюетере пользователя храните и работайте со временем в UTC, так, если он сменит свою зону, все даты останутся правильными.

    Примечание


    Настройка временных зон в linux

    Рассмотрим только deb-based дистрибутивы и пару железных методов по настройке временн`ой зоны.
    • Способ первый (работает на deb-based дистрибутивах):
      Выполнить в терминале команду и следовать инструкциям (“UTC” находится в разделе “Etc”):
      sudo dpkg-reconfigure tzdata
    • Способ второй (работает, наверное везде):
      Выполнить в терминале команду:
      sudo ln -sf /usr/share/zoneinfo/UTC /etc/localtime 

      и на всякий случай отредактировать /etc/timezone (если он есть)
    • Способ третий:
      Установить переменную окружения TZ в нужную зону, например:
      export TZ=Europe/London

    LEAP SECOND

    Вообще это отдельная тема, поэтому в этой статье нет примеров, касающихся leap second. Вы можете сами проверить как работают те или иные функции, а так же как ведут себя различные базы данных, вот примеры mysql.
    • UTC включает в себя leap second
    • Очень важное замечание:
      POSIX требует, чтобы time_t отсчитанное от 00:00:00 on January 1, 1970, UTC не включало leap seconds, но на практике иногда включает. Так же в зависимости от поддержки leap second по-разному работает функция difftime. Будьте внимательны.
    • Юлианский день так же не включает в себя leap second.
    • В mysql вы не увидите leap second, т.е. вместо 60 или 61 секунд(ы) всегда будет 59 (ссылка). Но при этом, все поддерживается и корректно работает, если вы имеете дело с unix epochs в UTC.
    • Общая рекомендация по sqlite: храните дату в виде целого числа (integer), в который уже включены leap seconds (как в mysql). Тогда вы всегда будете знать точное время.

    И еще

    • Если значение переменной time_zone (mysql) равно SYSTEM, то в качестве текущей зоны выбирается системная (которая была настроена в системе на момент запуска сервера).
    • http://www.onlineconversion.com/unix_time.htm сайт для конвертации unix time в обычное время
    • GMT — можно рассматривать как устаревшее понятие, поэтому в статье в основном используется UTC.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 13

      +4
      Я не использую способ
      sudo ln -sf /usr/share/zoneinfo/UTC /etc/localtime
      
      , потому что при обновлении системы или пакета tzdata файл /etc/localtime у меня перезаписывался на значение по умолчанию, а файла /etc/timezone в используемых мной дистрибутивах нет.
      В RedHat-подобных дистрибутивах рекомендуется такой алгоритм:

      1. Изменить файл /etc/sysconfig/clock (создать его при необходимости):
      ZONE="Europe/Kiev"
      UTC=true
      

      Здесь ZONE — часовая зона; все эти зоны находятся в каталоге /usr/share/zoneinfo/; UTC-идут ли внутренние часы компа по UTC(GMT) или по локальному времени (рекомендовано true (идут по GMT), но на компах с Виндой придётся ставить false)
      2. Теперь запустить tzdata-update:
      /usr/sbin/tzdata-update
      
        +2
        я всегда использую datetime:
        1. юзерскую дату конвертирую в UTC
        2. сохраняю в БД
        3. отображаю в зависимости от зоны юзера
        и никаких проблем с переездами по зонам не бывает
          0
          В вашем подходе безусловно есть плюс, т.к. диапазон значений TIMESTAMP сильно ограничен, в отличие от DATETIME.

          Но проблемы могут возникнуть при «переезде» сервера из одной зоны в другую. В статье есть пример, когда при разворачивании базы на другом сервере с настроенной зоной не на UTC (опустим причины, по которым это может произойти) могут возникнуть проблемы после конвертации значений DATETIME в unix-эпохи как на стороне базы, так и при работе с C-функциями.
            0
            В данном случае мы не зависим от текущей зоны, мы просто знаем что все значения в UTC, и можем конвертировать в любую таймзону (на уровне приложения, а не БД), т.е. получаем преимущества TIMESTAMP и DATETIME. Или же я не правильно Вас понял.
              0
              Согласен с вами. В связи с вашим комментарием сделаю дополнение:

              В смысле значений мы не зависим. Но давайте рассмотрим пример:

              — сначала вы берете эти данные из БД. Можно взять в виде «строки» или в виде unix-эпох (бывает, что ORM делает это за вас). Если вы выбираете в виде эпох, и сессионная зона подключения отличается от зоны, в которой создавалась запись, то результат будет другой. Если вы взяли значение в виде строки, то эту строку нужно парсить, чтобы скорректировать значение для пользовательской зоны.

              — теперь мы имеем в нашей программе дату, которую можно сконвертировать в пользовательское время. Для этого можно воспользоваться C-функциями и, чтобы не зависеть от настроек зоны в системе, лучше пользоваться timegm.
          +1
          и сессионная зона подключения отличается от зоны, в которой создавалась запись

          А разве нам важно из какой зоны создавалась запись, если мы храним ее в UTC? Единственное условие, при переезде с сервера на сервер, всегда ставить UTC в настройках подключения к БД, а если мы используем только DATETIME, даже этого не придется делать.
            +1
            Единственное правильное — просто следить чтобы при любых операциях над БД и прочих манипуляциях (до отображения юзеру) время было в UTC. И всё, все перечисленные проблемы не будут существовать как класс при любых реформах и переездах серверов.

            з.ы.
            Общая рекомендация по sqlite: храните дату в виде целого числа (integer), в который уже включены leap seconds (как в mysql). Тогда вы всегда будете знать точное время.
            Это в какой это integer включены leap seconds? Простой timestamp? Вы что-то путаете, туда она не может быть по определению включена.
              0
              Ещё один способ — настраивать сервер один раз и качественно. Использовать puppet/chef`ы и уже ездить с ноды на ноду, не думая, что что-то забудется.

              Ручное конфигурирование — наследие средних веков.

              А аргумент типа «что-то забудете» это уже как-то странно — из-за того, что мы забывчивы в админстве и не умеем записывать мы тратим лишнее время на приведение системы в определённую идеологию на программерском уровне. Оно нам надо?
                0
                Не в качестве порицания, но меня безумно коробит калька «временная зона». Чем не угодил «часовой пояс»?
                  0
                  Ну, по закону с не очень давнего времени именно это является официальным названием в РФ.
                    0
                    Вообще-то «часовые зоны». Но ОК.
                      0
                      Да, спутал, сорри)
                  +1
                  Офтоп. Кто же покажет данную статью ребятам из майкрософт, когда же они скайп починят. Достали эти сообщения из прошлого…

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