epoch and timezone changed bevior

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

=# show timezone;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/24/2015 07:01 AM, Willy-Bas Loos wrote:

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

This sounded familiar:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:
/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:
/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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