Обсуждение: doing math with date function
Howdy:
Running Postgres 7.1.3 on RedHat Linux 7.2 kernel
2.4.7 - rev 10.
This has probably asked done a million times over,
but I can't seem to find the examples:
I want to do basic math on a data field (get the
difference in years) but can't find the example
to do it.
What I want is something like:
[pseudo code]
select
name,
dob,
case
when now() - dob > '21'
then 'legal'
when now() - dob > '18'
then 'no drinking'
else 'youths'
end as identify
from temp_table
;
[/pseudo code]
Are there any examples like this?
Thanks!
-X
-----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@turnstep.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-----