Thread: expression index on date_trunc

Hi,

is is possible to create an expression index based on the date_trunc function?

Working with PostgreSQL 8.2, I'm trying to create an index using:

  CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day', request_received));

I get the error message:

  ERROR: functions in index expression must be marked IMMUTABLE
  SQL state: 42P17

I'd like to use the index to speed up a query that does a "group by" on the day part of a timestamp to lump all the
stuffthat happens in the same day together (I have a timestamp because the information in the table is coming from an
appserverlogfile and the date/time component of each line provides a full timestamptz (always the same tz!)). 

I'd be grateful if someone could point out what part of the statement is not IMMUTABLE or how I could mark my create
indexstatement as being immutable. 

Thanks,

Andy

    A Redhead wrote:

>   CREATE INDEX request_day_idx ON moksha_sm_request
(date_trunc('day', request_received));
>
> I get the error message:
>
>   ERROR: functions in index expression must be marked IMMUTABLE
[...]
> 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.

The retrieved value of request_received depends on your current
timezone, and so does the result of date_trunc, that would be why it's
not immutable.
If you don't need that behavior, you can shift your timestamptz to a
fixed timezone, both in your index and in your queries, as in:

CREATE INDEX request_day_idx ON moksha_sm_request
  (date_trunc('day', request_received at time zone 'Europe/Paris'));

--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

<a.redhead@openinternetsolutions.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!

><a.redhead@openinternetsolutions.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.