Обсуждение: BUG #5728: Unexpected behavior comparing result of age() to an interval

The following bug has been logged online:

Bug reference:      5728
Logged by:          Dobes Vandermeer
Email address:      dobes.vandermeer@kashoo.com
PostgreSQL version: 8.4
Operating system:   Windows
Description:        Unexpected behavior comparing result of age() to an
interval
Details:

Seeing some surprising behavior with the use of age() and comparing the
result to an interval:

select current_date,
       age(current_date - interval '123 days') <= interval '120 days',
       age(current_date - interval '122 days') <= interval '120 days',
       age(current_date - interval '121 days') <= interval '120 days',
       age(current_date - interval '120 days') <= interval '120 days',
       age(current_date - interval '62 days') <= interval '60 days',
       age(current_date - interval '61 days') <= interval '60 days',
       age(current_date - interval '60 days') <= interval '60 days',
       age(current_date - interval '30 days') <= interval '30 days';


Returns:

"2010-10-26";f;t;t;t;f;t;t;t

But I expected:

"2010-10-26";f;f;f;t;f;f;t;t

Something fishy there ...

"Dobes Vandermeer" <dobes.vandermeer@kashoo.com> writes:
> Seeing some surprising behavior with the use of age() and comparing the
> result to an interval:

> select current_date,
>        age(current_date - interval '123 days') <= interval '120 days',
>        age(current_date - interval '122 days') <= interval '120 days',
>        age(current_date - interval '121 days') <= interval '120 days',
>        age(current_date - interval '120 days') <= interval '120 days',

It's not that surprising.  The actual underlying results (as of today)
are

regression=# select age(current_date - interval '123 days') ;
     age
--------------
 4 mons 1 day
(1 row)

regression=# select age(current_date - interval '122 days') ;
  age
--------
 4 mons
(1 row)

regression=# select age(current_date - interval '121 days') ;
      age
----------------
 3 mons 29 days
(1 row)

regression=# select age(current_date - interval '120 days') ;
      age
----------------
 3 mons 28 days
(1 row)

which aren't really very comparable to an interval stated as '120 days'.
The interval <= operator does the best it can by assuming that a month
equals 30 days, but of course the particular months underlying the
age() calculation were not that length.  Basically you've lost
information when you go from specific dates to a symbolic interval
value.

The point of the age() calculation is to give you something that's
useful in human terms.  It isn't necessarily going to work without
any surprises to do further arithmetic with it.

            regards, tom lane

Er, OK.

Perhaps a footnote in the documentation would resolve this for the benefit
of future users.  I think the fact that there is an incongruence between
intervals '120 days' and '3 mon 28 days' confused me since I'm used to
thinking of intervals as being some number of seconds behind the scenes.

On Tue, Oct 26, 2010 at 5:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Dobes Vandermeer" <dobes.vandermeer@kashoo.com> writes:
> > Seeing some surprising behavior with the use of age() and comparing the
> > result to an interval:
>
> > select current_date,
> >        age(current_date - interval '123 days') <= interval '120 days',
> >        age(current_date - interval '122 days') <= interval '120 days',
> >        age(current_date - interval '121 days') <= interval '120 days',
> >        age(current_date - interval '120 days') <= interval '120 days',
>
> It's not that surprising.  The actual underlying results (as of today)
> are
>
> regression=# select age(current_date - interval '123 days') ;
>     age
> --------------
>  4 mons 1 day
> (1 row)
>
> regression=# select age(current_date - interval '122 days') ;
>  age
> --------
>  4 mons
> (1 row)
>
> regression=# select age(current_date - interval '121 days') ;
>      age
> ----------------
>  3 mons 29 days
> (1 row)
>
> regression=# select age(current_date - interval '120 days') ;
>      age
> ----------------
>  3 mons 28 days
> (1 row)
>
> which aren't really very comparable to an interval stated as '120 days'.
> The interval <= operator does the best it can by assuming that a month
> equals 30 days, but of course the particular months underlying the
> age() calculation were not that length.  Basically you've lost
> information when you go from specific dates to a symbolic interval
> value.
>
> The point of the age() calculation is to give you something that's
> useful in human terms.  It isn't necessarily going to work without
> any surprises to do further arithmetic with it.
>
>                        regards, tom lane
>