Re: Timestamp with and without timezone conversion confusion.

[Tim Uckun <timuckun@xxxxxxxxx>]

>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.

That seems unintuitive. What is the difference between timestamp without time zone and timestamp with time zone? I was expecting to have the time zone stored in the field. For example one row might be in UTC  but the other row might be in my local time.

Maybe the question I need to ask is "how can I store the time zone along with the timestamp"   

>That is because AT TIME ZONE returns a "timestamp without time zone"

Also seems counterintutive but I guess I can aways convert it. I am just not getting the right offset when I convert. That's what's puzzling.

On Wed, Oct 2, 2013 at 9:15 PM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:

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