Calculating age in years from a person’s date of birth is a common requirement when working with database applications. MySQL provides several different functions to perform operations on date data types and calculating a person’s age is achievable using a simple MySQL query.
Although there are several ways to do this, we like to keep SQL queries as efficient and straightforward as possible. Here’s an elegant MySQL Select statement using a combination of just two of the date functions.
SELECT TIMESTAMPDIFF(YEAR, '1973-01-01', CURDATE())
Explanation
The syntax for TIMESTAMPDIFF() is:
TIMESTAMPDIFF(unit, begin_date, end_date)
In the unit argument, we’re using YEAR in this example because we want our result expressed in years. You could modify this argument to any of the following values to return different time units:
YEARQUARTERMONTHWEEKDAYHOURMINUTESECONDMICROSECOND
The begin_date argument contains the date of birth. It can either be specified as a date string as shown in the example, but in practice, you’ll probably be using a column name here (without the single quotes) that references date of birth in a table. This value forms the start of our date range that we want to calculate.
The end_date argument represents the end of our date range. Since we want to know the person’s current age, the end of the date range should be today. The CURDATE() function is a simple way to return the current date and requires no parameters between the parentheses.
