I just got an email from docs@mysql.com saying the following:
The user comment system in the MySQL manual is not the place to request features. You can do so using our bug-tracking system at http://bugs.mysql.com/. Thanks. (Actually, your comment is not a feature request, but it relates to another comment that is. The example you’re giving is nice, but this is a reference manual, so we have to restrict it to _a few_ useful examples.)
My original comment was:
You bring up some important issues, but dealing with ages really isn’t that hard. For example you could do something like this:
mysql> SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS age FROM people;Where ‘dob’ is obviously their date of birth. It’ll also work with pre and post-epoch dates. Please excuse the funky formatting as the
comment stem seems to insist on inserting line breaks into the code block. I ran into this problem while working on some genealogical things over at Mullenweg.com, a family site. I hope this helps!
Looking back, it’s funny that the comment is still around, I wrote it over two years ago. The date and time functions is the MySQL page I use most, so in some sense it was always nice to have my mark on there. For google and posterity I’ve preserved the comment here.
I’m glad they’re cleaning up the comments, as they are really bad in places and have atrocious formatting, especially when compared to say, the PHP manual. However there is a later comment (which is still up) that offers perhaps a better method. From Kirill Novitchenko:
The method posted by Mathew Mullenweg is good, but leap years goof it up on birthdays. (Try it. Use the current date and subtract exactly 5 years ago.)
Hopefully this will be the last ‘find age’ function. There is a simple premise to it:
- Subtract the current year from the birth year to get the age.
- If the current month and date is less than the birth month and date, subtract 1 from step 1.
Therefore, this should work with everyone who wasn’t born in the future.
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS agewhere dob is date of birth.
I’ve never run into any problems with my function but I see nothing wrong with the way this one works, so I may update my code to use it.
Why not just use unix timestamps and avoid all the funkiness? When I first started writing everything I actually did, but then one day I got a call from my lovely sister saying that it was showing everyone’s birthday as January 8th, 1901 (or something like that). I had reached the negative limit of a 32-bit integer, the upper limit being sometime in 2038. Moving all the date functions into the SQL is probably bad from a programming point of view but it works great for the application. Of course I have no clue how it deals with the 10 days Pope Gregory removed from the calendar in 1582. Hopefully that won’t come up. 🙂
Maybe it is because I know PHP better than SQL, but it seems like it would be easier to do this in PHP… is there a performance benefit from having the database do the work?
If I did it in PHP I would have to use a class or something that used either 64-bit integers for date calculations or had some sort of custom date handling mehcanisms. Built-in PHP stuff wouldn’t work.
This works for me: ROUND((CURDATE() – DOB)/10000) AS age
dob = day of birth
I dont know why I divide by 10.000 but the ages are correct after rounding it. If would like to know why I have to divide it.
perhaps
FLOOR((CURDATE() – DOB)/10000) AS age
THANKS MAN! GREAT HELP
Yeah! I seemed to work. However, I am also curious why we need that 10000 division. Any response from Mysql people?
You need 10000 because if you subtract 2 integer version of mysql dates 10000 = 1 year
Example:
2003-01-01 = 20030101
2004-01-01 = 20040101
That’s 1 year
subtract the 2 numbers you got 10000
The divide-by-10000 is probably necessary to strip out the hours, minutes, and seconds from the numerical value … 🙂
THANKS MAN! GREAT HELP
easier way
SELECT YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS(dob) DAY)) AS age
Roy
You’re almost all wrong 🙁
The only working solution is the one mentioned by Kirill Novitchenko. As he mentioned in his post: the otherwise fine solution by Matt – as well as all the other solutions mentioned in the comments – have a problem when dealing with leap years.
Thanks man, saved me from having to do some actual math.
SELECT birth_dt, ROUND((TO_DAYS(CURDATE()) ““ TO_DAYS(birth_dt)) / 365,0) AS Age FROM employees;
Try something like this instead
rxn @ 8.2.07 is right: The only completely correct solution is the one posted by Kirill Novitchenko.
A big reason not to this stuff with PHP is the possibility of sorting those age calculations in a mysql result set.
This post has been very helpful, thanks a lot!
I got curious and did some tests with Matt’s method, so I thought I’d share my results.
The problem is that the number of leap days (Feb 29) between NOW() and the birth date can differ from the number of leap days when counting from 0000-00-00.
For example, if NOW() (first date) is 2006-06-11 and the birth date is and 2005-06-11, the correct age we want is 1, but the query result is 0.
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(‘2006-06-11’)-TO_DAYS(‘2005-06-11’)), ‘%Y’)+0 AS age;
Feb 2005: 28 days
… while …
Feb 0000: 29 days = SELECT TO_DAYS(‘0000-03-01’)-TO_DAYS(‘0000-02-01’)
Of course the error is not nearly as bad as if dividing the number of days by 365 (as this completely ignores leap years), and might remain completely unnoticed as it should never be more than one day off.
@Patrick
Thanks for the testing data. I ran a few from it as well, and came up with this solution:
YEAR(NOW()) – YEAR(birthdate) – (DAYOFYEAR(NOW()) < DAYOFYEAR(birthdate))
It seems to be working correctly (including leap years). Personally, I prefer that the comparison not use DATE_FORMAT. Does anyone see a problem with this?
I have tried all of these in a mysql statement and cant get any to return an accurate date. The date as I type this is 2008-27-04. My birth date is 1975-21-04. It returns my age as 33, which is correct. However, the other date I have in my database is 1978-08-01 (August 1, 1978) which is returned as 30….but it is actually 29.
This is the statement I am running:
SELECT Model_BirthDate,
(YEAR(NOW()) – YEAR(Model_BirthDate)) – ((DAYOFYEAR(NOW()) < DAYOFYEAR(Model_BirthDate)))
FROM Models;
AS Age;
Any ideas how I can get this to return the correct age?
Thank you
((YEAR(CURRENT_DATE) – YEAR(birthdate)) – (RIGHT(CURRENT_DATE,5) < RIGHT(birthdate,5)))
@Jeremy:
Your solution has a problem with leap years as well, the 31st of december is day number 265 in normal years, and number 266 in leap years. Since on a leap year an extra day is added to february, when it’s a leap year and i run this query on my 22st birthday on the 14th of may it will say that i’m still 21. I came up with this solution, it is leapyear proof afaik and it equates timestamps which i think is safer than comparing strings:
(YEAR(CURRENT_DATE()) – YEAR(birthdate) – (UNIX_TIMESTAMP(CURRENT_DATE())<UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE()), ‘-‘, MONTH(birthdate), ‘-‘, DAYOFMONTH(birthdate))))) AS `age`
I know this is a ‘late’ reply, but there is a syntax error with Rico’s solution (replying to Jeremy): check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”“ YEAR(birthdate) -MySQL server version: 5.1.30
However the Kirill Novitchenko as mentioned before works perfectly up to this point in time. The reason I was curious to try Rico’s solution is because it used the UNIX timestamp instead. =) If you ever come back RICO, please post the updated or correct code. Thanks in advance.
Don’t copy/paste it because you’re pasting non-latin characters. The “–” should be a hyphen.
Dude, let’s focus,
I know that dividing the difference (in days) for 365 introduces and error for leap years, but this error is like 0.0027 years every 4 years, so, unless a creature older than 1480 subscribes to your website, using a simple
SELECT FLOOR(DATEDIFF(CURRENT_DATE,dob)/365);
works really, really fine…
We can find the amount of the leap years if we assume some year after which we are counting the age (like 1900, if we assume the maximum age to be bit over 100 years).
So, year 1904 was a leap year. Leap days between dob and 1904 are:
SELECT FLOOR(((YEAR(dob)-1900) / 4))
And again, leap days between current date and the year 1904:
SELECT FLOOR(((YEAR(CURRENT_DATE)-1900) / 4))
So, well find the number of leap days between the dates by a simple subtraction: second_number – first_number.
So, for example the age for a person born in 12th November 1991 would be today:
SELECT (DATEDIFF(CURRENT_DATE, ‘1991-09-12’) – (FLOOR(((YEAR(CURRENT_DATE)-1900) / 4)) – FLOOR(((YEAR(‘1991-09-12’)-1900) / 4)))) / 365
We can easily divide by 365 without any error. This works until the year 2100 as 2100 won’t be a leap year since it’s a number divisible by 100 but not by 400.
Correction above: not born in November but in September
A database is designed to store data in the most logical way. It is not a trashcan or toy box where items are just tossed in those types of containers. It is structured and controlled by “DATA TYPE”. So when doing a query one should always focus on the “DATA TYPE” not create SLOPPY HACKS because you don’t understand how to logically use the database the way it was meant to be used. While Kirill Novitchenko query works and is the only one that does work it still goes against every logical rule one should follow when designing optimized queries! RESPECT DATA TYPES! So the proper way to do an age calculation is as follows…
SELECT YEAR(CURRENT_DATE)
– YEAR(stamp)
– CASE WHEN MONTH(CURRENT_DATE)
> MONTH(stamp)
THEN 0
WHEN MONTH(CURRENT_DATE)
< MONTH(stamp)
THEN 1
WHEN DAYOFMONTH(CURRENT_DATE)
< DAYOFMONTH(stamp)
THEN 1
ELSE 0 END AS age
FROM table ORDER BY stamp;.
Where "stamp" is your date of birth column and "table" is your table name!
Thank you Sonia…. your example works great!
Thanks so much for posting this, it helped immensely.
just run the query
SELECT DATEDIFF(CURDATE(),dob)/365 FROM table;
This ignores leap years
This worked like a charm – it helped me greatly. Thank you!
Thanks for the code. It helps me.
To calculate age of a person using non standard formatted date:
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(STR_TO_DATE(’02/11/1990′,’%d/%m/%Y’))), ‘%Y’)+0 AS age;
Try this
TIMESTAMPDIFF(YEAR, dob, now())
Where dob is your date of birth in db in mm/dd/yyyy format. This is accurate to the second using unix timestamp. The only flaw is that unix timestamp is based on paris’s timezone so you would have to keep your current timezone in relation with paris’s timezone to be %100 accurate.
I needed a more detailed age includes months and days of age. This is the brief function
Hope it helps.
I came up with:
DELIMITER $$
DROP FUNCTION IF EXISTS Get_Age $$
CREATE FUNCTION Get_Age( rid INT unsigned )
RETURNS CHAR(20)
BEGIN
DECLARE years INT default 0;
DECLARE months INT default 0;
DECLARE days INT default 0;
DECLARE age DATE;
SELECT date_add(‘0001-01-01’, interval datediff(current_date(),birth_date) day ) INTO age FROM patients WHERE id = rid;
— Check that the age we’re given is useful
IF age is null or age = 0 or age = ‘0000-00-00’ THEN
RETURN age;
END IF;
SELECT YEAR(age) – 1 INTO years;
SELECT MONTH(age)- 1 INTO months;
SELECT DAY(age) – 1 INTO days;
IF years THEN
RETURN concat(years,’y ‘,months,’m’);
ELSEIF months THEN
RETURN concat(months,’m ‘,days,’d’);
ELSE
RETURN concat(days,’ days’);
END IF;
END $$
DELIMITER ;
Check this page out it is the answer to the mysql age calculation
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timestampdiff
This comment is for Alan sorry for late reply I just send activity on this today in my email.
For more detailed age is
TIMESTAMPDIFF(YEAR, dob, now()) as year
TIMESTAMPDIFF(MONTH, dob, now()) as month
TIMESTAMPDIFF(DAY, dob, now()) as day
* You can also do minutes too, everybody on here is either giving some complex if condition (which can be %100 accurate) or math equation (that is slightly incorrect when considering leap years). Mysql already has a function that accounts for all that and is not limited by the unix timestamp limit even though the name would have you think otherwise
This is awesome, people have the tendency to complicate things. I search the Internet for a simple solution for a simple problem and all the solutions for the simple problem were way to complex and that didn’t compute. This solution is simple and right, writing 100 lines of code to something so simple is overkill. Thank you!
SELECT DATEDIFF(now(),birthday)/365.25 as ‘age’, firstname, lastname from employee
one year is equivalent to 365 days and 6 hours… In short 365.25 days
My successful statement was this:
SELECT
username,
floor((now()-borndate)/ 10000000000) AS age
from users
borndate is DATETIME field. now()-borndate returns huge integer, so I divide it to 10000000000 to strip months, hours. minutes, seconds and microseconds there.
If it is only a DATE field, you sdhould divide it to 10000.
Then i MUST FLOOR() it, NOT round! Ages must be floored because else the application will report a birthday 6 months before the actual birthday 🙂
That is my opinion.
Let’s think about this in a practical manner. If I tell you my birthday. You figure out my age by subtracting the year I was born from the current year. And then if my birthday isn’t already come this year, you subtract 1. So, let’s just do it that way. Eliminating all the dividing leap year calculating etc……
Age is not that hard to find. Subtract birthyear from current year and then if the birthday hasn’t already passed, subtract 1 more. Simple.
SELECT YEAR(NOW()) – YEAR(‘1976-02-29’) – (DAYOFYEAR(NOW()) < DAYOFYEAR('1976-02-29'));
Doesn’t work for Leap year Feb 29th babies every day of the year, but I added some code that keeps it accurate even for them. See my comment below for link.
My take on this along with a detailed breakdown of the thinking behind the final query: http://www.gizmola.com/blog/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html
Try this:
SELECT (year(curdate()) – year(yourDB.yourTable.dob)) – (right(curdate(), 5) < right(yourDB.yourTable.dob, 5)) AS Age
FROM yourDB.YourTable;
This post seems to be 10 years old. As the first comment is from the year 2003 I guess the original post is also from this year. There is also a solution on mysql http://dev.mysql.com/doc/refman/5.0/en//date-calculations.html which should be sufficient and proper. Or not?