Re: Timestamp with and without timezone conversion confusion.

Tim Uckun wrote:
>> 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.

It is unintuitive and has caused many similar complaints
in the past, not least because other databases do it
differently.

The main difference between timestamp with time zone and
timestamp without is that the former will get converted
to your time zone (specified with the "TimeZone" parameter)
automatically, while the latter always looks the same.

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

Store an additional field "offset".
If you want to invest more energy and don't mind writing C,
you could create your own data type.

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

I think that this is required by the SQL standard.

But think of it that way:
It is the answer to the question "What is 2013-10-02 00:00:00 UTC
in Vienna?"
The answer is not time zone dependent. It should be
"2013-10-02 02:00:00" and not "2013-10-02 02:00:00 CEST".

Yours,
Laurenz Albe