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

Re: fixes for date_part micro/millisecond precision

From: Brent Verner <brent(at)rcfile(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: fixes for date_part micro/millisecond precision
Date: 2001-11-24 21:12:00
Message-ID: 20011124161200.A8640@rcfile.org (view raw or flat)
Thread:
Lists: pgsql-patches
On 24 Nov 2001 at 15:32 (-0500), Tom Lane wrote:
| Brent Verner <brent(at)rcfile(dot)org> writes:
| > ... if I insert a timestamp of
| > '2001-1-1 11:11:11.12341234-05' I /really/ want to get back '12341234'
| > when  I ask for the microseconds that I stored,
| 
| You cannot expect to get that back exactly, because *the precision is
| not there* (and yes, the term here is precision not accuracy).  Right
| now (late 2001) we have about seven digits of precision to the right
| of the decimal point in a timestamp.  As we get further away from the
| 2000-01-01 origin, precision will drop; it'll be six digits or less
| by 2010.  The further you go from 2000 in either direction, the worse
| the precision.

ah, I see. I wasn't thinking about the limited range that could be
accurately stored in the double... :-(

| This is an inherent property of float arithmetic and can't be papered
| over with display hacks, at least not without losing more than you gain.
| 
| > Is there a better place to 'force' the accuracy of the fractional
| > second part that would be less prone to introduce other problems?
| 
| You cannot force accuracy that isn't there.

thanks, and sorry for the hassle.

wandering-off-to-try-making-Timestamp-a-struct-ly yours,
  brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

In response to

pgsql-patches by date

Next:From: Bruce MomjianDate: 2001-11-24 21:21:57
Subject: Re: Support for QNX6, POSIX IPC and PTHREAD-style locking
Previous:From: Tom LaneDate: 2001-11-24 20:32:27
Subject: Re: fixes for date_part micro/millisecond precision

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