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;

MySQL functions

In this part of the MySQL tutorial, we will cover MySQL built-in functions.

MySQL built-in functions can be categorised into several groups.

• Mathematical functions
• Aggregate functions
• String functions
• Date and time functions
• System Functions

Here we show only a portion of all MySQL functions. To get the full list of available functions, consult the MySQL reference manual.

Mathematical functions

MySQL supports multiple mathematical functions.

mysql> SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.786536605829873 |
+-------------------+

The RAND() function returns a random number from the <0, 1> interval.

mysql> SELECT ABS(-3), PI(), SIN(0.5);
+---------+----------+-------------------+
| ABS(-3) | PI()     | SIN(0.5)          |
+---------+----------+-------------------+
|       3 | 3.141593 | 0.479425538604203 |
+---------+----------+-------------------+

The ABS() function returns the absolute value of a number. The PI() function gives the value of PI. And the SIN() function computes the sine of an argument.

mysql> SELECT BIN(22), OCT(22), HEX(22);
+---------+---------+---------+
| BIN(22) | OCT(22) | HEX(22) |
+---------+---------+---------+
| 10110   | 26      | 16      |
+---------+---------+---------+

We use functions to give binary, octal and hexadecimal representation of decimal 22.

mysql> SELECT CEIL(11.256), FLOOR(11.256), ROUND(11.256, 2);
+--------------+---------------+------------------+
| CEIL(11.256) | FLOOR(11.256) | ROUND(11.256, 2) |
+--------------+---------------+------------------+
|           12 |            11 |            11.26 |
+--------------+---------------+------------------+

The CEIL() function rounds the value to the smallest following integer. The FLOOR() function rounds the value to the largest previous integer. The ROUND() returns a number rounded to a specified number of decimal places.

mysql> SELECT POW(3, 3), SQRT(9);
+-----------+---------+
| POW(3, 3) | SQRT(9) |
+-----------+---------+
|        27 |       3 |
+-----------+---------+

The power and the square root functions.

mysql> SELECT DEGREES(2*PI());
+-----------------+
| DEGREES(2*PI()) |
+-----------------+
|             360 |
+-----------------+

The DEGREES() function computes degrees from radians.

Aggregate functions

Aggregate functions operate on sets of values.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We have the Cars table.

mysql> SELECT MIN(Cost), MAX(Cost), AVG(Cost)
    -> FROM Cars;
+-----------+-----------+------------+
| MIN(Cost) | MAX(Cost) | AVG(Cost)  |
+-----------+-----------+------------+
|      9000 |    350000 | 72721.1250 |
+-----------+-----------+------------+

We use the MIN(), MAX() and AVG() aggregate functions to compute the minimal price, maximal price and the average price of cars in the table.

mysql> SELECT SUM(Cost), COUNT(Id), STD(Cost), 
    -> VARIANCE(Cost) FROM Cars;
+-----------+-----------+-------------+------------------+
| SUM(Cost) | COUNT(Id) | STD(Cost)   | VARIANCE(Cost)   |
+-----------+-----------+-------------+------------------+
|    581769 |         8 | 105931.1676 | 11221412265.3594 |
+-----------+-----------+-------------+------------------+

We use the SUM() function to get the sum of all values in the Cost column. We count the number of cars in the table with the COUNT() function. Finally, we get the standard deviation and variance using the STD() and VARIANCE() functions.

String functions

In this group we have various strings related functions.

mysql> SELECT LENGTH('ZetCode'), UPPER('ZetCode'), LOWER('ZetCode');
+-------------------+------------------+------------------+
| LENGTH('ZetCode') | UPPER('ZetCode') | LOWER('ZetCode') |
+-------------------+------------------+------------------+
|                 7 | ZETCODE          | zetcode          |
+-------------------+------------------+------------------+

The LENGTH() function returns the length of a string. The UPPER() function converts characters into upper-case letters. The LOWER() function converts characters into lower-case letters.

ysql> SELECT LPAD(RPAD("ZetCode", 10, "*"), 13, "*");
+-----------------------------------------+
| LPAD(RPAD("ZetCode", 10, "*"), 13, "*") |
+-----------------------------------------+
| ***ZetCode***                           |
+-----------------------------------------+

We use the LPAD() and RPAD() functions to append and prepend characters to a specified string. The "ZetCode" string has 7 characters. The RPAD() function appends 3 '*' characters to the string, which will be now 10 characters long.

mysql> SELECT REVERSE('ZetCode'), REPEAT('*', 6);
+--------------------+----------------+
| REVERSE('ZetCode') | REPEAT('*', 6) |
+--------------------+----------------+
| edoCteZ            | ******         |
+--------------------+----------------+

The REVERSE() function reverses the characters in a string. The REPEAT() function repeats a string specified number of times.

mysql> SELECT LEFT('ZetCode', 3), RIGHT('ZetCode', 3), 
    -> SUBSTRING('ZetCode', 3, 3);
+--------------------+---------------------+----------------------------+
| LEFT('ZetCode', 3) | RIGHT('ZetCode', 3) | SUBSTRING('ZetCode', 3, 3) |
+--------------------+---------------------+----------------------------+
| Zet                | ode                 | tCo                        |
+--------------------+---------------------+----------------------------+

