Re: Timezone locale consistency for functional indexes

On 06/12/2017 06:11 AM, Olav Gjerde wrote:
> 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
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com