Re: BUG #6220: Flagstaff

Nicolas Beuzeboc wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6220
> Logged by:          Nicolas Beuzeboc
> Email address:      nicolasbeuzeboc@gmail.com
> PostgreSQL version: 8.4.8
> Operating system:   Ubuntu Natty
> Description:        Flagstaff
> Details:
>
> There is a month difference between the query results
>
> it's not happening if I take the 7.4999.. number and multiply it by interval
> '1 year'
>
> psql (8.4.8)
> Type "help" for help.
>
> postgres=# select now() - interval '7.5 years',  now()- interval
> '7.4999999999999999 year', now() - interval '7.49999999999999999 years';
>            ?column?            |           ?column?            |
> ?column?
> -------------------------------+-------------------------------+------------
> -------------------
>  2004-03-23 11:44:30.980895-07 | 2004-04-23 11:44:30.980895-07 | 2004-03-23
> 11:44:30.980895-07
> (1 row)

This problem is expected, and is clearer just looking at the intervals:

    test=> select interval '7.5 years';
        interval
    ----------------
     7 years 6 mons
    (1 row)

    test=> select interval '7.49999999999999999 years';
        interval
    ----------------
     7 years 6 mons
    (1 row)

    test=> select interval '7.4999999999999999 years';
        interval
    ----------------
     7 years 5 mons
    (1 row)

Intervals do not have a fixed time frame so we have to store the
interval in three parts:

    typedef struct
    {
        TimeOffset  time;           /* all time units other than days, months and
                                     * years */
        int32       day;            /* days, after time for alignment */
        int32       month;          /* months and years, after time for alignment */
    } Interval;

so we can't adjust the day as the month becomes fractional.  The example
you supply is basically just rounding from one month down to another.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +