Search Postgresql Archives

Re: epoch and timezone changed bevior

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



=# show timezone;
 TimeZone 
-----------
 localtime
(1 row)


On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver <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



--
Willy-Bas Loos

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux