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.

Answer 1

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.