To convert from UTC in a PostgreSQL database to a local time, convert twice. E.g.,
select starts_at at time zone 'UTC' at time zone 'US/Pacific';
If someone knows a better way to do this, we’re all ears.
You properly save your data in PostgreSQL in UTC (For Rails, the default data type for timestamps in PostgreSQL is: “timestamp without time zone”). But you want to write some SQL reports that express those UTC dates in the local time, taking into account Daylight Savings Time. One of the reasons you might do this is because you are very concerned about the date portion of the timestamp: You might want to aggregate by a date that the report reader will understand. Why else? You might want to produce a report that can be exported to CSV and then imported into Excel so that the times are in the zone of the stakeholder using the spreadsheet.
So how do you get your query right in PostgreSQL?
This turns out to be non-obvious.
In 2012, daylight savings time began at 2 AM on 11 March 2012. So let’s compare two timestamps, one in standard time, the other in daylight savings.
The first will be at 1 PM US/Pacific on 8 March 2012. Since this is before the 11 March switchover to DST, the zone is PST (UTC-8 hours). This will be recorded in our database as UTC (without timestamp): 2012-03-08 21:00:00. (A nice tool for helping with these translations is timeanddate.com, for instance: http://www.timeanddate.com/worldclock/converted.html?day=8&month=3&year=2012&hour=13&min=0&sec=0&p1=127&p2=0)
The second will be at 11 PM US/Pacific on 14 March 2012. Since this is after the 11 March switchoevr to DST, the zone is PDT (UTC-7 hours). This will be recorded in our database as UTC: 2012-03-15 06:00:00 (http://www.timeanddate.com/worldclock/converted.html?day=14&month=3&year=2012&hour=23&min=0&sec=0&p1=127&p2=0).
First let’s start with simply getting a timestamp without a time zone:
select timestamp '2012-03-08 21:00:00';
2012-03-08 21:00:00: Yay.
Now let’s try and view that timestamp in the US/Pacific timezone. Consulting the PostgreSQL documentation (http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT-TABLE), we might try the “at time zone” syntax:
select timestamp '2012-03-08 21:00:00', timestamp '2012-03-08 21:00:00' at time zone 'US/Pacific';
That’s funny. We were expecting
2012-03-08 13:00:00-08 (see above). But here’s what we got (rearranging the output into rows):
2012-03-08 21:00:00 2012-03-09 06:00:00+01
Huh? Well, it happens that I’m running my database in Paris, France on May 3, 2012 (UTC+01). Here’s what the documentation says about the use of “at time zone” when applied to a timestamp without a timezone: “Treat given time stamp without time zone as located in the specified time zone.” Hmm. Then why is it showing “+01”? Well, it’s because when PostgreSQL displays a timestamp, it does it in your local time zone (here’s how it’s described for an example in the docs: “The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display”). So let’s check and see What
2012-03-09 06:00:00+01 in PST? It’s Thursday, 8 March 2012, 21:00:00 (http://www.timeanddate.com/worldclock/converted.html?day=9&month=3&year=2012&hour=6&min=0&sec=0&p1=195&p2=127). Well that’s dumb. All PostgreSQL did was take the literal timestamp value, pretend that it’s actually PST, and then display it in my local time zone.
This last paragraph is why you’re reading this blog post, isn’t it? PostgreSQL’s “at time zone” is surprising.
So how are we going to fix it?
Well, we know that our timestamps really are in UTC. Therefore, we are going to convert them to UTC, then we’re going to convert again to our target:
select timestamp '2012-03-08 21:00:00', timestamp '2012-03-08 21:00:00' at time zone 'UTC', timestamp '2012-03-08 21:00:00' at time zone 'UTC' at time zone 'US/Pacific';
Now we get:
2012-03-08 21:00:00 2012-03-08 22:00:00+01 2012-03-08 13:00:00
Notice that the time zone appears for the first conversion and disappears for the second. This is what the docs say should happen.
Finally, let’s try this pattern with our DST example. This is where we want to see
2012-03-15 06:00:00 (UTC) converted to local time
select timestamp '2012-03-15 06:00:00', timestamp '2012-03-15 06:00:00' at time zone 'UTC', timestamp '2012-03-15 06:00:00' at time zone 'UTC' at time zone 'US/Pacific';
2012-03-15 06:00:00 2012-03-15 07:00:00+01 2012-03-14 23:00:00
And about time zone names and Daylight Savings Time
You’ll notice that in these examples I’ve scrupulously used the time zone “US/Pacific” – This is because the three-letter time zone abbreviations are already encoded for standard or daylight savings time. If you want the automatic conversion, use the full name. You can get a full list of the names with
select * from pg_timezone_names; (see http://www.postgresql.org/docs/9.1/static/view-pg-timezone-names.html). Our application is a Rails application, so we typically use a case statement to convert from the Rails-style name to the PostgreSQL-style name:
select patients.created_at as "UTC", patients.created_at at time zone 'UTC' at time zone case practices.time_zone when 'Eastern Time (US & Canada)' then 'US/Eastern' when 'Pacific Time (US & Canada)' then 'US/Pacific' end as "Local" from patients, practices where practices.id = patients.practice_id
At some point we might create a helper table to manage the time zone name conversion.
Functional syntax for “at time zone”
One last thing. You might be more comfortable with the functional syntax for these conversions. Example:
select timestamp '2012-03-15 06:00:00', timezone('UTC', timestamp '2012-03-15 06:00:00'), timezone('US/Pacific', timezone('UTC', timestamp '2012-03-15 06:00:00'));