Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Pelle Johansson <pelle(at)morth(dot)org>, pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: BUG #3431: age() gets the days wrong
Date: 2007-07-18 00:40:36
Message-ID: 200707180040.l6I0eab04981@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackerspgsql-patches
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. +

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-07-18 00:41:12
Subject: Re: BUG #3431: age() gets the days wrong
Previous:From: Bruce MomjianDate: 2007-07-18 00:31:21
Subject: Re: Should we bump libpq major version for 8.3?

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2007-07-18 00:41:12
Subject: Re: BUG #3431: age() gets the days wrong
Previous:From: Tom LaneDate: 2007-07-17 20:33:22
Subject: Re: BUG #3459: Query Error : plan should not reference subplan's variable

pgsql-patches by date

Next:From: Bruce MomjianDate: 2007-07-18 00:41:12
Subject: Re: BUG #3431: age() gets the days wrong
Previous:From: Andrew DunstanDate: 2007-07-18 00:19:44
Subject: Re: [HACKERS] msvc, build and install with cygwin in the PATH

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group