Re: Error: timestamp with timezone + interval is not immutable while creating index - Mailing list pgsql-general

On 10/13/2011 04:32 PM, Tom Lane wrote:
> Phil Couling<couling@gmail.com>  writes:
>> main=>  create index foo_next_update on foo( (last_updated + update_cycle) ) ;
>> ERROR:  functions in index expression must be marked IMMUTABLE...
>
> timestamptz + interval is not immutable because the results can vary
> depending on timezone.  For instance, in my zone (America/New_York):
>
So it seems like a potential workaround, depending on the nature of your
data and applications, would be to convert the timestamptz into a
timestamp at a reference TZ:

steve=# create table testfoo (a_timestamptz timestamptz, an_interval
interval);
CREATE TABLE
steve=# create index testfoo_index on testfoo ((a_timestamptz at time
zone 'UTC' + an_interval));
CREATE INDEX

You will have to be sure you are getting the results you want in the
vicinity of DST changes and if you are handling multiple timezones.

Cheers,
Steve