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;
Latest posts by Chris Downey (see all)
- Dramatic improvement by replacing ActiveRecord loop with single SQL query - March 17, 2017
- Activist best practice: auto-archiving web pages - February 4, 2017
- Pre-calculating vaccine ingredients: from PostgreSQL to HTML data-* - February 1, 2017