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

SQL Fiddle demonstrating summer / winter.