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).

That worked perfectly, many thanks.