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

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

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

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

For future dates, I would store it with a specific timezone, to avoid changes such as leap seconds or timezone rule changes.

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

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

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

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

Think [of it] like a calendar event. UTC doesn’t make sense for that

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

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

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

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 утра в Чикаго — это 8 утра в Лос-Анджелесе (с использованием текущих определений часового пояса):

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 |
+---------------------+