Обсуждение: epoch and timezone changed bevior
Hi,
We're upgrading a database from 8.4 to 9.4The 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 8.4
Is there a reason for this change of behavior between 8.4 and 9.* ?
Cheers,
--
Willy-Bas Loos
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
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@aklaver.com
=# show timezone;
TimeZone
-----------
localtime
(1 row)
TimeZone
-----------
localtime
(1 row)
On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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@aklaver.com
--
Willy-Bas Loos
On Thu, Sep 24, 2015 at 4:01 PM, Willy-Bas Loos <willybas@gmail.com> wrote:
-- =# show timezone;
TimeZone
-----------
localtime
(1 row)
sorry for the top post
Willy-Bas Loos
On 09/24/2015 07:01 AM, Willy-Bas Loos wrote: > =# show timezone; > TimeZone > ----------- > localtime > (1 row) > Is this the same on both 8.4 and 9.4? Are both servers on the same machine? What does /etc/localtime point to? > > On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > Willy-Bas Loos -- Adrian Klaver adrian.klaver@aklaver.com
On 09/24/2015 07:01 AM, Willy-Bas Loos wrote: > =# show timezone; > TimeZone > ----------- > localtime > (1 row) > > This sounded familiar: http://www.postgresql.org/message-id/m3616t3m5d.fsf@carbon.jhcloos.org From there, per Tom Lane: select * from pg_settings where name = 'TimeZone'; This will show what is actually supplying the timezone value from the Postgres side. Might be easiest to just set timezone in postgresql.conf to what you want it to be. -- Adrian Klaver adrian.klaver@aklaver.com
Willy-Bas Loos <willybas@gmail.com> writes: > Is there a reason for this change of behavior between 8.4 and 9.* ? See the "incompatibilities" section in the 9.2 release notes: * Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch from local midnight, not UTC midnight (Tom Lane) This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the timezone setting, which computations for timestamp without time zone should not be. The previous behavior remains available by casting the input value to timestamp with time zone. regards, tom lane
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 09/24/2015 07:01 AM, Willy-Bas Loos wrote: >> =# show timezone; >> TimeZone >> ----------- >> localtime >> (1 row) > This sounded familiar: > http://www.postgresql.org/message-id/m3616t3m5d.fsf@carbon.jhcloos.org Yeah ... we never did figure out what was producing that setting on Cloos' machine. But it's not relevant to the specific problem being complained of here. regards, tom lane
On 09/24/2015 08:08 AM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 09/24/2015 07:01 AM, Willy-Bas Loos wrote: >>> =# show timezone; >>> TimeZone >>> ----------- >>> localtime >>> (1 row) > >> This sounded familiar: >> http://www.postgresql.org/message-id/m3616t3m5d.fsf@carbon.jhcloos.org > > Yeah ... we never did figure out what was producing that setting on > Cloos' machine. But it's not relevant to the specific problem being > complained of here. Yeah, I forgot about the EXTRACT change. > > regards, tom lane > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Sep 24, 2015 at 5:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Yeah, I forgot about the EXTRACT change.
regards, tom lane
thanks a lot for clarifying!
--
Willy-Bas Loos