Re: BUG #2768: dates before year 1600 in timestamptz column

On Mon, 20 Nov 2006, Tom Lane wrote:

> "Mikko Tiihonen" <mikko(dot)tiihonen(at)iki(dot)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