Average Age from DOB Field - MySQL / PHP

Question

I wasn't sure how I could calculate the average age of my contacts who all exist in a mysql table with a DOB date field such as YYYY-MM-DD.

Is there a way to do with with a MySQL call (please note I am using cakephp but that shouldn't be an issue)

Thanks

Answer 1

You can determine age by using DATEDIFF():

DATEDIFF(TO_DAYS(NOW()), TO_DAYS(DOB))

The average is found in MySQL using AVG():

AVG(Column)

So combine those:

SELECT AVG(DATEDIFF(TO_DAYS(NOW()), TO_DAYS(DOB))) as `Average` FROM Contacts;

Note that this returns the average age in days, not years. To obtain years you can average the years part of each date:

SELECT AVG(DATEDIFF(YEAR(NOW()), YEAR(DOB))) as `Average` FROM Contacts;

Or as @TimDearborn suggested, divide the day average by 365.242199.

Answer 2

Here is your answer

SELECT AVG(TIMESTAMPDIFF(YEAR, DOB, CURDATE())) AS `Average` FROM Contacts WHERE DOB IS NOT NULL;