The LEFT() function returns 3 leftmost characters, the RIGHT() function returns 3 characters from the right. The SUBSTRING() function returns three characters from the third position of the string.

mysql> SELECT STRCMP('byte', 'byte'), CONCAT('three', ' apples');
+------------------------+----------------------------+
| STRCMP('byte', 'byte') | CONCAT('three', ' apples') |
+------------------------+----------------------------+
|                      0 | three apples               |
+------------------------+----------------------------+

The STRCMP() compares two strings and returns 0 if they are the same. The CONCAT() function concatenates two strings.

mysql> SELECT REPLACE('basketball', 'basket', 'foot');
+-----------------------------------------+
| REPLACE('basketball', 'basket', 'foot') |
+-----------------------------------------+
| football                                |
+-----------------------------------------+

The REPLACE() function returns a string, in which we have replaced some text. The first parameter is the original string. The second parameter is a string, we want to replace. And the last parameter is the new replacing string.

Date & time functions

In this group we have various date and time functions.

mysql> SELECT DAYNAME('2011-01-23'), YEAR('2011/01/23'),
    -> MONTHNAME('110123');
+-----------------------+--------------------+---------------------+
| DAYNAME('2011-01-23') | YEAR('2011/01/23') | MONTHNAME('110123') |
+-----------------------+--------------------+---------------------+
| Sunday                |               2011 | January             |
+-----------------------+--------------------+---------------------+

In MySQL, date is written in the format YYYY-MM-DD. Year is followed by month and day. They can be separated by slash or by hyphen. MySQL also supports a shortened date format, without separators. Time is written in a standard form, HH:MM:SS. Hours followed by minutes and seconds.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-01-22 00:24:49 |
+---------------------+

The NOW() function returns the current date and time.

mysql> SELECT CURTIME(), CURDATE();
+-----------+------------+
| CURTIME() | CURDATE()  |
+-----------+------------+
| 00:25:03  | 2011-01-22 |
+-----------+------------+

The CURTIME() returns the current time and the CURDATE() returns the current date.

mysql> SELECT DATEDIFF('2011-3-12', '2011-1-12');
+------------------------------------+
| DATEDIFF('2011-3-12', '2011-1-12') |
+------------------------------------+
|                                 59 |
+------------------------------------+

With the DATEDIFF() we get the number of days between two dates.

mysql> SELECT DAYNAME('1982-4-12'), MONTHNAME('1982-4-12') ;
+----------------------+------------------------+
| DAYNAME('1982-4-12') | MONTHNAME('1982-4-12') |
+----------------------+------------------------+
| Monday               | April                  |
+----------------------+------------------------+

The DAYNAME() function returns the day name of a date. The MONTHNAME() function returns a month name of a date.

mysql> SELECT WEEKOFYEAR('110123'), WEEKDAY('110123'),
    -> QUARTER('110123');
+----------------------+-------------------+-------------------+
| WEEKOFYEAR('110123') | WEEKDAY('110123') | QUARTER('110123') |
+----------------------+-------------------+-------------------+
|                    3 |                 6 |                 1 |
+----------------------+-------------------+-------------------+

January 23, 2011 can be written in a shortened date format, 110123. We use the WEEKOFYEAR() to find out the week of the year. The WEEKDAY() returns 6, which is Sunday. And the QUARTER() function returns the quarter of the year.

mysql> SELECT DATE_FORMAT('110123', '%d-%m-%Y');
+-----------------------------------+
| DATE_FORMAT('110123', '%d-%m-%Y') |
+-----------------------------------+
| 23-01-2011                        |
+-----------------------------------+

To display date in a different format, we use the DATE_FORMAT().

mysql> SELECT DATE_ADD('110123', INTERVAL 45 DAY), 
    -> SUBDATE('110309', INTERVAL 45 DAY);
+-------------------------------------+------------------------------------+
| DATE_ADD('110123', INTERVAL 45 DAY) | SUBDATE('110309', INTERVAL 45 DAY) |
+-------------------------------------+------------------------------------+
| 2011-03-09                          | 2011-01-23                         |
+-------------------------------------+------------------------------------+

We can use DATE_ADD() to add time intervals to a date and SUBDATE() to subtract time intervals from a date.

System functions

System functions provide some system information about MySQL database.

mysql> SELECT VERSION(), DATABASE();
+--------------------+------------+
| VERSION()          | DATABASE() |
+--------------------+------------+
| 5.1.41-3ubuntu12.8 | mydb       |
+--------------------+------------+

We get the version of the MySQL database and the current database name.

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

The USER() function returns the user name and the host name provided by the client.

mysql> SELECT CHARSET('ZetCode'), COLLATION('ZetCode');
+--------------------+----------------------+
| CHARSET('ZetCode') | COLLATION('ZetCode') |
+--------------------+----------------------+
| utf8               | utf8_general_ci      |
+--------------------+----------------------+

The CHARSET() function returns the character set of the argument. The COLLATION() returns the collation of the current string argument. They depend on the charset and collation of the client in use.