Re: epoch and timezone changed bevior

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