Timestamp with and without timezone conversion confusion.

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Timestamp with and without timezone conversion confusion.
Date: 2013-10-02 02:54:18
Message-ID: CAGuHJrNCzUuJpDms5CvkB2ezJg-E_f=Er+jQFN2N2sXHffmhqA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have the following query.

with parsed_data as (
SELECT
devicereportedtime ,
DATE_TRUNC('minute', devicereportedtime - (EXTRACT(minute FROM
devicereportedtime)::integer % 5 || 'minutes')::interval) as interval_start
FROM systemevents
WHERE devicereportedtime >= now() - interval '10 minutes'
ORDER BY devicereportedtime asc
limit 10000
),
grouped_data as (
SELECT
interval_start at time zone 'Etc/UTC' as interval_start,
MIN(devicereportedtime) at time zone 'Etc/UTC' as min_datetime,
MAX(devicereportedtime) at time zone 'Etc/UTC' as max_datetime
FROM parsed_data
GROUP BY interval_start
)

SELECT
interval_start,
(interval_start AT TIME ZONE 'Africa/Monrovia')::timestamp with time zone
as interval_start_in_africa,
min_datetime,
min_datetime AT TIME ZONE 'Europe/Berlin' as min_datetime_in_berlin,
max_datetime,
max_datetime AT TIME ZONE 'America/New_York' as max_datetime_in_new_york

FROM grouped_data gd

The field "devicereportedtime" is timstamp without time zone. The database
is set to 'Pacific/Auckland" (my development machine) and the devices are
reporting UTC.

The grouped_data clause forces the parsing of the timestamp without
timestamp to be UTC as well as the mins and the max timestamps. I then
want to present this data in other time zones

When I run this query in pgadmin I get the following results

"interval_start","interval_start_in_africa","min_datetime","min_datetime_in_berlin","max_datetime","max_datetime_in_new_york"
"2013-10-04 15:35:00+13","2013-10-04 02:35:00+13","2013-10-04
15:35:00+13","2013-10-04 04:35:00","2013-10-04 15:39:59+13","2013-10-03
22:39:59"
"2013-10-04 15:25:00+13","2013-10-04 02:25:00+13","2013-10-04
15:28:11+13","2013-10-04 04:28:11","2013-10-04 15:29:59+13","2013-10-03
22:29:59"
"2013-10-04 15:40:00+13","2013-10-04 02:40:00+13","2013-10-04
15:40:00+13","2013-10-04 04:40:00","2013-10-04 15:44:39+13","2013-10-03
22:44:39"
"2013-10-04 15:30:00+13","2013-10-04 02:30:00+13","2013-10-04
15:30:00+13","2013-10-04 04:30:00","2013-10-04 15:34:59+13","2013-10-03
22:34:59"

Notice that all the offsets are set to +13 which is my laptop's offset. Why
don't they show the offset of Africa or Berlin or whatever? Also note then
unless I explictly cast the data as timestamp with time zone all the
offsets go away and it's reported as timestamp without time zone.

So what am I doing wrong here?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2013-10-02 03:09:50 Re: Largest PG database known to man!
Previous Message John R Pierce 2013-10-02 02:05:09 Re: Largest PG database known to man!