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(dot)klaver(at)aklaver(dot)com