On 01/18/2013 09:31 AM, Robert James wrote:
> I'd like to better understand TIMESTAMP WITH TIME ZONE.
>
> My understanding is that, contrary to what the name sounds like, the
> time zone is never stored. It simply stores a UTC timestamp,
> identical to what TIMESTAMP WITHOUT TIME ZONE stores.
>
> And then the only difference is that WITH TIME ZONE will allow you to
> specify an offset in a literal value when INSERTing or UPDATEing ?
> That sounds to me like a conversion or function - why is that a
> different data type?
>
>
Though the type is called "timestamp with time zone" for historical
reasons, a better mental model is to think of that data type as a "point
in time." Think rocket launch, start of a conference-call, etc.
PostgreSQL happens to store the data internally as UTC but that is just
a reasonable and convenient way to store points in time and unimportant
from a user perspective. What is important is that the point in time can
be represented in whatever time zone is useful to the user. Furthermore,
PostgreSQL handles the daylight saving time (or European Summer Time or
...) rules applicable to the requested time zone.
select name, now() at time zone name from pg_timezone_names;
Note in the above that a "timestamptz at time zone somezone" returns a
value of type timestamp *without* time zone as you have provided both
the point-in time and the desired time zone.
In my work I find timestamp without time zone of little use but I can
see it being useful for events that are local-timezone-relative such as
"our stores are open from 9am to 5pm."
Date/time handling is tricky. It is worth spending some time reading and
re-reading the relevant sections of the manual - especially the warnings
- and playing with date/time manipulation till it "clicks."
Cheers,
Steve
