Some time ago I needed to be able to extract the age of persons from a MySQL database. The database stored the persons’ date of births in a DATE column, which is a pretty good idea when dealing with birth dates, due to the limitations of UNIX timestamps.
Somewhere in a comment on the MySQL website, I found a calculation that seemed to do the trick. I am unsure who the original author is. I improved it to be more efficient and clean by removing some unnecessary string conversions. I wrapped the result in a function that can be seen below.
CREATE FUNCTION `get_age` ( `date_of_birth` DATE, `current_time` DATETIME ) RETURNS INT(11) UNSIGNED COMMENT 'Calculates the age from the date of birth' DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN ((YEAR(current_time) - YEAR(date_of_birth)) - ((DATE_FORMAT(current_time, '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d'))));
The formatting makes it slightly confusing to read, but copy and paste it into your database and give it a go. I will not go into much detail with how the function works, but essentially it subtracts the current year with the year from the date of birth, and then it subtracts one year if the current day and month is less than that of the date of birth. In that case, the person has not aged yet this year. It relies on the fact that a boolean expression is represented by the integer value of 1 or 0, which is kind of a “hack” – but it works, nevertheless. This is a small price of keeping things short and simple.
The function is made deterministic by taking the current datetime as a parameter, meaning that the function will return the same result every time it is invoked with the same parameters. If the current time were determined within the function itself, it could not have been deterministic, as the result of calling the function with a given date of birth would depend on when it was called. This can potentially be of importance if you are using the function more than once within a single query, for example. In such a scenario, the query optimizer is likely to not execute the function twice because if the function is labeled as deterministic, it should return the same result anyways. Please notice the emphasis on the word “likely”; one should not rely on the query optimizer making these kinds of decisions, but consider it more like a bonus if it is the case.
Using the function is as simple as in the below query.
SELECT get_age(date_of_birth, NOW()) AS age FROM person WHERE id = 123
That is all! The above converts a DATE value to an age. If you have any comments or know of other ways to accomplish this, then you are more than welcome to leave a comment.
Learn MySQL today!
Take an online course and improve your MySQL skills!
Here is what is covered:
- The basics of MySQL
- Locks, transactions, functions, stored procedures, triggers
- User management and priviledges
- ... and much more!