Search Postgresql Archives

Timestamp with and without timezone conversion confusion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux