On 09/24/2015 03:42 PM, 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. > > 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 > > Is there a reason for this change of behavior between 8.4 and 9.* ? Yes. As of 9.2, the server's timezone is set when the database is initialized. See the following commit message: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ca4af308c32d03db5fbacb54d6e583ceb904f268 -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
