Как работать с метками времени (timestamp) в PostgreSQL?

Тема работы с временными метками в 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 года! Готовьте свои вопросы, мы постараемся на них ответить.

ссылка на оригинал статьи http://habrahabr.ru/post/273177/