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. +