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.
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.