Обсуждение: Timestamp precision

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

=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
> 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 ?

The restriction to 4713BC comes from the Julian-date calendar
manipulation algorithms, not from the possible range of the underlying
integer or float.

> And so, what do you mean by "within a few years"?

It means we didn't bother to write out the math.  If you know the
precision of float8 on your hardware (2^-53 for IEEE) you can work
it out for yourself...

            regards, tom lane

> 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.

I think this variance in precision is actually kind of cool - any
thought to allowing the "center" of the range to be a build-time
option?  That is, any particular application might want more
precision at a custom center.  Does this involve changing more than
that one constant?

Hmm, except if the timestamp "anchor" is installation-specific, then
binary exchange of timestamps is complicated.  What does libpq do now
with timetamps, if the client requests data in binary form?  How does
the client know whether it's getting floats or integers?

- John D. Burger
   MITRE

"John D. Burger" <john@mitre.org> writes:
> Hmm, except if the timestamp "anchor" is installation-specific, then
> binary exchange of timestamps is complicated.

Yeah, that would be a problem.

> What does libpq do now
> with timetamps, if the client requests data in binary form?  How does
> the client know whether it's getting floats or integers?

You have to look at the integer_datetimes parameter (which is available
to the client automatically in recent versions, though whether you can
get at it easily depends on your client library...)

            regards, tom lane