Отличие PostgreSQL 9.3.5 от 8.3.6 при извлечении эпохи из приведенного значения

У нас есть таблица, которая содержит отметку времени в виде строки, и мы извлекаем ее эпоху на сервере PostgreSQL 8.3.6 с помощью extract:

select '2015/01/07 14:00:00' as the_timestamp, 
extract(epoch from cast('2015/01/07 14:00:00' as timestamp)) as the_epoch;

    the_timestamp    | the_epoch
---------------------+------------
 2015/01/07 14:00:00 | 1420668000
(1 row)

Мы, наконец, обновляемся, и у нас есть сервер с PostgreSQL 9.3.5, и теперь мы получаем другой результат:

select '2015/01/07 14:00:00' as the_timestamp, 
extract(epoch from cast('2015/01/07 14:00:00' as timestamp)) as the_epoch;

    the_timestamp    | the_epoch
---------------------+------------
 2015/01/07 14:00:00 | 1420639200         <<=== this is 8 hours earlier
(1 row)

Оба этих примера используют psql в качестве клиента, оба используют один и тот же часовой пояс:

show timezone;

      TimeZone
---------------------
 America/Los_Angeles
(1 row)

В документации 9.3 PostgreSQL я нашел это:

ПРИМЕЧАНИЕ. Стандарт SQL требует, чтобы просто запись timestamp была эквивалентна timestamp без часового пояса, и PostgreSQL учитывает такое поведение. (В версиях до 7.3 оно рассматривалось как отметка времени с часовым поясом.) timestamptz принимается как сокращение от отметка времени с часовым поясом; это расширение PostgreSQL.

Я обнаружил, что если я изменю запрос на использование timestamptz на сервере 9.3, он даст тот же результат, что и 8.3:

select '2015/01/07 14:00:00' as the_timestamp, 
extract(epoch from cast('2015/01/07 14:00:00' as timestamp)) as the_epoch;

    the_timestamp    | the_epoch
---------------------+------------
 2015/01/07 14:00:00 | 1420668000
(1 row)

Обратите внимание, что и timestamp, и timestamptz дают одинаковые результаты на 8.3:

select extract(epoch from cast('2015/01/07 14:00:00' as timestamptz));
 date_part
------------
 1420668000
(1 row)

select extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
 date_part
------------
 1420668000
(1 row)

Кажется, мы нашли ошибку в 9.3? Кажется, что при извлечении таким образом неправильно предполагается с часовым поясом, хотя этого не должно быть.


timestamp postgresql-9.3
person Alan    schedule 07.01.2015    source источник
comment
Вы имеете в виду ошибку в 8.3?   -  person Mike Sherrill 'Cat Recall'    schedule 08.01.2015
comment
Я думаю, что 9.3 неправильно обрабатывает извлечение временной метки. Кажется, он обрабатывает его как временную метку С часовым поясом вместо временной метки БЕЗ часового пояса, что, согласно документации, должно быть поведением.   -  person Alan    schedule 08.01.2015

Ответы (1)


arrow_upward
2
arrow_downward

TIMESTAMP WITH TIME ZONE означает не то, что вы думаете. К сожалению, это не означает «взять эту метку времени и сохранить ее вместе с соответствующим часовым поясом как два отдельных значения в поле». Вместо этого PostgreSQL обрабатывает это как «возьмите эту метку времени, которая, как вы должны предположить, соответствует местному времени, если только у нее нет спецификатора часового пояса, и преобразуйте ее в UTC, затем сохраните ее как UTC. Преобразуйте ее обратно в местное время для отображения».

Информация о часовом поясе фактически используется, а затем отбрасывается при импорте, что делает TIMESTAMP WITH TIME ZONE ужасным неправильным названием.

Проблема, с которой вы столкнулись, заключается в том, что эпоха для timestamp with time zone - это эпоха UTC, а не местное время. Поскольку предполагается, что временная метка соответствует местному времени, если вы не укажете спецификатор часового пояса, это означает, что TimeZone влияет на интерпретацию входных данных.

Подробности

Когда вы пишете:

cast('2015/01/07 14:00:00' as timestamp)

или эквивалент для литерала:

TIMESTAMP '2015/01/07 14:00:00'

вы говорите «отметка времени« 2015/01/07 14:00:00 »как точка времени настенных часов без определенного часового пояса». Локальный часовой пояс на это не влияет. Предполагается, что эпоха находится в том же часовом поясе, что и метка времени, какой бы она ни была. Вот почему настройка TimeZone не влияет на него:

