As always, wisdom personified by Tom Lane said :
> > regression=# create table foo (event_date_time timestamp);
> > CREATE
> > regression=# create index event_day on foo (date(event_date_time));
> > ERROR: DefineIndex: index function must be marked iscachable
> >
> > This raises a subtle point that you'd better think about before you go
> > too far in this direction: truncating a timestamp to date is not a very
> > well-defined operation, because it depends on the timezone setting.
> > Indexes on functions whose values might vary depend on who's executing
> > them are a recipe for disaster --- the index is almost certainly going
> > to wind up corrupted (out of order).
Tom, I clearly understand the problem but it is your developer's (I
should say "your designer's") POV.
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).
Can't we find a middle point ? I mean keep the current restrictions
regarding timezones but be able to create, say "noTZdate" field types
that would be cachable ?
Today we have only the options of :
- using no date index
- use inefficient date indexes
- convert dates to integers (eg: Julian) and index the integer
- convert dates to ISO strings and index the string
Same restrictions for date+time fields.
There's still something I don't understand : how are timestamps stored?
Don't you store : 1)universaltime or gmt 2)timezone ?
This way, timezones are only used to display a local date from a
universal value (which can be sorted normally)
Is it : 1)localtime 2)timezone
I guess I should RTFM or RTFS(ources)... Got a URL for dummies like me?
Oops! After re-reading my writing, I realize timezones are important in the US though it does not change the
problem.
Regards,
--
Hervé Piedvache
Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: herve@elma.fr
