Calculate Age From Date of Birth in MySQL

Published on July 14, 2013 by Bo Andersen

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.

mysql

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

truth

# May 15, 2014 at 3:08 PM

did not work!
- Reply
rdr

# June 15, 2015 at 7:32 PM

Hi !

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

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

natalia

# June 26, 2016 at 1:10 PM

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

# December 2, 2017 at 10:00 AM

I need a php html form code that you can input the date of birth then automatically calculates the age of a person
- Reply
Ian Rutson

# December 21, 2017 at 8:50 PM

Thanks for the function but it always returns current age i.e.
```
SELECT get_age('1943-06-13','1999-01-01');
SELECT get_age('1943-06-13',now());
```
both return 74 as current_time returns the current time.

Replacing current_time with (for example) time_to_calc fixes this.
- Reply

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

Leave a Reply Cancel reply