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.klaver@aklaver.com
