Re: date_trunc not immutable

Ravi Krishna <srkrishna@fastmail.com> writes:
> I am trying to create an index on function date_trunc('month',timestamp)
> PG is complaining that the function must be marked as IMMUTABLE.

The timestamptz version of it is not immutable, because its effects depend
on the timezone setting:

regression=# set timezone = 'America/New_York';
SET
regression=# select date_trunc('month', now());
       date_trunc       
------------------------
 2018-12-01 00:00:00-05
(1 row)

regression=# set timezone = 'Europe/Paris';
SET
regression=# select date_trunc('month', now());
       date_trunc       
------------------------
 2018-12-01 00:00:00+01
(1 row)

If you want immutability, you need to be working with timestamp-without-tz
or date input, so that timezone isn't a factor.

            regards, tom lane