Tim Uckun wrote: > I have the following query. [...] > 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 > 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","ma > x_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? The configuration parameter "TimeZone" determines how "timestamp with time zone" is interpreted and converted to a string. The reason for that is that in PostgreSQL there is no time zone information stored along with a "timestamp with time zone", it is stored in UTC. > 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. That is because AT TIME ZONE returns a "timestamp without time zone" in this case, see the documentation. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
