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

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 (view raw or flat)
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

pgsql-general by date

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

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