Re: epoch and timezone changed bevior

=# show timezone;
TimeZone
-----------
localtime
(1 row)

On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 09/24/2015 06:42 AM, Willy-Bas Loos wrote:
>
>> Hi,
>>
>> We're upgrading a database from 8.4 to 9.4
>> The web developer complains that the timestamps are suddenly 2 hours
>> late. We are in GMT+02.
>> The issue would go away if we cast the postgres timestamps to timestamp
>> WITH timezone. It works in pg8.4 and 9.4
>>
>> He told me that PHP always uses timezones, so i tried to reproduce it
>> without the application layer.
>> Since PHP always uses a timezone, the first part of the query always
>> converts to "with time zone', it is what i presume PHP is doing.
>>
>
> That is the same as assuming and I would verify.
>
>
>> select timestamp with time zone 'epoch' + extract(epoch from
>> now()::timestamp) * interval '1 second'-now(),substr(version(), 12, 3)
>> --> 02:00:00 9.4
>> --> 00:00:00 8.4
>>
>> select timestamp with time zone 'epoch' + extract(epoch from
>> now()::timestamp WITH TIME ZONE) * interval '1 second' -
>> now(),substr(version(), 12, 3)
>> --> 00:00:00 9.4
>> --> 00:00:00 8.4
>>
>
> What does:
>
> show timezone;
>
> return?
>
>
>> Is there a reason for this change of behavior between 8.4 and 9.* ?
>>
>
> Have you looked at what TimeZone is set to in the 8.4 and 9.4
> postgresql.conf files?
>
> The method of setting that during initdb changed in 9.2:
>
> http://www.postgresql.org/docs/9.4/interactive/release-9-2.html
>
> E.29.3.1.7.1. postgresql.conf
>
> Identify the server time zone during initdb, and set postgresql.conf
> entries timezone and log_timezone accordingly (Tom Lane)
>
> This avoids expensive time zone probes during server start.
>
>>
>>
>> Cheers,
>> --
>> Willy-Bas Loos
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Willy-Bas Loos