Postgres Pro
Downloads
Home   >   mailing lists

Thread: Time bug with small years

Time bug with small years

From
Rod Taylor
Date:
I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto
thetimezone component?<br /><br /><br />sk_test=# select version();<br
/>                                                   version                                                     <br />
----------------------------------------------------------------------------------------------------------------<br
/> PostgreSQL9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit<br />
(1row)<br />-- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64
x86_64x86_64 GNU/Linux<br /><br /><br />sk_test=# select '1894-01-01'::timestamp with time zone;<br />        
timestamptz         <br /> ------------------------------<br /> 1894-01-01 00:00:00-05:17:32<br />(1 row)<br /><br
/>sk_test=#select '1895-01-01'::timestamp with time zone;<br />      timestamptz       <br
/>------------------------<br/> 1895-01-01 00:17:32-05<br /> (1 row)<br /><br />sk_test=# select
'1896-01-01'::timestampwith time zone;<br />      timestamptz       <br />------------------------<br /> 1896-01-01
00:00:00-05<br/>(1 row)<br /><br />sk_test=# show timezone;<br /> TimeZone  <br /> -----------<br /> localtime<br />(1
row)<br/><br />sk_test=# set timezone= 'est5edt';<br />SET<br />sk_test=# select '1895-01-01'::timestamp with time
zone;<br/>      timestamptz       <br />------------------------<br />  1895-01-01 00:00:00-05<br />(1 row)<br /><br
/>sk_test=#select '1894-01-01'::timestamp with time zone;<br />      timestamptz       <br
/>------------------------<br/> 1894-01-01 00:00:00-05<br />(1 row)<br /><br /><br /><br />I can duplicate with the
exactsame version of Pg on Intel hardware with kernel:<br /><br />Linux infongd2888
2.6.28.8-20101130b-iscsi-ntacker-fasync-mremap-amd-sec6-grsec#1 SMP Tue Nov 30 18:27:29 CET 2010 i686 GNU/Linux<br
/><br/>

Re: Time bug with small years

From
Richard Huxton
Date:
On 24/11/11 04:45, Rod Taylor wrote:
> I have no idea what is going on with the minutes/seconds, particularly for
> years under 1895 where it gets appended onto the timezone component?
>
>
> sk_test=# select version();
>
> version
> ----------------------------------------------------------------------------------------------------------------
>   PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
> (1 row)
> -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov
> 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux
>
>
> sk_test=# select '1894-01-01'::timestamp with time zone;
>           timestamptz
> ------------------------------
>   1894-01-01 00:00:00-05:17:32

Floating-point timestamps? Although I thought integer was the default 
for 9.x - hmm INSTALL says since 8.4

--   Richard Huxton  Archonet Ltd

Re: Time bug with small years

From
Robert Haas
Date:
On Wed, Nov 23, 2011 at 11:45 PM, Rod Taylor <pg@rbt.ca> wrote:
> sk_test=# select '1894-01-01'::timestamp with time zone;
>          timestamptz
> ------------------------------
>  1894-01-01 00:00:00-05:17:32
> (1 row)

I believe that -05:17:32 is the offset of your local time zone as
compared with UTC.  For example:

rhaas=# select now();            now
------------------------------2011-11-24 13:46:46.68016+00
(1 row)

rhaas=# set time zone 'Australia/Eucla';
SET
rhaas=# select now();              now
----------------------------------2011-11-24 22:31:55.792565+08:45
(1 row)

rhaas=# set time zone 'UTC';
SET
rhaas=# select now();             now
-------------------------------2011-11-24 13:46:58.480484+00
(1 row)

On my system, all current time zone offsets are multiples of 15
minutes, but historically that wasn't the case.  It seems that in your
local time zone, the offset versus UTC was, as of January 1, 1894,
minus five hours, seventeen minutes, and 32 seconds.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Time bug with small years

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Nov 23, 2011 at 11:45 PM, Rod Taylor <pg@rbt.ca> wrote:
>> sk_test=# select '1894-01-01'::timestamp with time zone;
>>          timestamptz
>> ------------------------------
>>  1894-01-01 00:00:00-05:17:32
>> (1 row)

> On my system, all current time zone offsets are multiples of 15
> minutes, but historically that wasn't the case.  It seems that in your
> local time zone, the offset versus UTC was, as of January 1, 1894,
> minus five hours, seventeen minutes, and 32 seconds.

And in fact, a quick grep through the timezone database shows that
you must be using America/Toronto:

# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone America/Toronto    -5:17:32 -    LMT    1895        -5:00    Canada    E%sT    1919        -5:00    Toronto
E%sT   1942 Feb  9 2:00s        -5:00    Canada    E%sT    1946        -5:00    Toronto    E%sT    1974        -5:00
Canada   E%sT
 

The general practice in the Olson database is to show localities as
using local mean time until the year in which they adopted some form of
standardized time.  So local midnight of 1894-01-01 is in fact at an
unusual (for us) offset from UTC.
        regards, tom lane