Timestamp precision

From: Stéphane Schildknecht <stephane(dot)schildknecht(at)postgresqlfr(dot)org>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Timestamp precision
Date: 2007-03-29 06:31:56
Message-ID: 460B5D5C.8090304@postgresqlfr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm reading date/time datatypes documentation, and I'm a little bit
surprised by this piece of documentation :

Note: When timestamp values are stored as double precision
floating-point numbers (currently the default), the effective limit of
precision may be less than 6. timestamp values are stored as seconds
before or after midnight 2000-01-01. Microsecond precision is achieved
for dates within a few years of 2000-01-01, but the precision degrades
for dates further away. When timestamp values are stored as eight-byte
integers (a compile-time option), microsecond precision is available
over the full range of values. However eight-byte integer timestamps
have a more limited range of dates than shown above: from 4713 BC up to
294276 AD. (...)

In fact, I wonder why a date ranging from somme 4000 BC to 30000 AC is
stored as a reference to the 1st january of 2000. Is it because that day
is some "close to actual time" date ?

And so, what do you mean by "within a few years"? Is it in reference to
geological time (200 years on 300000 is less than one on a thousand) or
to human life?

I still wonder who could want to store a date 100 years ago with a
microsecond precision ;-)

Best regards,
--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.PostgreSQLFr.org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dmitry Koterov 2007-03-29 07:06:46 Re: How to speedup CHECKPOINTs?
Previous Message Brent Wood 2007-03-29 03:56:01 Re: inserting 4800 records at a time