Thread: with or without timezone?

I am designing a new schema from scratch.  Which type is more natural to
work with, a timestamp with a timezone or without a timezone?  I read
the 7.4 documentation on timezones two or three times but I'm still not
really sure.  The fact that the default is without time zone suggests
that without is more natural.  But I have a vague preference for with
time zone.  Can someone push me in the right direction?

--
If you are an American then support http://fairtax.org
 (Permanently replace 50,000+ pages of tax law with about 200 pages.)

Joshua N Pritikin <jpritikin@pobox.com> writes:
> I am designing a new schema from scratch.  Which type is more natural to
> work with, a timestamp with a timezone or without a timezone?  I read
> the 7.4 documentation on timezones two or three times but I'm still not
> really sure.  The fact that the default is without time zone suggests
> that without is more natural.  But I have a vague preference for with
> time zone.  Can someone push me in the right direction?

The default is compelled by the SQL spec; it's not what the developers
think is the most rational choice ;-)

If you are storing timestamps that represent absolute points in time,
I would definitely recommend using TIMESTAMP WITH TIME ZONE.  That
will have the "correct" value if/when you look at the data from another
TimeZone setting.

I haven't seen all that many applications where TIMESTAMP WITHOUT
TIME ZONE was really the best choice; although if you want to do
date arithmetic without thinking about daylight-saving-transition
funnies, I suppose it has its uses.  If you're not interested in
time-of-day at all, you should choose plain DATE.

            regards, tom lane