http://www.depesz.com/2014/04/04/how-to-deal-with-timestamps/

Перевод

Тема работы с временными метками в PostgreSQL плохо раскрыта в русскоязычных профильных публикациях в Интернете и служит частым источником проблем в работе программистов. Предлагаю вашему вниманию перевод материала от Hubert Lubaczewski, автора популярного зарубежного блога depesz.com. Надеюсь, статья будет для вас полезна!

Время от времени в 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

----------

Kwiecień

(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 недостаточно для решения поставленной задачи.