Re: SELECTing on age - Mailing list pgsql-general

From Scott Marlowe
Subject Re: SELECTing on age
Date
Msg-id 1102973965.22049.6.camel@state.g2switchworks.com
Whole thread Raw
In response to Re: SELECTing on age  ("Kall, Bruce A." <kall@mayo.edu>)
List pgsql-general
On Mon, 2004-12-13 at 15:15, Kall, Bruce A. wrote:
> I'm attempting to select records from my postgresql database using php
> based on whether someone is at least 17 years old on the date of a
> particular visit.
>
> My sql is:
>
> $db_sql = "SELECT * from list WHERE ((visit_date - birth_date) >= 17)'"
> $db_result = db_exec($db_sql)
> $num = pg_num_rows($db_result);
> for($i = 0; $i < $num; $i++)
>    {
>    $data = pg_num_rows($db_result,$i)
>    $visit_date = $data["visit_date"];
>    $birth_date = $data["birth_date"];
>    echo "Visit date[$visit_date]  Birth date[$birth_date]";
>    }
>
> The problem I'm having is that the the query is returning results for
> some people with ages < 17 (most of them are correct, just a couple of
> incorrect ones interspersed with the correct ones that are over 17)?
>
> For example, my output contains:
>
> Visit date[2004-07-14]  Birth date[2004-02-19]
> and
> Visit date[2004-08-11]  Birth date[2003-04-21]
>
> which are clearly people who are < 17.

Check out what this query tells you:

postgres=# select ('2004-07-31'::date-'2004-07-01'::date);
 ?column?
----------
       30


Notice how the output of subtracting one date from another is an int for
the number of days?  A better way would be:

select * from table1 where dt <now()-'17 years'::interval;

pgsql-general by date:

Previous
From: Greg Stark
Date:
Subject: Corrupt RTREE index
Next
From: Joe Conway
Date:
Subject: Re: [ADMIN] plperl loading