Re: BUG #15696: year field of interval type is not rounded to nearest integer

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: 110876189(at)qq(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15696: year field of interval type is not rounded to nearest integer
Date: 2019-03-16 14:46:42
Message-ID: 14595.1552747602@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> all fields of interval type use rule of rounding to nearest integer(by
> rint() function), except for year field.

I'm not really convinced this is wrong, or at least that year is
particularly inconsistent with anything else. Yes, "0.9999... year"
is never rounded up to 1 year, but neither is 0.9999... month rounded
to 1 mon:

regression=# select '0.9999999999999999 mon'::interval;
interval
------------------
29 days 24:00:00
(1 row)

nor 0.9999... day rounded to 1 day:

regression=# select '0.9999999999999999 day'::interval;
interval
----------
24:00:00
(1 row)

In all these cases the fraction is only allowed to propagate to
lower-order fields of the interval result.

A possibly more interesting point is that 0.9999... year only
affects the months field and not any lower fields; by analogy to
the 0.9999... month case, you might expect to get "11 mons 30 days"
or perhaps "11 mons 29 days 24:00:00", but you don't.

Probably more interesting for practical use is that today you get

regression=# select '0.3 year'::interval;
interval
----------
3 mons
(1 row)

but (if I did the math correctly) that would change to "4 mons"
if we rounded where you suggest doing so. I don't find that
terribly defensible --- in a green field, it wouldn't have mattered
which we picked perhaps, but I doubt we can get away with changing
that behavior now.

We could imagine some other universe where all this is done differently,
but why exactly is that so much better that we should take risks of
backwards-compatibility complaints? None of this seems like an
expected use-case for intervals. The way you really ought to do
scaling-type calculations is more like

regression=# select 0.3 * '1 year'::interval;
?column?
----------------
3 mons 18 days
(1 row)

regression=# select 0.33 * '1 year'::interval;
?column?
-------------------------
3 mons 28 days 19:12:00
(1 row)

regression=# select 0.9999999999999999 * '1 year'::interval;
?column?
-----------------
11 mons 30 days
(1 row)

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-03-16 20:11:19 BUG #15698: to_char doesn't return expected value with negative INTERVAL
Previous Message Tom Lane 2019-03-16 14:14:09 Re: BUG #15697: Restarting the server because of the error checkpointer