How to calculate age in MySQL
While PostgreSQL has a built in age() command, MySQL does not. Imagine we have a users table with a birthdate field and we need to figure out how old they are. We could accomplish this by subtracting the current date with the date the user was born on.
Let’s say the John Doe, was born on 1965-02-16, and today is 2019-04-15:
>SELECT birthdate FROM users WHERE first_name='John';
| birthdate |
| ---------- |
| 1965-02-16 |
We could try subtracting CURDATE() from birthdate. Let’s plug this raw value into a subtraction query with CURDATE():
>SELECT (CURDATE() - birthdate) FROM users WHERE first_name='John';
| (CURDATE() - birthdate) |
| ----------------------- |
| 540199 |
We seem to have an issue - MySQL dates are subtracted as ints in the form of YYYYMMDD. What do we do with this? We know the age will be 54, and we received 540199, so maybe we can divide it by 10000 to get the significant digits we care about.
>SELECT ((CURDATE() - birthdate) / 10000) FROM users WHERE first_name='John';
| ((CURDATE() - birthdate) / 10000) |
| --------------------------------- |
| 54.0199 |
Okay, nice. This is looking better, but nobody is 54.0199. Let’s FLOOR the result.
>SELECT FLOOR((CURDATE() - birthdate) / 10000) FROM users WHERE first_name='John';
| FLOOR((CURDATE() - birthdate) / 10000) |
| -------------------------------------- |
| 54 |
Perfect.
Since the date is returned as an int in the form of YYYYMMDD, dividing it by 10000 gives us only the first four significant digits. With this, we receive the age of 54.0199. We need to round down the age because nobody is 54.0199 years old. FLOOR rounds down the age to 54.
Thus, the command to find a user’s age is:
SELECT FLOOR((CURDATE() - birthdate) / 10000) FROM users
| first_name | last_name | birthdate | CURDATE() | CURDATE() - birthdate | ((CURDATE() - birthdate) / 10000) | FLOOR((CURDATE() - birthdate) / 10000) |
| ---------- | --------- | ---------- | ---------- | --------------------- | --------------------------------- | -------------------------------------- |
| John | Smith | 1965-02-16 | 2019-04-15 | 540199 | 54.0199 | 54 |
| Jane | Smith | 1965-12-09 | 2019-04-15 | 539206 | 53.9206 | 53 |
| Joe | Smith | 1996-04-06 | 2019-04-15 | 230009 | 23.0009 | 23 |
Here is a dbfiddle where you can play with the data we used in this example.