Как правильно обрабатывать летнее время с отметками времени

Я пытаюсь создать таблицу в Postgres, в которой будут храниться события, происходящие один раз в час каждый день в течение следующих нескольких лет. Поэтому я заполнил столбец, используя следующее выражение:

INSERT INTO tablename(time) 
SELECT CAST('2013-01-01' AS DATE) + (n || ' hour')::INTERVAL 
 FROM generate_series(0, 100000) n;

В качестве типа данных для этого столбца я выбрал отметка времени с часовым поясом и надеялся, что таким образом будет автоматически учитываться переход на летнее время. (Кстати, мой часовой пояс по умолчанию — CET, поэтому это UTC+1 или UTC+2, когда применяется летнее время). В результате вышеуказанного запроса я получаю это:

  • 2013-03-31 00:00:00 +01
  • 2013-03-31 01:00:00 +01
  • 2013-03-31 03:00:00 +02
  • 2013-03-31 03:00:00 +02
  • 2013-03-31 04:00:00 +02
  • ...
  • 2013-10-27 00:00:00 +02
  • 2013-10-27 01:00:00 +02
  • 2013-10-27 02:00:00 +01
  • 2013-10-27 03:00:00 +01
  • 2013-10-27 04:00:00 +01
  • ...

Смещение относительно UTC меняется, и я ожидал, что 02:00 пропущено 31 марта, так как в этом дне всего 23 часа, но я не знаю, почему 03:00 есть дважды, тогда как 27 октября 02:00 есть только там один раз вместо двух, так как в этом дне 25 часов. Чего я хотел бы добиться, так это того, чтобы для всех лет в определенный день марта 2 часа не пропускались (я бы предпочел поставить «н. д.» или что-то еще для соответствующего значения) и что есть две записи для 3 часов в определенный день в октябре (но не в марте), так что я получу столбец следующего вида (где 1 означает час с 00:00 до 1:00, 2 для 1:00-2:00 , и т.д.):

  • 2013-03-31 1 +01
  • 31.03.2013 2 +01
  • 31.03.2013 3 +02
  • 31.03.2013 4 +02
  • 2013-03-31 5 +02
  • ...
  • 2013-10-27 1 +02
  • 27.10.2013 2 +02
  • 27.10.2013 3A +02
  • 27.10.2013 3B +01
  • 27.10.2013 4 +01
  • 2013-10-27 5 +01
  • ...

Кто-нибудь знает, как это сделать? Я делаю что-то принципиально неправильно? Это просто вопрос форматирования? Должен ли я писать функцию? Любая помощь будет оценена по достоинству. Спасибо.


postgresql sql timezone timestamp-with-timezone dst
person user3403354    schedule 16.03.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Дата и время в Postgres хранятся в формате UTC и преобразуются в местное время в соответствии с поясом, указанным в конфигурации часового пояса.

Это означает, что вам нужно только решить проблему представления. Попробуйте использовать AT TIME ZONE 'UTC+2' для преобразования времени UTC в ваш часовой пояс и посмотрите результат. Вот запрос:

SELECT (CAST('2013-03-30' AS DATE) + (n || ' hour')::INTERVAL) AT TIME ZONE 'UTC+2' 
    FROM generate_series(0, 1000) n;
person Nick    schedule 16.03.2014

arrow_upward
0
arrow_downward

Временная метка всегда сохраняется в формате UTC независимо от настроек часового пояса. Из руководства

Для метки времени с часовым поясом внутренне сохраненное значение всегда находится в формате UTC (универсальное скоординированное время, традиционно известное как среднее время по Гринвичу, GMT). Входное значение с явно заданным часовым поясом преобразуется в формат UTC с использованием соответствующего смещения для этого часового пояса. Если во входной строке часовой пояс не указан, предполагается, что он находится в часовом поясе, указанном системным параметром TimeZone, и преобразуется в формат UTC с использованием смещения часового пояса.

set time zone 'CET';

drop table if exists events;
create table events (
    tstz timestamp with time zone
);
insert into events (tstz)
select generate_series('2013-01-01', '2013-10-28', interval '1 hour') s(tstz)
;

Обратите внимание на использование функции generate_series.

select
    tstz at time zone 'UTC' as "UTC",
    tstz at time zone 'CET' as "CET",
    tstz at time zone 'CEST' as "CEST",
    tstz as "LOCAL"
from events
where date_trunc('day', tstz) in ('2013-03-31', '2013-10-27')
order by tstz
;
         UTC         |         CET         |        CEST         |         LOCAL          
---------------------+---------------------+---------------------+------------------------
 2013-03-30 23:00:00 | 2013-03-31 00:00:00 | 2013-03-31 01:00:00 | 2013-03-31 00:00:00+01
 2013-03-31 00:00:00 | 2013-03-31 01:00:00 | 2013-03-31 02:00:00 | 2013-03-31 01:00:00+01
 2013-03-31 01:00:00 | 2013-03-31 02:00:00 | 2013-03-31 03:00:00 | 2013-03-31 03:00:00+02
 2013-03-31 02:00:00 | 2013-03-31 03:00:00 | 2013-03-31 04:00:00 | 2013-03-31 04:00:00+02
 2013-03-31 03:00:00 | 2013-03-31 04:00:00 | 2013-03-31 05:00:00 | 2013-03-31 05:00:00+02
...
 2013-10-26 22:00:00 | 2013-10-26 23:00:00 | 2013-10-27 00:00:00 | 2013-10-27 00:00:00+02
 2013-10-26 23:00:00 | 2013-10-27 00:00:00 | 2013-10-27 01:00:00 | 2013-10-27 01:00:00+02
 2013-10-27 00:00:00 | 2013-10-27 01:00:00 | 2013-10-27 02:00:00 | 2013-10-27 02:00:00+02
 2013-10-27 01:00:00 | 2013-10-27 02:00:00 | 2013-10-27 03:00:00 | 2013-10-27 02:00:00+01
 2013-10-27 02:00:00 | 2013-10-27 03:00:00 | 2013-10-27 04:00:00 | 2013-10-27 03:00:00+01
 2013-10-27 03:00:00 | 2013-10-27 04:00:00 | 2013-10-27 05:00:00 | 2013-10-27 04:00:00+01
 2013-10-27 04:00:00 | 2013-10-27 05:00:00 | 2013-10-27 06:00:00 | 2013-10-27 05:00:00+01

Если столбец timestamp with timestamp выбран без использования at time zone как и в столбце LOCAL выше, оно будет выведено в часовом поясе сервера с этой отметкой времени. Именно поэтому есть пропущенные и дублированные часы.

Я думаю, что ваш желаемый результат неверен. Но это достижимо с некоторыми запросами

Я не могу воспроизвести ваш фактический вывод. Какой часовой пояс сервера?

show time zone;
 TimeZone 
----------
 CET
person Clodoaldo Neto    schedule 16.03.2014