Re: Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 10:40:36AM -0700, Steve Crawford 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.
> >A better name might perhaps been "timezone aware timestamp".
> >
> >Karsten
>
> The trouble is that it isn't timezone aware.

INSERT/UPDATE is, SELECT is not :-)

> When I have to explain this I tend to tell people to mentally change
> "timestamp with time zone" to "point-in-time". That "point-in-time"
> data can be represented in many different formats and "localized" to
> different zones but they are all the identical point-in-time.

That is, indeed, a helpful metaphor.

Maybe an explicit statement could be added to the docs
(I just checked 9.3) to the effect that "no, the input
time zone is NOT stored and can NOT be retrieved later,
think of it as a point-in-time".

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346