Re: expression index on date_trunc

<a(dot)redhead(at)openinternetsolutions(dot)com> writes:

> CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day', request_received));
...
> I'd be grateful if someone could point out what part of the statement is not
> IMMUTABLE or how I could mark my create index statement as being immutable.

date_trunc(timestamp with time zone) is not immutable because it depends what
your current time zone is. That is, if you change what time zone you're in a
timestamp with time zone could appear to change from one day to another.

However date_trunc(timestamp without time zone) is immutable. So I think what
you have to do is build your index on:

date_trunc('day', request_received AT TINE ZONE 'GMT')

or whatever time zone you're interested in. That will get you the day part of
that timestamp at that time zone (because it first casts it to a timestamp
without time zone for the time zone you specify).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!