Skip site navigation (1) Skip section navigation (2)

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

From: Mikko Tiihonen <mikko(dot)tiihonen(at)iki(dot)fi>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2768: dates before year 1600 in timestamptz column
Date: 2006-11-21 06:33:18
Message-ID: Pine.OSF.4.64.0611210812040.272415@kosh.hut.fi (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-11-21 06:46:09
Subject: Re: BUG #2768: dates before year 1600 in timestamptz column give strange results
Previous:From: Steffen MackeDate: 2006-11-21 04:17:03
Subject: Re: BUG #2767: Bug in avg(interval)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group