Re: Fix for timestamp rouding

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, I have a new patch, which simplifies the code by using
> > TrimTrailingZeros(), gives more consistent subsecond display, and
> > subpresses the rounding problem:
>
> Does anyone have any idea why the existing code is designed to keep the
> number of displayed fractional digits even? It seems a bit silly to
> me too, but we probably ought not remove what was clearly an intentional
> behavior without understanding why it was intentional.

If you look at TrimTrailingZeros(), you will see it keeps at least two
decimal digits of display, so I suppose the special case while() loop
was just an older version of that. I can't imagine why they would only
want even digits, but given the other weird things in the datetime code,
it isn't surprising.

> Also, I will point out once more that the problem is not "we only have
> nine digits of precision not ten". The problem is that the precision
> degrades as the interval gets larger.
>
> regression=# select '20 days 15 hours 57 mins 12.1 secs ago'::interval;
> interval
> -------------------------------
> -20 days -15:57:12.1000000001
> (1 row)
>
> regression=# select '100020 days 15 hours 57 mins 12.1 secs ago'::interval;
> interval
> -----------------------------------
> -100020 days -15:57:12.1000003815
> (1 row)
>
> regression=# select '100000020 days 15 hours 57 mins 12.1 secs ago'::interval;
> interval
> -------------------------------------
> -100000020 days -15:57:12.099609375
> (1 row)
>
> regression=#
>
> Without accounting for that fundamental fact, you will not have a
> solution, only a kluge.

Yep, it just hits the most common complaint for <30 days --- larger
values are going to round funny. I can't think of how to round it
cleanly without adding a lot of hacky code dealing with floats and
exponents.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073