epoch and timezone changed bevior

[Willy-Bas Loos <willybas@xxxxxxxxx>]

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