Query across timezones

Question

I'm developing an app where a user can request that an email be sent to them at a specific time every day in their timezone. For example User A lives in London and schedules an email at 2pm every day London time and User B lives in New York and schedules an email at 2pm New York time.

I'm wondering what way I should configure my database postgres such that a scheduler can fire every minute and query for all emails to be sent at that minute regardless of what timezone their in.

The one thing I want to avoid is having to run multiple queries, once per timezone.

Most timezones are 1 hour apart from each other. Only a handful are half-hour apart. So you could have your scheduler fire every 30 minutes instead of every minute. Also, I suggest storing all times in UTC, so there is less bother about time-zone conversions. — Geeky Guy, May 18 '14 at 18:44
I forgot to mention that people can schedule emails on the minute mark so the scheduler needs to fire once per minute. So if I were to store all times in utc, and have a column for the time zone information; is there some query I can run so as to get back all emails to send at the same UTC time. i.e London user wants an email at 2pm London time and NY user wants an email at 9am NY time. Both these emails should send at the same UTC time given the current DST in both locations. — KJF, May 18 '14 at 18:52
I just wouldn't bother about storing any time zone information at all, at least not in the schedule times table. I would just store the UTC times at which emails must be sent. If a user wishes to see at what time their emails will be sent, you can then get the timezone from either their geolocation, or from a TZ column in the user profile, then add the offset to the time stored in the schedule table. As for queries... to help you with queries, we'd need to know your schema. — Geeky Guy, May 18 '14 at 18:54
Thanks for the response. The schema hasn't been completed yet since it will largely depend on getting this functionality worked out so the schema is a blank canvas. I'll need the timezone information for the scheduler to account for daylight savings time. Otherwise emails will be received 1 hour earlier than expected when DST kicks in. — KJF, May 18 '14 at 19:10
You could store the local time zones at which the emails are to be sent and convert them during your select using `AT TIME ZONE ('UTC')`, which I think will handle DST offsets — FuzzyTree, May 18 '14 at 19:15

score 3 · Accepted Answer · edited May 23 '17 at 12:27

Due to the (rather idiotic, quite frankly) rules for daylight saving times (DST) across the world, a local time can mean all kind of things in absolute (UTC time).

Save a time (not timetz!) and the time zone name (not the abbreviation) for when to send the emails. Tricky details under this related question:
Time zone names with identical properties yield different result when applied to timestamp

CREATE TABLE event (
   event_id serial PRIMARY KEY
 , alarm_time time  -- local alarm time
 , tz text          -- time zone name
 , ...
);

Use the following expression to "cook" the exact daily point in time, taking local DST settings into account:

SELECT current_date + alarm_time AT TIME ZONE tz;

Example:

SELECT current_date + '2:30'::time AT TIME ZONE 'Europe/London' AS alarm_ts

Returns:

alarm_ts
2014-05-19 02:30:00+02

Use timestamp with time zone (timestamptz) across your whole application. Be sure to understand how it works. This comprehensive post may be of help (also explains the AT TIME ZONE construct:
Ignoring timezones altogether in Rails and PostgreSQL

Just to be clear, once you have "cooked" the daily UTC time, you can translate it to and work with any local time just as well. But it might be less confusing to do all the rest in UTC.

Query across timezones

1 Answers1