How To Calculate Age From Date Of Birth In MySQL

SQL age calculations often come up during transaction processing and date arithmetic during workflow processing in data-centric applications. MySQL provides several date functions to perform several operations on date data types. Age calculation is also performed in SQL using a combination of these date functions. In this article, we will learn how to calculate age from a given date of birth value in MySQL.

SQL Age Calculation – The Components

There is no out-of-the-box function to calculate age in MySQL. However, that doesn’t stop us from creating a function of our own or perform the calculations in SQL given that there are several different date functions available. In fact, there could be more than one implementation approach to determine age.  Moreover, age is, mathematically just a difference of two dates, the current date and the date of birth.

MySQL already provides the following function:

•         CURDATE() – to find out the current date
•         TIMESTAMPDIFF() – to find out the difference between two dates in units of choice

These two functions can be put together to determine age from date of birth in years, months or other time units.

CURDATE() returns the date of the instance when it is run. It does not need any parameters and its syntax is:

CURDATE()

The syntax for TIMESTAMPDIFF() is

TIMESTAMPDIFF(unit, begin_date, end_date)

Where the unit argument determines the unit in which the difference is to be retrieved. Valid units are:

•         YEAR
•         QUARTER
•         MONTH
•         WEEK
•         DAY
•         HOUR
•         MINUTE
•         SECOND
•         MICROSECOND

Therefore we could write

SELECT TIMESTAMPDIFF(YEAR, '2018-01-20', '2019-01-21');

The above statement shall return 1.

If we change it to:

SELECT TIMESTAMPDIFF(DAY, '2018-01-20', '2019-01-21');

The result is 366

SQL Calculate Age – Putting It Together

Now that we know which functions can be used, we aim to write an expression which shall give the difference in years between a “date of birth” field and current date i.e. age. With a little thought, the expression is:

TIMESTAMPDIFF(YEAR, dob, CURDATE())

Note here that the “dob” field should be of date data-type or the value should be converted into a date format before passing it to the TIMESTAMPDIFF function.

Let us say the current date is 25th May 2019, and the date of birth is 3rd August 1978. To calculate the age we could write:

SELECT TIMESTAMPDIFF(YEAR, '1978-08-03', CURDATE()); 

It comes out to be 40.

MySQL Calculate Age – Increasing The Precision

The above procedure gives correct age in years. However, it gives age rounded off to years and hence doesn’t give the age fractional age in months and days. Therefore if someone is a few months and days old, the above expression will return 0 years.

To calculate age in months and days apart from full years, we make use of 

TIMESTAMPDIFF function with a period of calculation as MONTH and DAYS.

Therefore:

TIMESTAMPDIFF(MONTH, dob_field, CURDATE())

gives the age in number of full months.

Whereas:

TIMESTAMPDIFF(DAY, dob_field, CURDATE())

gives the age in number of days.

Mathematically, therefore we could find partial months and partial days by find respective remainders. ie.

TIMESTAMPDIFF( MONTH, dob_ field, CURDATE() ) % 12 will give partial months and

TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 will give days left over after all months have been taken care of.

Putting all this together we may write the expression of age as:

SELECT CONCAT(TIMESTAMPDIFF( YEAR, dob_ field, CURDATE()  ),' Years,', TIMESTAMPDIFF( MONTH, dob_ field, CURDATE()) % 12,'  Months,',FLOOR( TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 ),' Days') 

where dob_ field is the field/variable containing the date of birth.

How To Calculate Age In SQL – Example

Let us create a table with a few names and date of births:

DROP TABLE IF EXISTS T1;

CREATE TABLE T1(student_name VARCHAR(100), dob date, age_year INT, age_month INT, age_days INT, age_formatted VARCHAR(200));

Let us then insert the random data:

INSERT INTO t1(student_name, dob) values ('Jennifer Brit', '1990-02-20');

INSERT INTO t1(student_name, dob) values ('David Dunkrick', '1988-12-20');

INSERT INTO t1(student_name, dob) values ('Michelle Morris', '2001-08-03');

INSERT INTO t1(student_name, dob) values ('Ubaid Ali', '1987-10-19');

INSERT INTO t1(student_name, dob) values ('Juliet Cross', '2019-01-25');

Lets now update the columns age_year, age_month, age_days and age_formatted with the following query which will calculate age in SQL query and update the age of the student in year, months and days in respective fields:

UPDATE T1

SET AGE_FORMATTED = CONCAT(TIMESTAMPDIFF( YEAR, DOB, CURDATE()  ),' YEARS,', TIMESTAMPDIFF( MONTH, DOB, CURDATE()) % 12,' MONTHS,',FLOOR( TIMESTAMPDIFF( DAY, DOB, CURDATE()  ) % 30.4375 ),' DAYS'),

AGE_YEAR = TIMESTAMPDIFF( YEAR, DOB, CURDATE()  ),

AGE_MONTH = TIMESTAMPDIFF( MONTH, DOB, CURDATE()) % 12,

AGE_DAYS = FLOOR( TIMESTAMPDIFF( DAY, DOB, CURDATE()) % 30.4375 )

We get the relevant age as below:

SELECT * FROM T1;