Thread: 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