Thread: BUG #2768: dates before year 1600 in timestamptz column give strange results

The following bug has been logged online:

Bug reference:      2768
Logged by:          Mikko Tiihonen
Email address:      mikko.tiihonen@iki.fi
PostgreSQL version: 8.1.5
Operating system:   Linux/amd64/gcc-4.1.1
Description:        dates before year 1600 in timestamptz column give
strange results
Details:

createuser -s -d test
createdb -E LATIN9 -O test test
psql -U test
---
CREATE TEMP TABLE tester (stamp timestamp(6) with time zone);
INSERT INTO tester (stamp) VALUES ( '0134-05-06 09:12:34.123456 +0200' );
INSERT INTO tester (stamp) VALUES ( '2134-05-06 09:12:34.123456 +0200' );
SELECT * FROM tester;
---
PostgreSQL compiled with intdatetime=on
              stamp
----------------------------------
 0134-05-06 08:52:26.123456+01:39
 2134-05-06 09:12:34.123456+02
(2 rows)

PostgreSQL compiled with intdatetime=off
              stamp
----------------------------------
 0134-05-06 08:52:26.123459+01:39
 2134-05-06 09:12:34.123456+02
(2 rows)


If I fetch the rows using v3 protocol with and binary encoding for the
timestamp field I get back has the correct time. If I fetch the rows using
text encoding I get the same erronous value as with psql.

That makes me think the problem is in converting the internal timestamptz
format to formatted text.

"Mikko Tiihonen" <mikko.tiihonen@iki.fi> writes:
> PostgreSQL compiled with intdatetime=off
>               stamp
> ----------------------------------
>  0134-05-06 08:52:26.123459+01:39
>  2134-05-06 09:12:34.123456+02
> (2 rows)

[ shrug... ]  Floating-point timestamps are not perfectly accurate; the
further away you go from 2000-01-01, the less so.  The weird offset from
GMT is probably a function of your local timezone, which you didn't
mention.

            regards, tom lane

Heikki Linnakangas wrote:
> Tom Lane wrote:
>>  The weird offset from GMT is probably a function of your local
>> timezone, which you didn't
>> mention.
>
> Most likely "Europe/Helsinki". I can reproduce the problem with that:

Confirmed that it's expected behavior. The timezone really was
GMT+01:39:52 until  May 1st, 1921. See

http://igs.kirjastot.fi/iGS/kysymykset/haku.aspx?word=Kes%C3%A4aika

and search for "1921" for details (in Finnish).

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Tom Lane wrote:
> "Mikko Tiihonen" <mikko.tiihonen@iki.fi> writes:
>> PostgreSQL compiled with intdatetime=off
>>               stamp
>> ----------------------------------
>>  0134-05-06 08:52:26.123459+01:39
>>  2134-05-06 09:12:34.123456+02
>> (2 rows)
>
> [ shrug... ]  Floating-point timestamps are not perfectly accurate; the
> further away you go from 2000-01-01, the less so.

Agreed.

>  The weird offset from GMT is probably a function of your local timezone, which you didn't
> mention.

Most likely "Europe/Helsinki". I can reproduce the problem with that:

postgres=# set time zone 'Europe/Helsinki';
SET
postgres=# SELECT * FROM tester;
                 stamp
-------------------------------------
  0134-05-06 08:52:26.123459+01:39:52
  2134-05-06 09:12:34.123456+02
(2 rows)

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Mikko Tiihonen <mikko.tiihonen@iki.fi> writes:
> On Mon, 20 Nov 2006, Tom Lane wrote:
>> ... The weird offset from GMT is probably a function of your local
>> timezone, which you didn't mention.

> My database in configured to timezone Europe/Helsinki aka +0200.

The zic database says that Helsinki kept local mean solar time before
1921:

# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone    Europe/Helsinki    1:39:52 -    LMT    1878 May 31
            1:39:52    -    HMT    1921 May    # Helsinki Mean Time
            2:00    Finland    EE%sT    1981 Mar 29 2:00
            2:00    EU    EE%sT

Of course, back in the 1600's they probably didn't keep time as
accurately as all that, but feel free to change your copy of that
configuration file if you want a different answer.  I'd be willing
to bet that around 1900, the 1:39 offset was indeed correct.

FWIW, pre-8.2 Postgres does have some issues with displaying
fractional-minute GMT offsets.  PG 8.1.5:

regression=# set timezone = 'Europe/Helsinki';
SET
regression=# select '1600-01-01'::timestamptz;
        timestamptz
---------------------------
 1600-01-01 00:00:00+01:39
(1 row)

CVS HEAD gets it right:

regression=# set timezone = 'Europe/Helsinki';
SET
regression=# select '1600-01-01'::timestamptz;
         timestamptz
------------------------------
 1600-01-01 00:00:00+01:39:52
(1 row)


            regards, tom lane

On Mon, 20 Nov 2006, Tom Lane wrote:

> "Mikko Tiihonen" <mikko.tiihonen@iki.fi> writes:
> > PostgreSQL compiled with intdatetime=off
> >               stamp
> > ----------------------------------
> >  0134-05-06 08:52:26.123459+01:39
> >  2134-05-06 09:12:34.123456+02
> > (2 rows)
>
> [ shrug... ]  Floating-point timestamps are not perfectly accurate; the
> further away you go from 2000-01-01, the less so.  The weird offset from
> GMT is probably a function of your local timezone, which you didn't
> mention.

My database in configured to timezone Europe/Helsinki aka +0200. I knew that
the floating point stamps are not fully accurate, but I showed the result with
that setting too, just to prove that the internal storing/handling format did
not matter in this bug.

Where can I find out what function is used for my timezone ? And even if there
would be different timezone calculation formulas for different timezones I do
not believe that the date of the timestamp should be a parameter that affects
the result.

Further notice that the insert statements included a fully qualified timestamp
including the timezone so that the database local timezone won't affect the
inserted value. On the other hand I do think that the values printed by select
can be affected by the database timezone.

I would very much like someone to try the small insert/select statements in my
bug report and to verify if the bug is in my setup or if it is a real bug.

My current quess is that when doing the math of converting from gregorian
calendar system to julian (or vise versa) postgresql forgets to clear the time
component from the timestamp before adjusting the date, resulting in the time
shifted with some strange constant.

-Mikko

On Tue, 21 Nov 2006, Heikki Linnakangas wrote:

> Heikki Linnakangas wrote:
> > Tom Lane wrote:
> > > The weird offset from GMT is probably a function of your local timezone,
> > > which you didn't
> > > mention.
> >
> > Most likely "Europe/Helsinki". I can reproduce the problem with that:
>
> Confirmed that it's expected behavior. The timezone really was GMT+01:39:52
> until  May 1st, 1921. See
>
> http://igs.kirjastot.fi/iGS/kysymykset/haku.aspx?word=Kes%C3%A4aika
>
> and search for "1921" for details (in Finnish).


Ok. Thank you for clearing this up.

I'm sorry to have bothered you with the issue. I'll go back to wondering why
the java jdbc postgresql driver is 3x slower when it sees the odd timezone in
the response.

-Mikko