User Tools

Site Tools


db:sql_calculate_age

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

db:sql_calculate_age [2013/03/16 17:40] (current)
Line 1: Line 1:
 +==== Calculating the age based on a date of birth field ====
 +19.10.2010
  
 +
 +This can be done calculating the difference in years, and then, taking into account the day of birth function of today: if in the future, we should -1 from age, because that person isn't there yet ;)
 +
 +
 +=== Using MySQL ===
 + 
 +<code sql>
 +SELECT
 +((DATE_FORMAT(NOW(),'​%Y'​) - DATE_FORMAT(date_of_birth_field,'​%Y'​)) - 
 +      (DATE_FORMAT(NOW(),'​00-%m-%d'​) < DATE_FORMAT(date_of_birth_field,'​00-%m-%d'​))) AS age 
 +FROM some_table WHERE 1
 +</​code>​
 +
 +
 +=== Using PostgreSQL ===
 +
 +PostgreSQL has a nice function called [[http://​www.postgresql.org/​docs/​current/​static/​functions-datetime.html#​FUNCTIONS-DATETIME-EXTRACT | age]].
 +
 +<code sql>
 +SELECT EXTRACT(year from AGE(NOW(), date_of_birth_field))
 +FROM some_table WHERE 1
 +</​code>​
db/sql_calculate_age.txt ยท Last modified: 2013/03/16 17:40 (external edit)