Re: BUG #6220: Flagstaff

Nicolas Beuzeboc wrote:
>
> The following bug has been logged online:
>
> Bug reference: 6220
> Logged by: Nicolas Beuzeboc
> Email address: nicolasbeuzeboc(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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