Re: SELECTing on age

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;