Re: doing math with date function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I want to do basic math on a data field (get the difference in years)
> but can't find the example to do it.

You want the age() function, but be careful how you approach it.
Remember that months are very uneven things, and thus the concept
of '18 years' can be quite ambiguous. In your particular example,
you should rely on extract() to pull out the year from age() instead
of letting postgres do the calculation itself, as it breaks down along
the edges:

- -- Under 18 = youths | 18-21 = no drinking | 21 and up = legal

CREATE TABLE temp_table (
who VARCHAR(20),
dob TIMESTAMP
);

- -- Alice is a youth
insert into temp_table(who,dob)
values ('Alice', now()-timespan('17 years'));

- -- Bob just turned 18
insert into temp_table(who,dob)
values ('Bob', now()-timespan('18 years'));

- -- Mallory is over 21 and quite legal
insert into temp_table(who,dob)
values ('Mallory', now()-timespan('21 years 1 week'));

- -- Eve's is 17 for twelve more hours
insert into temp_table(who,dob)
values ('Eve', now()-(timespan('18 years')-timespan('12 hours')));

SELECT who, to_char(dob, 'Mon DD, YYYY') as DOB,
TO_CHAR(age('now',dob),'YY/FMMM/FMDD') as "Years/Months/Days",
CASE
WHEN age('now',dob) >= timespan('21 years')
THEN 'legal'
WHEN age('now',dob) >= timespan('18 years')
THEN 'no drinking'
ELSE 'youths'
END as identify,
CASE
WHEN extract(year from age('now',dob)) >= 21
THEN 'legal'
WHEN extract(year from age('now',dob)) >= 18
THEN 'no drinking'
ELSE 'youths'
END as identify2
FROM temp_table;

who | dob | Years/Months/Days | identify | identify2
- ---------+--------------+-------------------+-------------+-------------
Alice | Mar 18, 1985 | 17/0/0 | youths | youths
Bob | Mar 18, 1984 | 18/0/0 | no drinking | no drinking
Mallory | Mar 11, 1981 | 21/0/7 | legal | legal
Eve | Mar 19, 1984 | 17/11/30 | no drinking | youths

Notice that Eve is incorrectly put into the 'no drinking' category
by the first CASE statement: she is not quite 18: the second
CASE statement gets it correct.

Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200203182300

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8lrk5vJuQZxSWSsgRAkj8AJ9tmKs3Rva8DQ0KA+64+1/zDFHo3gCgh254
tPf2AjgWv/FrtG9GdoG93JI=
=hfqk
-----END PGP SIGNATURE-----