[Zope-DB] Re: Question on ZSQL and Dates

[Michal Kurowski]

Charlie Clark [charlie@begeistert.org] wrote:
> Hi, 
> 
> I've been struggling for a while with this one: I need to be able compare 
> dates/ages in a ZSQL-query and don't seem to be able to do it right.
> 
> table person
> name varchar(20),
> id int,
> birthdate date
> 
> Now I want to be able to select all rows in the table for people who are 
> currently over 45 years old.
> 
> SELECT * FROM person
> WHERE
> 45 <= '<dtml-var expr="_.DateTime().Date()">' - person.birthdate
> 
> but as this doesn't work, I know I'm doing something wrong.
> Any ideas? Searched all over so far but have yet to find anything helpful 
> :-(

These things are largely vendor specific but always best done on the
database level.
In mySQL I would say:

select name, id, birthdate, (extract(year from now()) - extract(year
from birthdate)) as delta from person having delta >= 45;

But before that it is worth thinking about design and choosing proper
types (date, timestamp, etc). Check out docs.

Cheers,

-- 
Michal Kurowski
<michal@genesilico.pl>