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

Re: BUG #5728: Unexpected behavior comparing result of age() to an interval

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dobes Vandermeer" <dobes(dot)vandermeer(at)kashoo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5728: Unexpected behavior comparing result of age() to an interval
Date: 2010-10-27 00:47:55
Message-ID: 3128.1288140475@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Dobes Vandermeer" <dobes(dot)vandermeer(at)kashoo(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: Alan ChoiDate: 2010-10-27 01:02:52
Subject: BUG #5729: psql stopped after invalid surrogate pair
Previous:From: Dobes VandermeerDate: 2010-10-26 21:45:35
Subject: BUG #5728: Unexpected behavior comparing result of age() to an interval

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