Converting postgres time zones

I’ve always found working with time zones to be a bit confusing.  Here is a simple way to convert from UTC to any time zone.

Query without conversion:

select id, remote_ip, device_type, platform, created_at from visitors where autovist_url_id = 25032;

Query with conversion:

select id, remote_ip, device_type, platform, (created_at at time zone 'UTC') at time zone 'EST' as created_at from visitors where autovist_url_id = 25032;

UPDATE: March 22, 2017

The query with conversion began to give the wrong time results once we moved into Daylight Savings time this month.  Reading the postgres manual closely, I found this in section 8.5.3:

In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. As an example, 2014-06-04 12:00 America/New_York represents noon local time in New York, which for this particular date was Eastern Daylight Time (UTC-4). So 2014-06-04 12:00 EDT specifies that same time instant. But 2014-06-04 12:00 EST specifies noon Eastern Standard Time (UTC-5), regardless of whether daylight savings was nominally in effect on that date.

This revised query works all year around (‘EST’ replaced with ‘America/New_York’):

select id, remote_ip, device_type, platform, (created_at at time zone 'UTC') at time zone 'America/New_York' as created_at from visitors where autovist_url_id = 25032;

Chris Downey

Founder, VaxCalc Labs and creator of Autovist and the Vaccine Ingredients Calculator.

Author: Chris Downey

Founder, VaxCalc Labs and creator of Autovist and the Vaccine Ingredients Calculator.

Leave a Reply