Re: Floating-point timestamps versus Range Types

Andrew Dunstan <andrew@dunslane.net> writes:
> On 10/17/2010 04:40 PM, Tom Lane wrote:
>> ... That's assuming that we think there are
>> no users who are depending on float timestamps for functionality (they
>> have a wider range than int timestamps don't they?).

> Yes, they do.

> Maybe we need to look at providing a bigtimestamp type or similar at 
> some stage. Or maybe the demand for it would be so low it should be an 
> add-on module.

[ rechecks the code & docs ... ]  In principle float timestamps could
have a ridiculously wide range, on the order of 140 million years if
you assume that 1-second precision is sufficient.  In practice they are
constrained by our use of nonnegative 32-bit integers for Julian Day
counts, which restricts the range to be from 4713 BC to 5 million years
and change AD.  64-bit-int timestamps have a theoretical range of about
plus or minus 300 thousand years, which again is restricted on the BC
side by the Julian Day code.  We could push out the 5M AD limit by
converting the JD code to 64-bit ints, but it's not clear there's any
interest in that given that it won't do a thing for the integer
timestamp case (and I'm not sure if the equations are really correct
so far out, anyway).

So the bottom line question is whether somebody has a use for Gregorian
calendar dates between 300K AD and 5M AD, while not needing to go back
before 4K BC.  I should think that the BC-side limit pretty much renders
this datatype pointless for astronomers and geologists, even if they
wanted to count in Gregorian dates; and I can't think of any other
communities that are going to care much about dates that far out.
So, if there's a use-case at all, it's not interesting enough to include
in core.

IOW I don't think the range argument holds much water for keeping float
timestamps alive.  The on-disk-compatibility argument does, though.
        regards, tom lane