On 09/24/2015 07:01 AM, Willy-Bas Loos wrote:
=# show timezone; TimeZone ----------- localtime (1 row)
Is this the same on both 8.4 and 9.4? Are both servers on the same machine? What does /etc/localtime point to?
On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> -- Willy-Bas Loos
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
