Re: 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