Age calculation with MySQL
The one or another may think, that the calculation of the age is a trivial issue, that also should be eaysily solved by beginners. It is! But for what is this post then good for? Actually you only have to take the difference between the birth date and the current time.
Assume, that we store the birth date as an INT UNSIGNED in our database - which means we store the unix timestamp. So we could use the value on the client side (here with PHP) as follows:
$age = time() - $row['bday'];
Up to this point, the approach doesn't have any problems. But we usually do not specify the age in seconds. To express the age in years, we have to divide the age in seconds by the number of seconds of a year, means we devide with 60 * 60 * 24 * 365 - approximately.
Okay, let's make a quick test. I was born on 23 January 1988 - or 625878000. The current unix timestamp is (the value is taken from your computers clock using JavaScript). Now we divide the calculated difference by 31536000 (60 * 60 * 24 * 365) and we will get almost my expected age of . The problem is, that this result is a vague answer, especially when we need accuracy around the birthday - which is a really annoying problem (Carsten, if you read this, you know what I mean ;-) ). Where is this inaccuracy come from? If we are correct a year has about 365.25 days. To encounter this problem we have a leapyear every four years. Sure we could add a dozen conditions to get a correct result, but let's see if we can solve it easier inside of MySQL.
Back to the drawing board. First we should think about the storage of our birth date, is it really necessary to use an integer? I think most peoples do not know the exact second of their birth - why should they? We do not celebrate the birth second but the birth day. So we can state out, that it is enough to store the birth date as DATE. But let's come to the more interesting part - the calculation. I wrote a little function GETAGE() to get the age from a DATE value. The return value is a TINYINT UNSIGNED and we don't have to care about rounding problems or even the problem with leapyears. The function is really short and I hope also self-explanatory:
CREATE FUNCTION GETAGE(BDAY DATE) RETURNS tinyint unsigned RETURN (YEAR(CURDATE())-YEAR(BDAY)) - (RIGHT(CURDATE(),5) < RIGHT(BDAY,5));
You might also be interested in the following
- Calculate date ratios
- Handy PHP classes
- Disable ON UPDATE CURRENT_TIMESTAMP in MySQL
- People near you with MySQL
Sorry, comments are closed for this article. Contact me if you want to leave a note.
3 Comments on „Age calculation with MySQL”
Hi Remon,
thanks for your notice about the problem when the birthday is the current date. I've updated the function and it is now working correctly for all given date values.
Robert
Your getage() functions is not correct: it chokes on some (not all) birthdays that are on the current date. Today (2011-08-12) it will return an incorrect age of 0, 1 and 2 for people born on the 12th of August in 2010, 2009 and 2009. For 2007-08-12 it will correctly return 4. If you use FROM_DAYS() it will start on the 1st of January in the leap year 0000 and it will encounter a leap day quite soon, whereas the interval between BDAY and NOW() might include one leap day less. For birthdays on the current date the FROM_DAYS() function might return a date like 0002-12-31 instead of 0003-01-01 because the year 0000 had 366 days.
very smart and simple. thank you.