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

The following bug has been logged on the website:

Bug reference:      15696
Logged by:          zhou xiaowei
Email address:      110876189@qq.com
PostgreSQL version: 11.2
Operating system:   Linux x86_64
Description:

all fields of interval type use rule of rounding to nearest integer(by
rint() function), except for year field.
details:
postgres=# select '0.9999999999999999 year'::interval month;
 interval
----------
 11 mons
(1 row)

postgres=# select '0.9999999999999999 dec'::interval year;
 interval
----------
 9 years
(1 row)

postgres=# select '0.9999999999999999 cent'::interval year;
 interval
----------
 99 years
(1 row)

postgres=# select '0.9999999999999999 mil'::interval year;
 interval
-----------
 999 years
(1 row)

the reason is that assign double value to int value in function
DecodeInterval() :
int DecodeInterval(char **field, int *ftype, int nf, int range,
               int *dtype, struct pg_tm *tm, fsec_t *fsec)
{
    double        fval;
    .......
        switch (ftype[i])
                    case DTK_YEAR:
                        tm->tm_year += val;
                        if (fval != 0)
                            tm->tm_mon += fval * MONTHS_PER_YEAR;
                        tmask = DTK_M(YEAR);
                        break;
                    case DTK_DECADE:
                        tm->tm_year += val * 10;
                        if (fval != 0)
                            tm->tm_mon += fval * MONTHS_PER_YEAR * 10;
                        tmask = DTK_M(DECADE);
                        break;

                    case DTK_CENTURY:
                        tm->tm_year += val * 100;
                        if (fval != 0)
                            tm->tm_mon += fval * MONTHS_PER_YEAR * 100;
                        tmask = DTK_M(CENTURY);
                        break;

                    case DTK_MILLENNIUM:
                        tm->tm_year += val * 1000;
                        if (fval != 0)
                            tm->tm_mon += fval * MONTHS_PER_YEAR * 1000;
                        tmask = DTK_M(MILLENNIUM);
                        break;
    ......
}
The code 'tm->tm_mon' is int type.I think this is a bug，need use rint() to
adjust it’s result，like:
tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 1000);

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

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.


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