regress=# SET TimeZone = 'Australia/Perth';
SET
regress=# SELECT extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
 date_part  
------------
 1420639200
(1 row)

regress=# SET TimeZone = UTC;
SET
regress=# SELECT extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
 date_part  
------------
 1420639200
(1 row)

Теперь, когда вы вместо этого используете timestamp with time zone, вы говорите, что отметка времени указана по местному времени, если не указано иное. Он будет импортирован и преобразован в формат UTC для внутреннего хранения. Затем оно преобразуется обратно в местное время, как определено TimeZone, для отображения/вывода.

Эпоха UTC, а не местное время.

Вот почему это происходит:

regress=# SET TimeZone = 'Australia/Perth';
SET
regress=# SELECT extract(epoch from cast('2015/01/07 14:00:00' as timestamp with time zone));
 date_part  
------------
 1420610400
(1 row)

regress=# SET TimeZone = UTC;
SET
regress=# SELECT extract(epoch from cast('2015/01/07 14:00:00' as timestamp with time zone));
 date_part  
------------
 1420639200
(1 row)

Причина, по которой результат extract отличается, заключается в том, что значение входной метки времени отличается. Это то же значение, но TimeZone учитывается при чтении и загрузке значения. Это имеет больше смысла, если вы посмотрите на это в таблице:

CREATE TABLE myts (ts timestamp without time zone, tstz timestamp with time zone);

SET TimeZone = UTC;
INSERT INTO myts(ts,tstz) VALUES ('2015/01/07 14:00:00','2015/01/07 14:00:00');
SET TimeZone = 'Australia/Perth';
INSERT INTO myts(ts,tstz) VALUES ('2015/01/07 14:00:00','2015/01/07 14:00:00');

теперь посмотрим на содержимое:

regress=# Set TimeZone = UTC;
SET
regress=# SELECT * FROM myts;
         ts          |          tstz          
---------------------+------------------------
 2015-01-07 14:00:00 | 2015-01-07 14:00:00+00
 2015-01-07 14:00:00 | 2015-01-07 06:00:00+00
(2 rows)

и эпохи:

regress=# SELECT extract(epoch from ts) as ets, extract(epoch from tstz) as etstz FROM myts;
    ets     |   etstz    
------------+------------
 1420639200 | 1420639200
 1420639200 | 1420610400
(2 rows)

Как видите, на вещи влияет ввод, а не вывод.

А как насчет явных часовых поясов?

А что, если мы установим явный часовой пояс во входных данных?

SET TimeZone = UTC;

INSERT INTO myts(ts,tstz) VALUES ('2015/01/07 14:00:00 +8','2015/01/07 14:00:00 +8');

Вы увидите, что эффект такой же, как если бы TimeZone был установлен на Australia/Perth, т. е. локальный параметр TimeZone игнорируется для ввода, поскольку временная метка содержит явный часовой пояс.

Это по-прежнему дает разные эпохи для временной метки с часовым поясом и без него. Спецификатор часового пояса удаляется из поля timestamp, поскольку он используется для преобразования поля timstamptz.

(Да, тот факт, что часовые пояса на timestamp отбрасываются, ужасен. Есть много ужасных вещей о времени SQL.)

Так как же добиться желаемого результата?

Используйте timestamp, если вам нужно местное время, а не точки всемирного времени.

В качестве альтернативы сообщите PostgreSQL, что вам нужна эпоха timestamptz без преобразования обратно в местное время, то есть в UTC, переинтерпретировав ее как временную метку в UTC с помощью оператора AT TIME ZONE:

SELECT extract(epoch from cast('2015/01/07 14:00:00' as timestamp) AT TIME ZONE 'UTC');

Или просто запустите свой сервер с часовым поясом, установленным на UTC. Честно говоря, это то, что делает большинство людей, поскольку семантика TimeZone и timestamp против timestamptz в большинстве случаев не очень полезна.

почему 8.3 отличается?

Понятия не имею, мне придется копаться в гораздо большем количестве заметок о выпуске и журналах коммитов, чем у меня есть время. Похоже, ввод timstamptz был изменен, чтобы уважать TimeZone, но я не знаю, когда и каково было точное обоснование в то время.

person Craig Ringer    schedule 08.01.2015
comment
Спасибо, Крейг, за то, что ты так ясно объяснил это. Ваши примеры должны быть в документации PostgreSQL. - person Alan; 08.01.2015