Average Age from DOB Field - MySQL / PHP

mysql date average dob
sluggerdog picture sluggerdog · Nov 14, 2012 · Viewed 7.8k times · Source

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

Bailey Parker picture Bailey Parker · Nov 14, 2012

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.

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
how to convert a string to date in mysql?

I have a string column which acts as a date and I want to select it as a date. Is it possible? My sample data format would be; month/day/year -> 12/31/2011

Read More