Re: with or without timezone? - Mailing list pgsql-general

From Tom Lane
Subject Re: with or without timezone?
Date
Msg-id 2785.1115479246@sss.pgh.pa.us
Whole thread Raw
In response to with or without timezone?  (Joshua N Pritikin <jpritikin@pobox.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Hrishikesh Deshmukh
Date:
Subject: Functions!
Next
From: Tony Caduto
Date:
Subject: Re: Functions!