Re: Timestamp with and without timezone conversion confusion.

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