Accounting for DST in Postgres, when selecting scheduled items

Question

I have a Postgres table of clock alarms (not really, but this is analogous, and easier to explain). Alarms are set by users with a 1 hour resolution, and users can be from many different timezones. The alarms are repeating daily. I want to reliably fetch the alarms that are supposed to go off at a particular hour of the day, and I am having problems with daylight saving time. How do I do this in the best way?

Example

Alfred and Lotta both live in Stockholm (+1 hour from UTC, but +2h when it's DST).
Sharon lives in Singapore (+8 hours from UTC, no DST)

During winter, Alfred sets an alarm for 4 AM. The alarm should go off at 4 AM local time, all year.
During summer, Lotta sets an alarm for 5 AM. Again, it should go off at 5 AM all year round.
Meanwhile, Sharon has set an alarm for 11 AM.

All of these can be stored in the database as 03:00 UTC.

If I query the database in the winter for alarms that should go off at 03:00 UTC, I want Alfred's and Sharon's alarms. Singapore is now +7h from Sweden, so 11 AM in Singapore is 4 AM in Sweden. Lotta's alarm should not go off for another hour.

Conversely, if I query the database in the summer for alarms that should go off at 03:00 UTC, I want Lotta's and Sharon's alarms. Singapore is +6h from Sweden now, so 11 AM in Singapore is 5 AM in Sweden now. Sven's alarm went off an hour ago.

How do I store this, and query the database?

I can change the db schema if necessary. At the moment, we don't adjust for DST at all, and in fact just have an "hour" integer field (which seems dumb, a time field would be better).

It seems I need to store both a UTC time and timezone information, but I don't know how to best achieve this in Postgres. I've found that Postgres has some sort of concept of timezones, but no timezone field type as far as I can tell. Also, I guess I need to do some calculations in SQL to determine how to offset the UTC time in the select, based on the timezone data and the creation date. I'm not great with SQL…

I do want to solve this in Postgres, as there can be a lot of "alarms", and I want to avoid the performance issues that come with fetching all of them into Ruby and filter there. (Yes, this is a Rails app.)

Answer 1

Use timestamp with time zone (timestamptz) for calculations.
Times for alarms can be time [without time zone].
But you have to save the time zone explicitly for every row.

Never use time with time zone (timetz) It's a logically broken type, its use is discouraged by PostgreSQL. The manual:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without timezone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

Demo setup:

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.

It has to be time zone names (not abbreviations) to account for DST. Related:

• Time zone names with identical properties yield different result when applied to timestamp

Get matching alarms for "today":

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) ... assemble timestamp [without time zone] Could also just be now()::date + t for "today".
• AT WITH TIME ZONE tz ... place timestamp at the saved time zone, resulting in timestamptz.
• AT WITH TIME ZONE 'UTC' ... get according UTC timestamp
• ::time ... simplest way to extract the time component.

Here you can look up time zone names:

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

db<>fiddle here - demonstrating summer / winter
_{Old sqlfiddle}

Answer 2

You would do it by using a full time zone name, e.g. America/New_York rather than EDT/EST, and storing the hour in that time zone not UTC. You can then remain blissfully ignorant of the offset changes for daylight savings.

Something like the following should work:

-- 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;

Or:

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

Answer 3

Given:

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;

Result:

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

Principle:

• Store the timezone offset the alarm was created in including whether it was or was not DST at the time
• Also store the clock time converted to UTC
• When querying, use the fact that full timezone names follow the current UTC rules for times to produce a time that's in the current time zone for the region. Compare to the stored timestamp in what the time zone was when that alarm was created.

This also allows you to cope with cases where the user changes location, and therefore changes timezone.

This approach can be extended by date-qualifying the timestamps when you want to do predictive querying, like "at what local time will alarm sound in location".

I'm not completely confident in this solution and would recommend careful testing.