Re: 8.3 vs HEAD difference in Interval output?

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> Tom Lane wrote:
>> We could try to do the same in the float case, but I'm a bit worried
>> about finding ourselves showing "1234567.799999" where it should be
>> "1234567.8".

> If I understand the code right fsec should mostly be values
> between -1 and 1 anyway, because even in the floating point
> case seconds are carried in the tm->tm_sec part.

The problem is that that's a decomposed representation. In the stored
form, there's a floating-point seconds field that includes hours,
minutes, seconds, and fractional seconds, and therefore large values
of the H/M/S fields degrade the accuracy of the fraction part.

Here's an example (testing in 8.3, since HEAD defaults to integer):

regression=# select '1234567890 hours 0.123 sec'::interval;
interval
-------------------------
1234567890:00:00.123047
(1 row)

Since there's a (somewhat arbitrary) limitation of the hours to 2^31,
this is close to the worst possible case. (Hm, maybe someone actually
did the math and decided that 2 fractional digits were the most they
could promise given that? No, because this code dates from a time
when we included days in the same field too ... back then there might
have been no accuracy at all in the fraction part.)

regards, tom lane