PostgreSQL: selecting rows that occur on a certain day of the week, in a specific time zone

Question

I've got a table that stores "snapshot" data - the number of staff at work is captured every 10 minutes and stored in it. I'd like to generate a report to show the number of staff at work over the course of a day on a specific weekday (eg. for the last four Sundays).

In Rails my query looks like this:

<model>.where("EXTRACT(dow FROM (time + interval '#{time_zone_offset} hours')) = ?", Time.zone.now.wday)
       .where('time BETWEEN ? AND ?', 5.weeks.ago.end_of_week, 1.week.ago.end_of_week)
       .select("organisation_id, date_trunc('hour', time) as grouped_time, avg(staff) as staff")
       .group("grouped_time").order("grouped_time")

And that translates to this SQL:

SELECT date_trunc('hour', time) as grouped_time, avg(staff) as staff
FROM <model>
WHERE (EXTRACT(dow FROM (time + interval '10 hours')) = 0)
AND (time BETWEEN '2013-09-22 13:59:59.999999' AND '2013-10-20 13:59:59.999999')
GROUP BY grouped_time
ORDER BY grouped_time

In this case, time_zone_offset would differ based on the user I am doing the lookup for:

def time_zone_offset
  @tzoffset ||= ActiveSupport::TimeZone[current_user.organisation.time_zone].utc_offset / 60 / 60
end

(The current time zone is also set in an around_filter, so that the 1.week.ago etc. are in the correct zone.)

My database's time zone is UTC (set TIME ZONE 'UTC').

This works, but I'm sure there's a better way to do find records on a particular day of the week then by manipulating the interval by hand. I also don't think that will work with DST in time zones where that is applicable. I know PostgreSQL is capable of converting a time with time zone to a particular time zone, but that doesn't include a date, so I can't figure out the day of the week! Other WHERE clauses I have tried:

• EXTRACT (dow from time') = 0 - this gives me snapshots between 10 AM Sunday and 10 AM Monday

• EXTRACT (dow from time at time zone 'Brisbane/Australia') = 0 - this gives me actions between 8pm Sunday and 8pm Monday. My understanding is that this happens because Postgres treats the time field as if it is in Brisbane time, instead of converting it from UTC to Brisbane time.

So I'd love to know if there is something I should be doing to get this query working.

Answer 1

AT TIME ZONE when applied to a timestamp without timezone produces a timestamp with timezone (and the other way around). And this timestamp with timezone is interpreted in your session's timezone (which in your case is forced to UTC).

So the expression EXTRACT (dow from time at time zone 'Brisbane/Australia') does not extract the day in Brisbane at time (UTC), it extracts the day corresponding to the converted time from the point of view of someone virtually living in the UTC time zone.

As an example, when I'm typing this, if pretending to be in UTC:

=> set timezone to 'UTC';
=> select now(),now() at time zone 'Australia/Brisbane';
             now              |         timezone          
------------------------------+---------------------------
 2013-10-27 18:01:03.15286+00 | 2013-10-28 04:01:03.15286

Fine, it's Sunday 18:01 in UTC and Monday 04:01 at Brisbane

But if applying the timezone displacement to a timestamp without timezone:

select now(),now()::timestamp at time zone 'Australia/Brisbane';
              now              |           timezone            
-------------------------------+-------------------------------
 2013-10-27 18:01:57.878541+00 | 2013-10-27 08:01:57.878541+00

Notice how the second column differs from the previous result. It's actually 20 hours off of Brisbane expressed in UTC: it's presumably the technically correct answer to a question that doesn't make much sense.

Presumably you want this:

EXTRACT (dow from (time AT TIME ZONE 'UTC') at time zone 'Brisbane/Australia')=0

which should answer: does the date and time time as measured in UTC corresponds to a Sunday in Brisbane?