Re: Timezone locale consistency for functional indexes

I figured out the issue, it was as simple as some developers used the
default in Java's Hibernate which created the timestamp columns
without time zone.

Anyway I guess this is the correct approach that also take summer time
into consideration? And using the immutable function wrapper is wrong?

On Mon, Jun 12, 2017 at 2:25 PM, Olav Gjerde <olav(at)backupbay(dot)com> wrote:
> I have a table that I try create an functional index on like this:
>
> CREATE INDEX my_index_name
> ON opening_hours (
> opening_hours_type,
> EXTRACT(YEAR FROM date),
> EXTRACT(MONTH FROM date)
> )
>
> But I get the following error: functions in index expression must be
> marked IMMUTABLE
>
>
> But if I change it to:
>
> CREATE INDEX my_index_name
> ON opening_hours (
> opening_hours_type,
> EXTRACT(YEAR FROM date AT TIME ZONE 'UTC'),
> EXTRACT(MONTH FROM date AT TIME ZONE 'UTC')
> )
> The index will be created.
>
> On other systems it could be the oppsite, that it only works without
> the additional AT TIME ZONE
>
> On all systems, the default Timezone in postgresql.conf is set to UTC
> and show timezone; return UTC. Additionally show lc_time; returns
> nb_NO.UTF-8
>
> What kind of system settings could cause this behaviour? We run
> Ubuntu Linux 16.04 and Postgresql 9.6
>
> Another question is, should I just create immutable functions wrappers
> for this instead?
>
>
> --
> Kind Regards / Med Vennlig Hilsen
>
> Olav Grønås Gjerde

--
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde

BackupBay Gjerde
Asalvegen 19
4051 SOLA
Norway
Phone: +47 918 000 59