Calculate Age in MySQL (InnoDb)

If I have a persons date of birth stored in a table in the form dd-mm-yyyy and I subtract it from the current date, what format is the date returned in?

How can I use this returned format to calculate someone's age?

Solution 1:

You can use TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) function:

SELECT TIMESTAMPDIFF(YEAR, '1970-02-01', CURDATE()) AS age

Demo

Solution 2:

If the value is stored as a DATETIME data type:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age 
  FROM YOUR_TABLE

Less precise when you consider leap years:

SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
  FROM YOUR_TABLE t 

Solution 3:

select *,year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)) as age from your_table

in this way you consider even month and day of birth in order to have a more accurate age calculation.

Solution 4:

SELECT TIMESTAMPDIFF (YEAR, YOUR_COLUMN, CURDATE()) FROM YOUR_TABLE AS AGE

Click for demo..

Simple but elegant..

Solution 5:

select floor(datediff (now(), birthday)/365) as age