epoch and timezone changed bevior

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: epoch and timezone changed bevior
Date: 2015-09-24 13:42:47
Message-ID: CAHnozTj2Ho+AToNGibPicnXF-g_czvOP8Mznve4jLuzAMTfQXw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.* ?

Cheers,
--
Willy-Bas Loos

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2015-09-24 13:48:22 Re: Convert number to string
Previous Message Yves Dorfsman 2015-09-24 13:29:01 Re: Use tar to online backup has an error