sorry,I lose the expected result. Such as "select '0.9999999999999999 year'::interval month;" expected result is "12 mons",not "11 mons".I think this rule of rounding to nearest int like oracle function numtoyminterval(),maybe a more better idea.
---Original---
From: "Tom Lane"<tgl@sss.pgh.pa.us>
Date: Sat, Mar 16, 2019 22:46 PM
To: "110876189"<110876189@qq.com>;
Cc: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
Subject: Re: BUG #15696: year field of interval type is not rounded to nearest integer
PG Bug reporting form <noreply@postgresql.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
