Join our team!

We're looking for mid-level and senior software developers who love working throughout the stack, and have a track record for designing, building, shipping, and supporting web services and applications. Ideal candidates love Ruby, Rails and Ember, and using BDD and agile processes to ship working software quickly! Experience with Redis, Postgres, RabbitMQ, and ElasticSearch are a huge plus!

07 May 2012
by John G. Norman

Expressing PostgreSQL timestamps without zones in local time

TL;DR

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';

Shoutout

If someone knows a better way to do this, we’re all ears.

The problem

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.

Cases

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.

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:

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: