Учет DST в Postgres при выборе запланированных элементов

Ответ 1

Используйте timestamp with time zone (timestamptz) для вычислений.
Время для будильника может быть time [without time zone].
Но вы должны сохранить часовой пояс явно для каждой строки.

Никогда использовать time with time zone Это логически сломанный тип, его использование не рекомендуется PostgreSQL. Я цитирую здесь руководство:

Тип времени с часовым поясом определяется стандартом SQL, но определение показывает свойства, которые приводят к сомнительной полезности. В большинстве случаев комбинация даты, времени, временной метки без времени зона и временная метка с часовым поясом должны обеспечивать полный диапазон функциональность даты/времени, требуемую любым приложением.

Демо-настройка:

CREATE TABLE alarm(name text, t time, tz text);
INSERT INTO alarm VALUES
 ('Alfred', '04:00', 'Europe/Stockholm') -- Alfred sets an alarm for 4 AM.
,('Lotta',  '05:00', 'Europe/Stockholm') -- Lotta sets an alarm for 5 AM. 
,('Sharon', '11:00', 'Asia/Singapore');  -- Sharon has set an alarm for 11 AM.

Это должно быть имя часового пояса (а не аббревиатуры) для учета DST.
Рассмотрите информацию в этом связанном вопросе.

Получить соответствующие сигналы тревоги для "сегодня":

SELECT *
FROM   alarm
WHERE  (('2012-07-01'::date + t) AT TIME ZONE tz AT TIME ZONE 'UTC')::time
       = '03:00'::time

• ('2012-7-1'::date + t)... собрать timestamp [without time zone] Также может быть now()::date + t для "сегодня".
• AT WITH TIME ZONE tz... отметьте метку времени в сохраненном часовом поясе, в результате получим timestamptz.
• AT WITH TIME ZONE 'UTC'... получить соответствие UTC timestamp
• ::time... самый простой способ извлечь временную составляющую.

Здесь вы можете найти названия часовых поясов:

SELECT *
FROM   pg_timezone_names
WHERE  name ~~* '%sing%'
LIMIT  10

SQL Fiddle, демонстрирующий лето/зиму.

Ответ 2

Вы сделали бы это, используя полное имя часового пояса, например. Америка /New _York, а не EDT/EST, и сохранение часа в этом часовом поясе не UTC. Затем вы можете оставаться блаженно незнающими изменения смещения для летнего времени.

Что-то вроде следующего должно работать:

-- CREATE TABLE time_test (
--   user_to_alert CHARACTER VARYING (30),
--   alarm_hour TIME,
--   user_timezone CHARACTER VARYING (30)
-- );

SELECT user_to_alert, 
  CASE
    WHEN EXTRACT(HOUR FROM CURRENT_TIME AT TIME ZONE user_timezone) = EXTRACT(HOUR FROM alarm_hour) THEN TRUE
  ELSE FALSE
END AS raise_alarm
FROM time_test;

Или:

SELECT user_to_alert
FROM time_test
WHERE EXTRACT(HOUR FROM CURRENT_TIME AT TIME ZONE user_timezone) = EXTRACT(HOUR FROM alarm_hour);

Ответ 3

Дано:

SET timezone = 'UTC';

CREATE TABLE tzdemo (
    username text not null,
    alarm_time_utc time not null,
    alarm_tz_abbrev text not null,
    alarm_tz text not null
);

INSERT INTO tzdemo (username, alarm_time_utc, alarm_tz_abbrev, alarm_tz) VALUES
('Alfred', TIME '04:00' AT TIME ZONE '+01:00', 'CET', 'Europe/Stockholm'),
('Lotta', TIME '05:00' AT TIME ZONE '+02:00', 'CEST', 'Europe/Stockholm'),
('Sharon', TIME '11:00' AT TIME ZONE '+08:00', 'SGT', 'Singapore');

Try:

SELECT username 
FROM tzdemo 
WHERE alarm_time_utc AT TIME ZONE alarm_tz_abbrev = TIME '03:00' AT TIME ZONE alarm_tz;

Результат:

 username 
----------
 Alfred
 Sharon
(2 rows)

Принцип:

• Хранить смещение часовой пояс, созданный с учетом того, был ли он или не был DST в то время
• Также сохраните время, преобразованное в UTC
• При запросе используйте тот факт, что полное имя часового пояса соответствует текущим правилам UTC, чтобы время создавало время, которое в текущем часовом поясе для региона. Сравните с сохраненной меткой времени в том, что было в часовом поясе, когда был создан этот сигнал.

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

Этот подход может быть расширен по дате - присвоение временных меток, когда вы хотите выполнить прогностическое задание, например "в какое местное время будет звучать сигнал тревоги в местоположении".

Я не уверен в этом решении и рекомендую тщательное тестирование.