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?