><a.redhead@xxxxxxxxxxxxxxxxxxxxxxxxx> 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
