Re: [GENERAL] epoch and timezone changed bevior

[Willy-Bas Loos]

On Thu, Sep 24, 2015 at 5:22 PM, Adrian Klaver 
wrote:


> Yeah, I forgot about the EXTRACT change.
>
>
>> regards, tom lane
>>
>>
>

thanks a lot for clarifying!

-- 
Willy-Bas Loos

Re: [GENERAL] epoch and timezone changed bevior

[Adrian Klaver]

On 09/24/2015 08:08 AM, Tom Lane wrote:

Adrian Klaver  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@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.kla...@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

Re: [GENERAL] epoch and timezone changed bevior

[Tom Lane]

Adrian Klaver  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@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

Re: [GENERAL] epoch and timezone changed bevior

[Tom Lane]

Willy-Bas Loos  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

Re: [GENERAL] epoch and timezone changed bevior

[Adrian Klaver]

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@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.kla...@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

Re: [GENERAL] epoch and timezone changed bevior

[Adrian Klaver]

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
mailto:adrian.kla...@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:009.4
--> 00:00:008.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:009.4
--> 00:00:008.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.kla...@aklaver.com 




--
Willy-Bas Loos



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] epoch and timezone changed bevior

[Willy-Bas Loos]

On Thu, Sep 24, 2015 at 4:01 PM, Willy-Bas Loos  wrote:

> =# show timezone;
>  TimeZone
> ---
>  localtime
> (1 row)
>
>
>
sorry for the top post
-- 
Willy-Bas Loos

Re: [GENERAL] epoch and timezone changed bevior

[Willy-Bas Loos]

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


On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver 
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:009.4
>> --> 00:00:008.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:009.4
>> --> 00:00:008.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.kla...@aklaver.com
>



-- 
Willy-Bas Loos

Re: [GENERAL] epoch and timezone changed bevior

[Adrian Klaver]

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:009.4
--> 00:00:008.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:009.4
--> 00:00:008.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.kla...@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

Re: [GENERAL] epoch and timezone changed bevior

[Vik Fearing]

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:009.4
> --> 00:00:008.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:009.4
> --> 00:00:008.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