How to calculate age in MariaDB

In MariaDB, the TIMESTAMPDIFF() function is used to calculate the difference between two times. So you can use it to calculate age.

TIMESTAMPDIFF() is a built-in date and time function that returns the difference between two date or datetime expressions. It accepts three parameters, the first parameter is the unit of the returned time difference, and the second and third parameters are two datetime values. So if you need to use TIMESTAMPDIFF() to calculate age, you should pass YEAR to the first parameter, the date of birth to the second parameter, and the current date to the third parameter.

Example 1

The statement calculates the age of people born in 1988-12-10:

SELECT TIMESTAMPDIFF(YEAR, '1988-12-10', CURDATE()) AS age;

Output:

+------+
| age  |
+------+
|   33 |
+------+

Here, we have used CURDATE() to return the current date.

Example 2

Suppose we have a table named student and it has the following rows:

SELECT * FROM student;

+------------+------+------------+
| student_id | name | birthday   |
+------------+------+------------+
|          1 | Tim  | 2012-04-03 |
|          2 | Jim  | 2013-05-06 |
|          3 | Lucy | NULL       |
+------------+------+------------+
3 rows in set (0.00 sec)

Let’s use the following statement to return the ages of all students and then sort the results by age descending:

SELECT
  student_id,
  name,
  birthday,
  CURDATE(),
  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age
FROM student
ORDER BY age DESC;

Output:

+------------+------+------------+------------+------+
| student_id | name | birthday   | CURDATE()  | age  |
+------------+------+------------+------------+------+
|          1 | Tim  | 2012-04-03 | 2022-12-06 |   10 |
|          2 | Jim  | 2013-05-06 | 2022-12-06 |    9 |
|          3 | Lucy | NULL       | 2022-12-06 | NULL |
+------------+------+------------+------------+------+
3 rows in set (0.00 sec)

TIMESTAMPDIFF() returns NULL if the parameter is NULL.

Conclusion

This article discussed how to calculate age using the TIMESTAMPDIFF() function.