PostgreSQL/JDBC и TIMESTAMP против TIMESTAMPTZ

В последнее время я испытываю большую боль, связанную с Timestamps, с JPA. Я обнаружил, что многие мои проблемы были устранены с помощью TIMESTAMPTZ для моих полей вместо TIMESTAMP. Мой сервер находится в UTC, в то время как моя JVM находится в PST. Кажется почти невозможным, чтобы JPA нормализовалась по значениям UTC в базе данных при использовании TIMESTAMP WITHOUT TIMEZONE.

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

Итак, мой вопрос: для сервера Java/JPA/PostgreSQL, когда я хочу использовать TIMESTAMP над TIMESTAMPTZ? Каковы варианты использования? Прямо сейчас я с трудом вижу, почему я когда-либо хотел использовать TIMESTAMP, и из-за этого я обеспокоен тем, что не понимаю его ценности.

Ответ 1

Вы можете использовать его для представления того, что Joda-Time и новые API-интерфейсы Java 8 называют LocalDateTime. A LocalDateTime не представляет точную точку на временной шкале. Это всего лишь набор полей, от года к наносекундам. Это "описание даты, используемой для дней рождения, в сочетании с местным временем, как показано на настенных часах".

Вы можете использовать его для представления, например, того факта, что ваша точная дата рождения - 1975-07-19 в 18:00. Или что во всем мире следующий новый год отмечается в 2015-01-01 в 00:00.

Чтобы представить точные моменты, как момент, когда Армстронг шел по луне, метка времени с часовым поясом действительно более уместна. Независимо от часового пояса JVM и часового пояса базы данных, он должен вернуть вам правильный момент.

Ответ 2

Обычно используйте TIMESTAMPTZ

Вот совет Дэвида Уилера, эксперта Postgres, в блоге, название которого говорит само за себя:
Всегда используйте TIMESTAMP с зоной времени (TIMESTAMPTZ)

Если вы отслеживаете фактические моменты, определенные точки на временной шкале, используйте TIMESTAMP WITH TIME ZONE.

Одно исключение: разбиение

Единственное исключение Уилерса - это разбиение на временные метки из-за технических ограничений. Редкое исключение для большинства из нас.

Информацию о разделении смотрите в doc и вики.

неправильное название

timestamp with time zone имен типов timestamp with time zone и timestamp without time zone являются неправильными. В обоих случаях значение даты и времени сохраняется в UTC (без смещения часового пояса). Прочтите это предыдущее предложение еще раз. UTC, всегда. Фраза "с часовым поясом" означает "с уделением внимания часовому поясу", а не "сохранять часовой пояс вместе с этим значением". Разница между типами заключается в том, следует ли применять какой-либо часовой пояс либо во время хранения (INSERT или UPDATE), либо при извлечении (запрос SELECT). (Это поведение описано для Postgres - другие базы данных в этом отношении сильно различаются.)

Точнее, следует сказать, что TIMESTAMP без TIME ZONE хранит значения даты и времени без часового пояса. Но без каких-либо временных рамок любой, кто просматривает эти данные, должен будет предположить (надеюсь, молиться?), Что значения указаны в формате UTC. Но, опять же, имейте в виду, что вы никогда не должны использовать этот тип.

Внимательно прочитайте документ и немного поэкспериментируйте, чтобы прояснить ваше понимание.

незональных

Если вы хотите сохранить общее представление о возможном времени, а не конкретном моменте, используйте другой тип, TIMESTAMP WITHOUT TIME ZONE.

Например, Рождество начинается в этом году в первый момент 25 декабря 2017 года. Это будет 2017-12-25T 00:00:00 без 2017-12-25T 00:00:00 часового пояса или смещения от UTC. Эта ценность - только смутное представление о возможных моментах. Это не имеет смысла, пока мы не применим часовой пояс (или смещение). Поэтому мы храним это, используя TIMESTAMP WITHOUT TIME ZONE.

Эльфы, работающие в отделе логистики специальных мероприятий Santas, используют часовые пояса как часть процесса планирования. Самым ранним часовым поясом в настоящее время является Pacific/Kiribati, на 14 часов больше UTC. Эльфы расписывают Сантуса первым прибытием туда. Эльфы планируют полетный план, по которому северный олень попадает в другие часовые пояса, где вскоре наступает полночь, например, в Pacific/Auckland. Они продолжают идти на запад, когда в каждую зону прибывает полночь. Несколько часов спустя в Asia/Kolkata, еще позже в Europe/Paris, еще больше часов спустя в America/Montreal и так далее.

Каждый из этих особых моментов доставки будет записываться эльфами с использованием WITH TIME ZONE, в то время как эта общая идея Рождества будет храниться как WITHOUT TIME ZONE.

Другое использование в бизнес-приложениях для WITHOUT TIME ZONE - это планирование встреч на срок более нескольких недель. Политики всего мира имеют необъяснимое пристрастие к тому, чтобы возиться с часами и пересматривать правила часовых поясов. Они присоединяются к переходу на летнее время (DST), покидают DST, запускают DST в другую дату, заканчивают летнее время в другую дату или переводят часы на 15 минут или полчаса. Все это было сделано в последние несколько лет Турцией, США, Россией, Венесуэлой и другими.

Политики часто вносят эти изменения с небольшим предупреждением. Поэтому, если вы планируете посещение стоматолога на шесть месяцев в 13:00, это, вероятно, следует сохранить как TIMESTAMP WITHOUT TIME ZONE иначе политики могут фактически изменить ваше назначение на полдень, или на 2 часа дня, или на 13:30.

Ответ 3

Обновление ответов выше: разбиение больше не является исключительным случаем в PG11.

https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITION-PRUNING

Лично успешно протестировал запросы к PG11 AWS RDS. Также официальная PG-вики утверждает, что использование timestamp without timezone - плохая идея:

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29_to_store_UTC_times