Re: Fix for timestamp rouding - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: Fix for timestamp rouding
Date
Msg-id 200505270411.j4R4Bhm22080@candle.pha.pa.us
Whole thread Raw
In response to Re: Fix for timestamp rouding  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgsql-patches by date:

Previous
From: Tom Lane
Date:
Subject: Re: psql backslash consistency
Next
From: Peter Eisentraut
Date:
Subject: Re: psql backslash consistency