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

в 11:00, , рубрики: datetime, mysql, timezone, метки: , , ,

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

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

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.

Автор: Talyutin

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js