Thread: date_trunc not immutable

On 12/15/18 3:26 PM, Ravi Krishna wrote:
> Version: PG 10.6 on AWS Linux.
> 
> I am trying to create an index on function date_trunc('month',timestamp)
> 
> PG is complaining that the function must be marked as IMMUTABLE.  So I 
> assume that date_trunc is not marked as immutable.
> 
> Definition of immutable from PG documentation
> ====================================
> 
> All functions and operators used in an index definition must be 
> "immutable", that is, their results must depend only on their arguments 
> and never on any outside influence (such as the contents of another 
> table or the current time). This restriction ensures that the behavior 
> of the index is well-defined. To use a user-defined function in an index 
> expression or WHERE clause, remember to mark the function immutable when 
> you create it.
> ===================================
> What am I missing?  date_trunc will always return the same value for a 
> given value. Not sure how I can mark a PG function as immutable.

No it won't:

show timezone;
   TimeZone
------------
  US/Pacific

  select date_trunc('hour', now());
date_trunc
------------------------
  2018-12-15 15:00:00-08

set timezone='UTC';

select date_trunc('hour', now());

date_trunc
------------------------
  2018-12-15 23:00:00+00



-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

On 2018-12-15, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 12/15/18 3:26 PM, Ravi Krishna wrote:
>> Version: PG 10.6 on AWS Linux.
>>
>> I am trying to create an index on function date_trunc('month',timestamp)
>>
>> PG is complaining that the function must be marked as IMMUTABLE.  So I
>> assume that date_trunc is not marked as immutable.
>>
>> Definition of immutable from PG documentation
>> ====================================
>>
>> All functions and operators used in an index definition must be
>> "immutable", that is, their results must depend only on their arguments
>> and never on any outside influence (such as the contents of another
>> table or the current time). This restriction ensures that the behavior
>> of the index is well-defined. To use a user-defined function in an index
>> expression or WHERE clause, remember to mark the function immutable when
>> you create it.
>> ===================================
>> What am I missing?  date_trunc will always return the same value for a
>> given value. Not sure how I can mark a PG function as immutable.
>
> No it won't:
>
> show timezone;
>    TimeZone
> ------------
>   US/Pacific
>
>   select date_trunc('hour', now());
> date_trunc
> ------------------------
>   2018-12-15 15:00:00-08
>
> set timezone='UTC';
>
> select date_trunc('hour', now());
>
> date_trunc
> ------------------------
>   2018-12-15 23:00:00+00
>

Ravi, the date_trunc('month',timestamp) is already immutable (at least in PG11):
postgres=# \df+ date_trunc
   Schema   |    Name    |      Result data type       |
Argument data types        | Volatility | ...
------------+------------+-----------------------------+-----------------------------------+------------+-...
 pg_catalog | date_trunc | timestamp without time zone | text,
timestamp without time zone | immutable  | ...


For the "date_trunc(text, timestampTZ) see Adrian's response, why it
does not always return the same values for the same input.

-- 
Best regards,
Vitaly Burovoy