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));