BUG #6220: Flagstaff

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)

postgres=# select version();
version

----------------------------------------------------------------------------
----------------------------------------
PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real
(Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit
(1 row)

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