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:
YEAR
QUARTER
MONTH
WEEK
DAY
HOUR
MINUTE
SECOND
MICROSECOND
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.