Thread: 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

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

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