Представление будущего времени в PostgreSQL

Question

Я был условно хранить прошлые даты в формате UTC в базе данных, поскольку именно тогда произошло событие. Для будущих дат я бы сохранял его с определенным часовым поясом, чтобы избежать таких изменений, как високосные секунды или изменения правил часового пояса.

Постгрес имеет timestamp with timezone, но под прикрытием он сохраняет его как UTC, делая вывод, что указанный часовой пояс является смещением UTC. Если правила часового пояса будут изменены, это не будет отражено в столбце.

Что рекомендуется в этом случае?

Answer 1

Думайте об этом как о событии календаря. UTC для этого не имеет смысла

Похоже, вы хотите сохранить местное время относительно определенного часового пояса. В этом случае храните timestamp (без часового пояса) и timezone в отдельной колонке.

Например, предположим, что вы хотите записать событие, которое произойдет в 10 часов утра 26 февраля 2030 года в Чикаго, и это должно быть в 10 часов утра по местному времени, независимо от правила часового пояса, действующего на эту дату.

Если база данных хранит метку времени без часового пояса:

unutbu=# select '2030-02-26 10:00:00'::timestamp as localtime, 'America/Chicago' AS tzone;
+---------------------+-----------------+
|      localtime      |      tzone      |
+---------------------+-----------------+
| 2030-02-26 10:00:00 | America/Chicago |
+---------------------+-----------------+

Затем, позже, вы можете найти дату и время в формате UTC, используя

unutbu=# select '2030-02-26 10:00:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2030-02-26 16:00:00 |
+---------------------+

Запрос возвращает дату и время UTC, 2030-02-26 16:00:00что соответствует 2030-02-26 10:00:00 по местному времени в Чикаго.

С помощью AT TIME ZONE задерживает применение правил часового пояса, когда запрос сделан вместо того, когда timestamptz был вставлен.

---

С помощью AT TIME ZONE на timestamp локализует дату и время в заданном часовом поясе, но сообщает дату и время в часовом поясе пользователя. С помощью AT TIME ZONE на timestamptz преобразует дату и время в заданный часовой пояс, затем сбрасывает смещение, возвращая timestamp, Выше, AT TIME ZONE используется дважды: сначала для локализации timestamp и затем преобразовать возвращенный timestamptz в новый часовой пояс (UTC). Результатом является timestamp в UTC.

Вот пример, демонстрирующий AT TIME ZONEповедение на timestamps:

unutbu=# SET timezone = 'America/Chicago';
unutbu=# SELECT '2030-02-26 10:00:00'::timestamp AT TIME ZONE 'America/Chicago';
+------------------------+
|        timezone        |
+------------------------+
| 2030-02-26 10:00:00-06 |
+------------------------+

unutbu=# SET timezone = 'America/Los_Angeles';
unutbu=# SELECT '2030-02-26 10:00:00'::timestamp AT TIME ZONE 'America/Chicago';
+------------------------+
|        timezone        |
+------------------------+
| 2030-02-26 08:00:00-08 |
+------------------------+

2030-02-26 10:00:00-06 а также 2030-02-26 08:00:00-08 являются одинаковыми датами, но сообщаются в разных часовых поясах пользователя. Это показывает, что 10:00 в Чикаго - 8:00 в Лос-Анджелесе (используя текущие определения часового пояса):

unutbu=# SELECT '2030-02-26 10:00:00-06'::timestamptz AT TIME ZONE 'America/Los_Angeles';
+---------------------+
|      timezone       |
+---------------------+
| 2030-02-26 08:00:00 |
+---------------------+

---

Альтернатива использованию AT TIME ZONE дважды, чтобы установить часовой пояс пользователя в UTC, Тогда вы могли бы использовать

select localtime AT TIME ZONE tzone

Обратите внимание, что когда это сделано, timestamptz возвращается вместо timestamp,

---

Помните, что хранение локальных времен может быть проблематичным, поскольку могут существовать несуществующие и неоднозначные времена. Например, 2018-03-11 02:30:00 это несуществующее местное время в America/Chicago, Postgresql нормализует несуществующие локальные времена, предполагая, что оно относится к соответствующему времени после начала перехода на летнее время (DST) (как будто кто-то забыл установить свои часы вперед):

unutbu=# select '2018-03-11 02:30:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-03-11 08:30:00 |
+---------------------+
(1 row)

unutbu=# select '2018-03-11 03:30:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-03-11 08:30:00 |
+---------------------+
(1 row)

Примером неоднозначного местного времени является 2018-11-04 01:00:00 в America/Chicago, Это происходит дважды из-за летнего времени. Postgresql разрешает эту неоднозначность, выбирая более позднее время после окончания летнего времени:

unutbu=# select '2018-11-04 01:00:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-11-04 07:00:00 |
+---------------------+

Обратите внимание, что это означает, что нет никакого способа обратиться к 2018-11-04 06:00:00 UTC храня местное время в America/Chicago часовой пояс:

unutbu=# select '2018-11-04 00:59:59'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-11-04 05:59:59 |
+---------------------+

Answer 2

Для будущих дат я бы сохранял его с определенным часовым поясом, чтобы избежать таких изменений, как високосные секунды или изменения правил часового пояса.

Это кажется отсталым. Основное преимущество UTC над другими часовыми поясами заключается в том, что он менее подвержен неожиданным будущим изменениям: UTC вводит дополнительные секунды только в известные, ограниченные точки календарного года; и не имеет ни одного из частых политически обусловленных изменений компенсации.

Хранение значений в некотором локально управляемом часовом поясе делает эти значения более склонными (по сравнению с UTC) к произвольным, непредсказуемым будущим изменениям значения.

Итак, общая рекомендация: хранить все значения времени (будь то дата или дата + время) как UTC в базе данных, обрабатывать их внутренне как значения UTC; и преобразовывать в / из локального часового пояса только на внешних интерфейсах.

Для PostgreSQL это означает, что предпочитают TIMESTAMP WITH TIME ZONE,

Answer 3

Особенно, если вы хотите защитить себя от будущих изменений времени, вы должны использовать timestamp with time zone,

PostgreSQL внутренне хранит количество микросекунд с 2000-01-01 00:00:00, что безопасно от изменений часовых поясов. Если вы продолжаете обновлять свой PostgreSQL, он всегда будет правильно отображать это абсолютное значение для вашего часового пояса сеанса.

В PostgreSQL нет положения о дополнительных секундах.