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!

Learn more about our mission by visiting IoraHealth.com and by following @IoraHealth.

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.

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).

Experiments

First let’s start with simply getting a timestamp without a time zone:

select
    timestamp '2012-03-08 21:00:00';

That produces 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 2012-03-14 23:00:00:

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

Results:

2012-03-15 06:00:00
    2012-03-15 07:00:00+01
    2012-03-14 23:00:00

Happy now?

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