Re: [BUGS] BUG #3431: age() gets the days wrong - Mailing list pgsql-hackers

I don't see this as applied yet.

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

Tom Lane wrote:
> "Pelle Johansson" <pelle@morth.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@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +