- PVSM.RU - https://www.pvsm.ru -
В связи с тем, что накопилось несколько вопросов и решений по работе со временем, решил сделать небольшой обзор.
В mysql существует несколько стандартных типов данных для представления времени, мы рассмотрим TIMESTAMP
и DATETIME
.
В документации [1] говорится, что к некоторым типам данных применяется политика конвертирования, а к некоторым — нет.
На практике все намного интереснее. Рассмотрим несколько примеров:
Создадим таблицу:
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
[2] рассматривает значение аргумента в текущей зоне и конвертирует его в 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 |
+---------------------+---------------------+
Тут нет ничего удивительного, согласно документации [3], 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
.
Рассмотрим ситуацию с sqlite3. Согласно документации [6] в sqlite нет типа данных для сохранения времени, но есть функции [7] для работы со временем, сохраненным в виде текста, числа с плавающей точкой и в виде целого числа. В целом эти представления принципиально ничем не отличается. Можно считать, что в 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 и не конвертируете его, то пользователи запутаются во времени. Например, если два пользователя живут в разных временных зонах, то, видя одну и ту же строку времени без указания зоны, они будут думать о разных временах. Чтобы не повторяться, вот ссылка [4] с примером.
Во-первых, очень полезно ознакомиться с описанием работы со временем в glibc [8]. Мы же рассмотрим несколько примеров, касающихся результатов работы нескольких функций в разных временных зонах. Оказывается, даже в документации говорится, что 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, так, если он сменит свою зону, все даты останутся правильными.
Рассмотрим только deb-based дистрибутивы и пару железных методов по настройке временн`ой зоны.
sudo ln -sf /usr/share/zoneinfo/UTC /etc/localtime
и на всякий случай отредактировать /etc/timezone
(если он есть)
export TZ=Europe/London
Вообще это отдельная тема, поэтому в этой статье нет примеров, касающихся leap second. Вы можете сами проверить как работают те или иные функции, а так же как ведут себя различные базы данных, вот примеры mysql [9].
Автор: Talyutin
Источник [13]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/mysql/22446
Ссылки в тексте:
[1] документации: http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html
[2] UNIX_TIMESTAMP
: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
[3] документации: http://dev.mysql.com/doc/refman/5.6/en/datetime.html
[4] статьи: http://habrahabr.ru/post/69983/
[5] feedbee: http://habrahabr.ru/users/feedbee/
[6] документации: http://www.sqlite.org/datatype3.html#datetime
[7] функции: http://www.sqlite.org/lang_datefunc.html
[8] glibc: http://www.delorie.com/gnu/docs/glibc/libc_toc.html#TOC429
[9] вот примеры mysql: http://dev.mysql.com/doc/refman/5.6/en/time-zone-leap-seconds.html
[10] leap second: http://en.wikipedia.org/wiki/Leap_seconds
[11] замечание: http://www.delorie.com/gnu/docs/glibc/libc_433.html
[12] http://www.onlineconversion.com/unix_time.htm: http://www.onlineconversion.com/unix_time.htm
[13] Источник: http://habrahabr.ru/post/162341/
Нажмите здесь для печати.