From: | Vik Fearing <vik(at)2ndquadrant(dot)fr> |
---|---|
To: | Willy-Bas Loos <willybas(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: epoch and timezone changed bevior |
Date: | 2015-09-24 13:53:21 |
Message-ID: | 56040051.9060607@2ndquadrant.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-09-24 13:57:37 | Re: epoch and timezone changed bevior |
Previous Message | Francisco Olarte | 2015-09-24 13:48:22 | Re: Convert number to string |