Hervé Piedvache <herve@elma.fr> writes:
> Most of us, users of PG (app developers I mean) never have to deal
> with timezones and that's where we conflict : we can't use (I mean as
> efficiently as could be) date indexes because of timezones which WE
> don't care about (at least in, say, 90% of the apps that use DB).
If you don't care about timezone handling, you should be using timestamp
without time zone. Observe:
regression=# create table foo (tnz timestamp without time zone,
regression(# tz timestamp with time zone);
CREATE
regression=# create index fooi on foo(date(tz));
ERROR: DefineIndex: index function must be marked iscachable
regression=# create index fooi on foo(date(tnz));
CREATE
regression=#
timestamp-with-timezone is really GMT under the hood; it's rotated to
your local timezone (as shown by TimeZone) before conversion to date,
and that's why timestamp-with-timezone-to-date is, and should be,
noncachable.
On the other hand, timestamp without time zone is not assumed to be
in any particular zone, and there's never any rotation to local or to
GMT. So that conversion to date is deterministic.
Some examples (I'm in EST, ie GMT-5):
regression=# select '2002-02-21 08:00-05'::timestamp with time zone; timestamptz
------------------------2002-02-21 08:00:00-05
(1 row)
regression=# select '2002-02-21 08:00+09'::timestamp with time zone; timestamptz
------------------------2002-02-20 18:00:00-05
(1 row)
regression=# select date('2002-02-21 08:00+09'::timestamp with time zone); date
------------2002-02-20
(1 row)
regression=# select '2002-02-21 08:00+09'::timestamp without time zone; timestamp
---------------------2002-02-21 08:00:00 -- the timezone indication is simply dropped
(1 row)
regression=# select date('2002-02-21 08:00+09'::timestamp without time zone); date
------------2002-02-21
(1 row)
BTW, 7.2 assumes plain "timestamp" to denote "timestamp with time zone";
this is for backwards compatibility with the behavior of previous
releases' timestamp datatype. However, the SQL spec says that
"timestamp" should mean ""timestamp without time zone", so we are
probably going to change over eventually.
(Hey Thomas, did I get all that right?)
regards, tom lane