Calculate Age From Date of Birth in MySQL

Published on July 14, 2013 by

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.

Featured

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
  • Aggregations
  • User management and priviledges
  • ... and much more!
MySQL logo
Author avatar
Bo Andersen

About the Author

I am a back-end web developer with a passion for open source technologies. I have been a PHP developer for many years, and also have experience with Java and Spring Framework. I currently work full time as a lead developer. Apart from that, I also spend time on making online courses, so be sure to check those out!

4 comments on »Calculate Age From Date of Birth in MySQL«

  1. truth

    did not work!

  2. rdr

    Hi !

    Your Function Works fine, But How I insert in a trigger? to assign the age value to a field

  3. Sajjad

    I tried this function

    function getage($strdate)
    {
    $dob = explode("-",$strdate);
    if(count($dob)!=3)
    {
    return 0;
    }
    $y = $dob[0];
    $m = $dob[1];
    $d = $dob[2];
    if(strlen($y)!=4)
    {
    return 0;
    }
    if(strlen($m)!=2)
    {
    return 0;
    }
    if(strlen($d)!=2)
    {
    return 0;
    }
    $y += 0;
    $m += 0;
    $d += 0;
    if($y==0) return 0;
    $rage = date("Y") - $y;
    if(date("m")<$m)
    {
    $rage-=1;
    }else{
    if((date("m")==$m)&&(date("d")<$d))
    {
    $rage-=1;
    }
    }
    return $rage;
    }
  4. natalia

    When i run above query, error message comes: function get_age does not exist.

Leave a Reply

Your e-mail address will not be published.