Ответ 1
timestamp типа данных - это краткое имя timestamp without time zone.
Другой параметр timestamptz является коротким для timestamp with time zone.
timestamptz является предпочтительным типом в семье даты/времени, буквально. Он имеет typispreferred набор в pg_type, который может иметь значение:
эпоха
Внутри временные метки сохраняются как счетчик из эпохи. Postgres использует эпоху первого момента первого дня 2000 года в UTC, то есть 2000-01-01T00: 00: 00Z. Восемь октетов используются для хранения номера счета. В зависимости от параметра времени компиляции это число:
- 8-байтовое целое число (по умолчанию), от 0 до 6 цифр дробной секунды
-
Число с плавающей запятой (устаревшее), от 0 до 10 цифр дробной секунды, где точность быстро ухудшается для значений, удаленных от эпохи.
В современных установках Postgres используется 8-байтовое целое число.
Обратите внимание, что Postgres не использует время Unix. Postgres epoch - первый момент 2000-01-01, а не Unix 1970-01-01. Хотя время Unix имеет разрешение целых секунд, Postgres сохраняет доли секунд.
timestamp
Если вы определяете [without time zone] timestamp типа данных [without time zone] вы сообщаете Postgres: "Я не предоставляю часового пояса явно, вместо этого принимаю текущий часовой пояс. Postgres сохраняет временную метку как есть - игнорируя модификатор часового пояса, если вы должны добавить один!
Когда вы позже покажете эту timestamp, вы вернетесь к тому, что вы ввели буквально. При одинаковом настройке часового пояса все в порядке. Если настройка часового пояса для сеанса изменяется, значит, значение timestamp - значение остается неизменным.
timestamptz
Обработка timestamp with time zone несколько отличается. Я цитирую здесь инструкцию:
Для
timestamp with time zoneвнутренне сохраненное значение всегда находится в UTC (Universal Coordinated Time...)
Смелый акцент мой. Сам часовой пояс никогда не сохраняется. Это модификатор ввода, используемый для вычисления соответствующей временной метки UTC, которая хранится - и/или модификатор вывода, используемый для вычисления локального времени для отображения - с добавленным смещением часового пояса. Если вы не добавляете смещение для timestamptz на входе, предполагается установка текущего часового пояса сессии. Все вычисления выполняются с отметками времени UTC. Если вам приходится (или, возможно, придется) иметь дело с более чем одним часовым поясом, используйте timestamptz.
Клиенты, такие как psql или pgAdmin или любое приложение, сообщающее через libpq (например, Ruby с pg gem), имеют отметку времени плюс смещение для текущего часового пояса или в соответствии с запрошенным часовым поясом (см. Ниже). Это всегда один и тот же момент времени, меняется только формат отображения. Или, как говорится в руководстве:
Все даты и время, относящиеся к времени, хранятся внутри UTC. Они преобразуются в локальное время в зоне, заданной параметром конфигурации TimeZone, перед тем, как показывать клиенту.
Рассмотрим этот простой пример (в psql):
db=# SELECT timestamptz '2012-03-05 20:00+03';
timestamptz
------------------------
2012-03-05 18:00:00+01
Смелый акцент мой. Что здесь случилось?
Я выбрал произвольное смещение часового пояса +3 для входного литерала. Для Postgres это всего лишь один из многих способов ввода временной метки UTC. 2012-03-05 17:00:00. Результат запроса отображается для текущего часового пояса в Вене/Австрии в моем тесте, который имеет смещение +1 течение зимы и +2 в летнее время: 2012-03-05 18:00:00+01, потому что он попадает в зимнее время.
Postgres уже забыл, как это значение было введено. Все, что он помнит, - это значение и тип данных. Точно так же, как с десятичным числом. numeric '003.4', numeric '3.40' или numeric '+3.4' - все результаты имеют то же самое внутреннее значение.
AT TIME ZONE
Как только вы поймете эту логику, вы можете делать все, что хотите. Все, что отсутствует сейчас, является инструментом для интерпретации или представления литератур timestamp в соответствии с определенным часовым поясом. То, в которое входит конструкция AT TIME ZONE. Существуют два разных варианта использования. timestamptz преобразуется в timestamp и наоборот.
Чтобы ввести UTC timestamptz 2012-03-05 17:00:00+0:
SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC'
... что эквивалентно:
SELECT timestamptz '2012-03-05 17:00:00 UTC'
Чтобы отобразить тот же момент времени, что и timestamp EST (Восточное стандартное время):
SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'
Это правильно, AT TIME ZONE 'UTC' дважды. Первая интерпретирует значение timestamp как (данное) UTC timestamp, возвращающее тип timestamptz. Второй преобразует timestamptz в timestamp в данном часовом поясе "EST" - то, что часы в часовом поясе EST отображаются в этот уникальный момент времени.
Примеры
SELECT ts AT TIME ZONE 'UTC'
FROM (
VALUES
(1, timestamptz '2012-03-05 17:00:00+0')
, (2, timestamptz '2012-03-05 18:00:00+1')
, (3, timestamptz '2012-03-05 17:00:00 UTC')
, (4, timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6')
, (5, timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC')
, (6, timestamp '2012-03-05 07:00:00' AT TIME ZONE 'US/Hawaii') -- ①
, (7, timestamptz '2012-03-05 07:00:00 US/Hawaii') -- ①
, (8, timestamp '2012-03-05 07:00:00' AT TIME ZONE 'HST') -- ①
, (9, timestamp '2012-03-05 18:00:00+1') -- ② loaded footgun!
) t(id, ts);
Возвращает 8 (или 9) одинаковых строк с столбцами timestamptz с одной и той же меткой времени UTC. 2012-03-05 17:00:00. 9-й ряд вроде бы работает в моем часовом поясе, но является злой ловушкой. Смотри ниже.
① Строки 6-8 с указанием названия часового пояса и сокращением времени часового пояса для времени Гавайев подлежат летнему времени (DST) и могут отличаться, хотя в настоящее время они не установлены. Название часового пояса, такое как 'US/Hawaii', знает правила DST и все исторические смены автоматически, а аббревиатура типа HST - это просто тупой код для фиксированного смещения. Возможно, вам придется добавить другую аббревиатуру для летнего/стандартного времени. Имя правильно интерпретирует любую метку времени в данном часовом поясе. Аббревиатура дешевая, но она должна быть правильной для данной отметки времени:
Переход на летнее время не входит в число самых ярких идей, с которыми когда-либо приходило человечество.
② Ряд 9, отмеченный как загруженный ногой, работает для меня, но только по совпадению. Если вы явно передали литеральное значение в timestamp [without time zone], любое смещение часового пояса игнорируется ! Используется только голая временная метка. Затем значение автоматически привязывается к timestamptz в примере, соответствующем типу столбца. Для этого шага предполагается установка timezone текущего сеанса, который в моем случае является одним и тем же часовым поясом +1 (Европа/Вена). Но, вероятно, не в вашем случае - что приведет к другому значению. Короче говоря: не бросайте timestamptz литералы в timestamp или вы теряете смещение часового пояса.
Ваши вопросы
Пользователь хранит время, скажем 17 марта 2012 года, 19:00. Я не хочу, чтобы изменения в часовом поясе или часовой пояс сохранялись.
Сам часовой пояс никогда не сохраняется. Используйте один из методов выше, чтобы ввести временную метку UTC.
Я использую только указанный пользователем часовой пояс, чтобы получать записи "до" или "после" текущего времени в локальном часовом поясе пользователей.
Вы можете использовать один запрос для всех клиентов в разных часовых поясах.
Для абсолютного глобального времени:
SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time
Для времени в соответствии с местными часами:
SELECT * FROM tbl WHERE time_col > now()::time
Еще не устали от справочной информации? В руководстве больше.