Время от времени в IRC или в почтовых рассылках кто-нибудь задает вопросы, которые показывают глубокое непонимание (или недостаток понимания) меток времени, особенно тех, которые учитывают часовые пояса. Так как я уже сталкивался с этим ранее, позвольте мне рассказать, что такое timestamps, как с ними работать и с какими наиболее распространенными загвоздками вы можете столкнуться.
У нас есть два типа данных, которые мы можем использовать:
- timestamp
- timestamp с часовым поясом (или timestamptz)
Тип timestamp содержит только дату и время, никакой другой информации. С одной стороны, может показаться, что это неплохо (в конце концов, в повседневной жизни мы используем только дату и время, не так ли?), но с другой — это полнейший ужас.
Давайте представим, что у вас есть временная метка “2014-04-04 20:00:00". О чем она вам говорит? К сожалению, не о многом. Всё зависит от того, о какой точке планеты идет речь. Восемь вечера 4-го апреля – это разный момент времени в Лос Анджелесе, Чикаго, Лондоне, Варшаве или Москве. В этом проблема часовых поясов.
Конечно, вы можете подумать: «Я всегда буду в одном часовом поясе, мне не нужно заморочек с поддержкой разных временных зон. В моем часовом поясе даты и времени будет вполне достаточно, чтобы отметить какой-либо момент времени, ведь именно так мы делаем в «реальной жизни».
Но так ли это на самом деле?
Представим, что у вас есть метка ‘2013-10-27 02:00:00', и вы знаете, что ваше приложение привязано к польскому времени. В этом случае, вам уже не повезло, потому что это может быть 2 часа ночи по центрально-европейскому летнему времени (CEST) или на час больше, по обычному центрально-европейскому времени. Всё из-за сезонного перевода часов.
Я считаю, что использование временных меток без часового пояса почти всегда является багом, и его нужно исправлять. Проблем становится еще больше, если, записи в вашем приложении поступают из разных часовых поясов (например, приложение-планировщик).
Так что самое очевидное решение – использовать метки времени с часовыми поясами (timestamptz).
Во-первых, это не займет больше места на диске:
$ select typname, typlen from pg_type where typname ~ '^timestamp';
typname | typlen
-------------+--------
timestamp | 8
timestamptz | 8
(2 rows)
Как же это работает? Метка должна знать часовой пояс, так почему же для этого не требуется больше места?
Дело в том, что она не знает часовой пояс. Внутри, все значения в колонках timestamptz указаны в формате UTC (всемирное координированное время).
У UTC есть приятные особенности: у него нет смещения (он сам является отправной точкой, от которой считаются смещения других часовых поясов), и у него нет разницы между летним и зимним временем. Так что любая временная метка в формате UTC всегда гарантированно указывает только на одну точку во времени.
Но если всё время указывать по UTC, то как я узнаю время в нужном мне часовом поясе?
Каждый раз, когда речь идет о значениях timestamptz, если часовой пояс не указан, то PostgreSQL использует заранее сконфигурированное время. И вы можете конфигурировать его разными способами:
- параметр timezone в postgresql.conf
- alter database … set timezone = ‘…'
- alter user … set timezone = ‘…'
- SET timezone = ‘…'
Первый параметр применяется для того, чтобы указать, в каком часовом поясе находится ваш сервер. Другими словами, часовой пояс по-умолчанию, который будет использоваться при отсутствии других изменений.
Следующие два способа меняют значение по-умолчанию для выбранной базы данных и пользователя.
Последний способ можно использовать, если вы хотите, чтобы ваше соединение с базой данных работало с иными настройками.
Примите во внимание, как при этом меняется вывод now():
$ select now();
now
-------------------------------
2014-04-04 20:32:59.390583+02
(1 row)
$ set timezone = 'America/New_York';
SET
$ select now();
now
-------------------------------
2014-04-04 14:33:06.442768-04
(1 row)
Так что каждый раз, когда вы просматриваете или меняете значения timestamptz, PostgreSQL конвертирует их в/из UTC.
Это значит, что значения можно легко сравнивать (все они в одном часовом поясе, нет сдвигов на летнее или зимнее время, так что сравнение всегда возможно).
А теперь давайте представим, что у вас есть значение ‘2014-04-04 20:00:00'. И вы знаете, что это время в Лос Анджелесе, но вы хотите сохранить его в своей базе данных, которая функционирует в другом часовом поясе. Вы можете проверить, что текущее смещение составляет -7 часов, и использовать значение таким образом:
$ select '2014-04-04 20:00:00-07'::timestamptz;
timestamptz
------------------------
2014-04-05 05:00:00+02
Что произошло? Почему не показывается 8 вечера?
Причина проста – в запрос я вставил timestamp в каком-то часовом поясе. Внутри, метка была сконвертирована в UTC, а затем, снова сконвертирована (возможно, даже без UTC, я не уверен) в мой обычный часовой пояс, которым является:
$ show timezone;
TimeZone
----------
Poland
(1 row)
Если бы у меня был установлен часовой пояс Лос Анджелеса, то результат запроса был бы таким:
$ set timezone = 'America/Los_Angeles';
SET
$ select '2014-04-04 20:00:00-07'::timestamptz;
timestamptz
------------------------
2014-04-04 20:00:00-07
(1 row)
Важно понимать, что выводимое значение всегда принимает во внимание настройку часового пояса.
Есть еще один способ получить 20:00 в Лос Анджелесе:
$ set timezone = 'Poland';
SET
$ select '2014-04-04 20:00:00'::timestamp at time zone 'America/Los_Angeles';
timezone
------------------------
2014-04-05 05:00:00+02
(1 row)
Очень важно добавлять “::timestamp" после значения, иначе мы получим что-то странное:
$ set timezone = 'Poland';
SET
$ select '2014-04-04 20:00:00' at time zone 'America/Los_Angeles';
timezone
---------------------
2014-04-04 11:00:00
(1 row)
Что здесь произошло? Откуда взялось 11:00?
Значение в кавычках (2014-04-04 20:00:00) воспринимается как timestamptz, что значит 8 вечера в моём часовом поясе:
select '2014-04-04 20:00:00'::timestamptz;
timestamptz
------------------------
2014-04-04 20:00:00+02
(1 row)
И только после перевода значения в мой часовой пояс PG считывает “at time zone …", которая используется для отображения времени в выбранном часовом поясе.
Таким образом, timestamp at time zone выдаёт значение timestamptz, которое показывает момент, когда местное время в выбранном часовом поясе было таким, как указано в команде.
А timestamptz at time zone выдаёт значение timestamp, которое показывает, каким было время в выбранном часовом поясе в указанный момент времени.
Это звучит немного путанно, поэтому давайте я приведу примеры:
select '2014-04-04 20:00:00'::timestamptz at time zone 'UTC';
timezone
---------------------
2014-04-04 18:00:00
(1 row)
select '2014-04-04 20:00:00'::timestamp at time zone 'UTC';
timezone
------------------------
2014-04-04 22:00:00+02
(1 row)
Интересно то, что мы можем использовать это для перевода времени из одного часового пояса в другой, даже если Pg не находится ни в одном из них.
Допустим, мы хотим узнать, который час в Лос Анджелесе, когда в Москве — 8 утра. Моё местное время следующее:
$ show timezone;
TimeZone
----------
Poland
(1 row)
Пользы от него мало.
Для начала нам нужно определить точку во времени (в формате timestamptz), которая показывает 8 утра в Москве:
$ select '2014-04-04 08:00:00'::timestamp at time zone 'Europe/Moscow';
timezone
------------------------
2014-04-04 06:00:00+02
(1 row)
Это говорит мне о том, что она соответствует 6 утра в моём часовом поясе. Но мы хотим узнать время в Лос Анджелесе. Я мог бы написать ‘2014-04-04 06:00:00+02' в часовом поясе ‘LA', но можно сделать по-другому:
$ select ('2014-04-04 08:00:00'::timestamp at time zone 'Europe/Moscow') at time zone 'America/Los_Angeles';
timezone
---------------------
2014-04-03 21:00:00
(1 row)
Так как выражение ‘timestamp at time zone ..' – это то же самое, что timestamptz, мы можем использовать “at time zone" еще раз, чтобы перевести его обратно в метку времени (без указания часового пояса), относящуюся к какому-то другому месту.
Надеюсь, теперь вам всё ясно. Я сам довольно долго пытался разобраться в этом вопросе, и наконец-то всё понял :)
У всего этого есть один интересный побочный эффект: не так-то просто добавить индексы к функциям, работающим с timestamptz. Например, вы не можете создать индекс, который будет использоваться для получения дня недели:
$ create table test (i timestamptz);
CREATE TABLE
$ create index q on test (to_char(i, 'Day'));
ERROR: functions in index expression must be marked IMMUTABLE
Как показано в примере выше, причина очень проста – одна и та же точка во времени может относиться к разным дням недели в зависимости от часового пояса. А поскольку to_char() использует текущий часовой пояс, он может выдавать разные значения для одних и тех же исходных данных в зависимости от настроек часового пояса в системе:
$ set timezone = 'Europe/Warsaw';
SET
$ insert into test (i) values ('2014-04-04 06:00:00');
INSERT 0 1
$ select i, to_char(i, 'Day') from test;
i | to_char
------------------------+-----------
2014-04-04 06:00:00+02 | Friday
(1 row)
$ set timezone = 'Europe/Moscow';
SET
$ select i, to_char(i, 'Day') from test;
i | to_char
------------------------+-----------
2014-04-04 08:00:00+04 | Friday
(1 row)
$ set timezone = 'America/Los_Angeles';
SET
$ select i, to_char(i, 'Day') from test;
i | to_char
------------------------+-----------
2014-04-03 21:00:00-07 | Thursday
(1 row)
Одна и та же точка во времени, но разные дни. Это могут быть разные месяцы или даже разные года, в зависимости от того, где это было.
Временная метка (без часового пояса) здесь “проявляет” сильную сторону – так как в ней не указан часовой пояс, её можно спокойно использовать для извлечения информации.
Но мы же знаем, как переводить timestamptz в timestamp. Нужно просто указать ей часовой пояс. Поэтому мы можем попробовать сделать так:
create index q on test (to_char(i at time zone 'Poland', 'Day'));
Но, к сожалению, ничего не выходит. Дело в том, что to_char слишком разносторонний. Вы можете использовать to_char вот так:
$ select to_char(now(), 'TMMonth');
to_char
---------
April
(1 row)
$ set lc_time = 'pl_PL.UTF-8';
SET
$ select to_char(now(), 'TMMonth');
to_char
----------
Kwiecien
(1 row)
На этот раз мы получаем другие результаты не из-за часового пояса, а из-за локали.
Правильным решением проблемы индексирования будет написать свою собственную функцию, которая будет вызывать to_char в абсолютно постоянной «среде», а затем ее уже индексировать. Вот так:
create function day_from_ts(timestamptz) returns text as $$
select to_char( $1 at time zone 'Poland', 'Day' );
$$ language sql immutable;
CREATE FUNCTION
А теперь мы можем использовать ее для индексирования:
create index q on test (day_from_ts( i ));
CREATE INDEX
Это безопасно, потому что сама функция заставляет часовой пояс принимать значение «Poland», и она вызывает to_char таким образом, чтобы игнорировать значение локали (другими словами, в формате to_char нет префикса TM).
Естественно, чтобы воспользоваться этим индексом, все запросы должны также использовать эту функцию:
select * from test where day_from_ts(i) = 'Friday';
Еще одна важная вещь при работе с часовыми поясами – получение времени Unix, или так называемой эпохи. В целом, это просто:
$ select extract(epoch from now());
date_part
------------------
1396638868.57491
(1 row)
Интересно то, что оно не зависит от часового пояса:
$ begin;
BEGIN
$ show timezone;
TimeZone
----------
Poland
(1 row)
$ select now(), extract(epoch from now());
now | date_part
-------------------------------+------------------
2014-04-04 21:15:27.834775+02 | 1396638927.83477
(1 row)
$ set timezone = 'America/Los_Angeles';
SET
$ select now(), extract(epoch from now());
now | date_part
-------------------------------+------------------
2014-04-04 12:15:27.834775-07 | 1396638927.83477
(1 row)
$ commit;
COMMIT
Причина, известная не всем, кроется в том, что время Unix всегда принимается в часовом поясе UTC. Это значит, что, когда вы извлекаете эпоху из временной метки timestamp, PG предполагает, что она находится в UTC. Из чего вытекают следующие потенциальные проблемы:
$ select now(), extract(epoch from now());
now | date_part
-------------------------------+-----------------
2014-04-04 21:19:01.456205+02 | 1396639141.4562
(1 row)
$ select extract(epoch from '2014-04-04 21:19:01.456205'::timestamp);
date_part
-----------------
1396646341.4562
(1 row)
В первом случае Pg получает «точку во времени», которая внутренне конвертируется в UTC (а когда отображается – преобразовывается в мой часовой пояс, +2).
Во втором случае временная метка находится в моём часовом поясе, но предполагается, что это UTC (без конвертации!), и эпоха берется от значения ‘2014-04-04 21:19:01.456205 UTC', а не ‘2014-04-04 21:19:01.456205+02'.
Мудрёно.
Короче говоря, старайтесь избегать timestamp и используйте timestamptz.
Последнее, о чём я хотел бы сказать – это не баг или потенциальная проблема, а скорее функциональность, о которой многие не знают.
Как вы видели, PostgreSQL использует timestamp (и timestamptz) с точностью до микросекунд. Многие люди настаивают на том, чтобы точность была только до секунды, хотя лично мне это не нравится.
И timestamp, и timestamptz (и другие виды данных, относящиеся ко времени) могут иметь дополнительную точность (“precision”).
Давайте я приведу простой пример:
$ select now(), now()::timestamptz(0), now()::timestamptz(1);
now | now | now
-------------------------------+------------------------+--------------------------
2014-04-04 21:23:42.322315+02 | 2014-04-04 21:23:42+02 | 2014-04-04 21:23:42.3+02
(1 row)
Конечно, вы можете использовать это и в таблицах:
$ create table test (i timestamptz(0));
CREATE TABLE
$ insert into test(i) values (now());
INSERT 0 1
$ select * from test;
i
------------------------
2014-04-04 21:24:16+02
(1 row)
Отлично! Вам не нужно менять “now()" или что-либо еще, просто добавьте точность к типу данных, и она всё скорректирует.
Я упомянул, что мне это не нравится. Причина проста – в любой достаточно нагруженной системе секунда – слишком низкий уровень точности. Тем более, что хранение данных с точностью до микросекунды ничего мне не стоит, но может быть полезным. С другой стороны, если данные до микросекунд, то как мне сделать, чтобы значения отображались без долей секунды?
Все просто: я использую (в запросах SELECT) фунуции to_char(), или date_trunc, или даже приведение к типу timestamptz(0):
$ select now(),
to_char(now(), 'YYYY-MM-DD HH24:MI:SS TZ'),
date_trunc('second', now()),
now()::timestamptz(0);
now | to_char | date_trunc | now
-------------------------------+--------------------------+------------------------+------------------------
2014-04-04 21:28:20.827763+02 | 2014-04-04 21:28:20 CEST | 2014-04-04 21:28:20+02 | 2014-04-04 21:28:21+02
(1 row)
Более подробно о том, как работать с timestamps, мы собираемся рассказать на конференции PG Day'16 Russia в июле 2016 года! Готовьте свои вопросы, мы постараемся на них ответить.
Вы, наверняка, также найдете для себя полезной статью, написанную коллегами из Mail.ru Group. В ней наглядно изображен пример, когда одного лишь timestamptz недостаточно для решения поставленной задачи.
Комментарии (17)
Pilat
17.12.2015 18:51>Возможно, коллеги предложат сценарии, когда использование UTC в качестве основной настройки сервера СУБД нежелательно.
Я сходу не вспомню адрес, но тут же на Хабре была большая статья, затрагивающая эту проблему.
Суть в том, что для того, чтобы получить время в какой-то локальной временной зоне, надо иметь базу данных по смещению времени в прошлом и в будущем (!) на весь диапазон рассматриваемых времён. Как в UTC записать дату «через 6 месяцев в 10 часов МСК», если неизвестно, какое будет тогда смещение? Поэтому, в принципе, настройка самого сервера к этой проблеме отношения не имеет, это полезно только в смысле анализа логов. А время хранить в полях с временной зоной timestamp with time zone.
>И timestamp, и timestamptz (и другие виды данных, относящиеся ко времени) могут иметь дополнительную точность (“precision”).
Я нарвался на таких незнаек однажды, производители софта для рисования структуры базы, так что надо действительно обращать внимание.Core2Duo
18.12.2015 00:04+1Я сходу не вспомню адрес, но тут же на Хабре была большая статья, затрагивающая эту проблему.
Про эту говорите? habrahabr.ru/company/mailru/blog/242645
rdruzyagin
18.12.2015 04:38> Суть в том, что для того, чтобы получить время в какой-то локальной временной зоне, надо иметь базу данных по смещению времени в прошлом и в будущем (!) на весь диапазон рассматриваемых времён. Как в UTC записать дату «через 6 месяцев в 10 часов МСК», если неизвестно, какое будет тогда смещение?
Иметь базу на будущее — в принципе невозможно. Мы же не можем предсказать, решит наше правительство осуществлять переход на зимнее/летнее время или нет. Нужный брать требуемый timestamptz at time zone и записывать, в приложении отображать at time zone «Europe/Moscow» и своевременно обновлять базы часовых поясов при внесении в них изменений.Pilat
18.12.2015 10:07Вот я и пишу — сохранять время в timestampz, как Вы рекомендуете, надо с большой осторожностью.
>Короче говоря, старайтесь избегать timestamp и используйте timestamptz.
Короче говоря, старайтесь избегать timestamp и timestamptz и используйте timestamp with time zone.
Крайний случай — когда ещё надо сохранять дополнительную информацию, а именно смещение так, как его считает правильнымм источник данных. Например, ДНР/ЛНР как часть украины имели одну временную зону, а как отдельное от Украины образование — другую. Основной принцип — не должна происходить потеря исходных данных.
Envek
17.12.2015 20:50Всё ж таки не хватает типа, в котором будет храниться кроме таймстампа ещё и часовая зона: и идентификатор и смещение. Для хранения времён в будущем это важно, чтобы ловить непредсказуемые изменения часовых зон.
rdruzyagin
18.12.2015 04:42Это палка о двух концах. Возможно, я не понимаю сценарий, когда необходимо хранить время в заведомо неправильной, относительно текущей действительности, таймзоне. Но в такой ситуации, если вы запишите уйму меток в формате «timestamp +03», например (для Московского времени), потом депутаты решат сделать перевод таймзоны, и у вас будет большая проблема с кучей таймстампов, не соответствующих действительности. Дело дойдет либо до усложнения бизнес-логики (вручную в приложении разруливать такие ситуации), либо до внесения изменений в БД для корректировки часового пояса. Я такие приключения наблюдал в крупном продакшене, это очень печально.
andrewsch
18.12.2015 08:22Я так понимаю, товарищ говорил о том, чтобы вместе с временем сохранять ID часового пояса (типа 1='America/Los_Angeles')
Тогда время может быть корректно рассчитано даже при изменении зимнего/летнего времени или часовых поясов.Ivan22
18.12.2015 10:23И все равно проблема есть. Если у нас сохранено. «2010-01-01 15.00.00» utc и отдельно сохранено таймзона = 'Moskow' Что это нам дает?? Проблемы, учитывая, что в то время смещение Москвы от utc было не такое как сейчас!!!
andrewsch
18.12.2015 10:43Почему не такое?
Расчет смещения по конкретному часовому поясу для исторических данных будет всегда возвращать одинаковое локальное время для исторических дат, не взирая на решения текущего правительства по смене зимнего/летнего времени или даже смене часового пояса для конкретной местности — все изменения будут касаться только будущих дат.
Как правильно заметили выше, будущие даты возможно придется корректировать, особенно если в данной местности сменили сам часовой пояс. Но не прошлые даты (за крайне редким исключением баг фиксов в исторических часовых поясах — я видел пару таких случаев, вроде-бы).
Pilat
18.12.2015 11:43У меня есть случай, когда время указывается с временной зоной «местное время». Вот где настоящий ад. Поэтому с указанием времени приходится хранить информацию об источнике времени.
rdruzyagin
18.12.2015 12:17Ну да, это как раз та самая настройка таймзоны у пользователя. Ее надо запрашивать явно или определять по местоположению.
Envek
18.12.2015 19:54+1Сценарий таков: сохраняем тройку значений: корректное время в будущем в UTC, оно же локальное (или смещение, не суть), идентификатор часовой зоны. Потом наша госдума доблестно переводит всю нашу страну (или часть) на час или два в какую-то сторону и перекраивает часть часовых поясов. Задача: после обновления tzdata найти те времена, которые «поехали» и исправить. Для каких-то таймстампов надо перемотать локальное время, для каких-то — время в UTC (если нам важно сохранить именно значение локального времени). Собственно, задача уже рассматривалась в этой статье, которую уже процитировали и тут и на фейсбуке: habrahabr.ru/company/mailru/blog/242645 и решение про тройку значений взято оттуда.
rdruzyagin
21.12.2015 11:07Да, вы правы, это хороший пример. Пожалуй, добавлю ссылку на эту статью в тело сообщения, для дополнительного изучения читателями.
Sozidatel
22.12.2015 10:47Мы храним все даты в UTC, конвертируем вводные и выходные данные. Но есть вариант, когда надо работать с временными зонами на уровне самой базы — отчеты. Например график количества заказов по дням недели — границы дней недели зависит от временной зоны клиента.
Пока мы не кешируем статистику, но когда будем это делать, к ключу кеша надо будет добавлять временную зону клиента.
andrewsch
Если я правильно понимаю, осовная фича timestamptz заключается в том, что значение автоматически сохраняется в UTC в соответствии с текущим часовым поясом, и при запросах соответственно преобразуется обратно?
Предположим, мы говорим не о базе, которая работает в рамках одного единственного часового пояса, а паралельно с пользователями из разных часовых поясов.
Не лучше-ли сохранять в базе UTC и делать преобразования ближе к front-end-у?
Мне кажется, тогда можно кешировать больше данных, так как на уровне базы данных и бизнес-логики можно работать в UTC, с однородными данными.
rdruzyagin
Да, в целом правильно, при условии что вы, принимая пользовательский input, явно указываете, в каком часовом поясе приняли время пользователя, опираясь на его настройки или определяя местоположение по IP, например. Иначе входное время будет некорректно интерпретироваться при конвертации.
Лично я склонен согласиться, что в такой ситуации UTC наиболее эффективен и устраняет много ненужных проблем. Но требуется аккуратность с вводом/выводом значений времени.
Более «ленивый» путь — это четко декларировать что ваш сервис / платформа работают по такому-то времени (будь-то Москва, UTC или что-то иное), и что все операции ввода/вывода времени происходят в этой таймзоне. Это складывает ответственность по учету времени на пользователя, но облегчает разработку и эксплуатацию системы.
Я встречал на практике разные примеры, вопрос в том, что бизнес-задача позволяет. Какой-нибудь серьезный финансовый инструмент практически наверняка должен уметь адекватно работать в условиях пользователей из широкого спектра часовых поясов.
Возможно, коллеги предложат сценарии, когда использование UTC в качестве основной настройки сервера СУБД нежелательно.