Re: BUG #3431: age() gets the days wrong

I don't see this as applied yet.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Pelle Johansson" <pelle(at)morth(dot)org> writes:
> > The age() function seem to work by first counting months until less than a
> > month remains to to the second argument, then counting days left. This
> > doesn't give the correct result, as shown by this example:
>
> > # select column1, age(column1, '2006-11-02'), date '2006-11-02' +
> > age(column1, '2006-11-02') from (values ('2007-01-31'::date),
> > ('2007-02-01')) as alias;
> > column1 | age | ?column?
> > ------------+----------------+---------------------
> > 2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
> > 2007-02-01 | 2 mons 29 days | 2007-01-31 00:00:00
> > (2 rows)
>
> I took another look at this example. I believe what is actually going
> wrong here is that when timestamp_age converts a month into an
> equivalent number of days, it uses the number of days in the first
> month of the interval it's dealing with (ie, the month containing
> the earlier of the two dates). This is just wrong, because interval
> addition adds months first and then days. The appropriate conversion
> to use is actually the length of the next-to-last month of the interval.
>
> As an example, 8.2 and CVS HEAD produce
>
> regression=# select age('2007-03-14', '2007-02-15');
> age
> ---------
> 27 days
> (1 row)
>
> which is reasonable, but
>
> regression=# select age('2007-04-14', '2007-02-15');
> age
> ---------------
> 1 mon 27 days
> (1 row)
>
> is not so reasonable, nor is
>
> regression=# select age('2007-03-14', '2007-01-15');
> age
> ---------------
> 1 mon 30 days
> (1 row)
>
> If we change the code to use the next-to-last month of the interval
> then these two cases produce '1 mon 30 days' and '1 mon 27 days'
> respectively.
>
> Another problem is that the code isn't doing the propagate-to-next-field
> bit for negative fractional seconds. Hence it produces
>
> regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:00.4');
> age
> ----------------------
> 30 days -00:00:00.40
> (1 row)
>
> which is maybe not incorrect, but certainly fairly inconsistent with
>
> regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:01');
> age
> ------------------
> 29 days 23:59:59
> (1 row)
>
>
> Hence I propose the attached patch. This does not change any existing
> regression test outputs, but it does change the example given in the
> documentation: age(timestamp '2001-04-10', timestamp '1957-06-13')
> will now produce '43 years 9 mons 28 days' not 27 days. Which actually
> is correct if you try to add back the result to timestamp '1957-06-13'.
> It also appears to fix Palle's example:
>
> regression=# select column1, age(column1, '2006-11-02'), date '2006-11-02' +
> age(column1, '2006-11-02') from (values ('2007-01-31'::date),
> ('2007-02-01')) as alias;
> column1 | age | ?column?
> ------------+----------------+---------------------
> 2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
> 2007-02-01 | 2 mons 30 days | 2007-02-01 00:00:00
> (2 rows)
>
> As I said earlier, I'm worried about changing the behavior of a function
> that's been around for so long, so I'm disinclined to back-patch this.
> But it seems like a reasonable change to make in 8.3. Comments?
>
> regards, tom lane
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +