postgresql - Postgres: query schedule given day, time_of_day, and time zone name -

i'm writing "send me messages @ time" app. i'm storing recurrence information in manner:

schedules ---------- days_of_week: [3, 4, 5] hours_of_day: [8, 13, 22] time_zone: "pacific time (us & canada)" 

works fine in displaying, need write frequent cron job grabs schedules "right (utc)". so, if the cron job running @ 09:00 utc monday, need grab schedules

• monday in days_of_week (where days_of_week @> array[1])
• hours_of_day @ 09:00 utc. given hours_of_day stored array of integers, storing user's time_zone.

so user may say: "deliver me message @ 9am monday" (which store [9]), means 9am in their time zone.

questions:

• any way query schedules given these parameters?
• if not, there better way structure data ensure easier querying through postgres? schema flexible.

thanks in advance!

postgres has superb facilities working timezones, , i've written similar you're asking here using at time zone construct. in addition fields, use last_scheduled_at flag when schedule last "executed"--i.e., when last successful cron job ran schedule avoid double-scheduling, , deleted_at logical deletion of schedules.

my schema schedules similar, except had single hour. stored days in array, you, , timezone text. fields in schedules table dows, hour, , timezone.

this query:

select   s.*   schedules s   array[extract(dow timestamptz (now() @ time zone timezone))] && dows     , hour = extract(hour timestamptz (now() @ time zone timezone))     , (s.last_scheduled_at null           or s.last_scheduled_at < (now() - interval '12 hours'))     , s.deleted_at null limit   1000 

i use && (overlaps) rather @> (contains), either works. you'll want limit can process work in batches (keep running , you're done hour x if 0 results; make sure you're done before hour up). you'll want pass timestamp parameter query--i've inlined here now() simplify things, passing time parameter makes testing lot easier.

note postgres can picky time zone names , abbreviations , behavior daylight saving time can counterintuitive: e.g., pacific standard time , pacific daylight time treated 2 distinct time zones (for purposes of at time zone):

maciek=# select now() @ time zone 'pst';           timezone           ----------------------------  2015-10-09 23:14:51.856813 (1 row)  maciek=# select now() @ time zone 'pdt';           timezone           ----------------------------  2015-10-10 00:14:54.402524 (1 row) 

that is, daylight saving time there, whether observing or not. if you're letting people enter time zone directly, it's either reject these or automatically coerce these 'america/los_angeles' (or whatever time zone happen map to), handle these conversions automatically according time zone rules postgres version has (make sure update point releases promptly if accuracy critical here areas have frequent time zone changes). list of time zone names used postgres can found in olson database. postgres tables pg_timezone_names , pg_timezone_abbrevs may of interest.