Rietta.com Security logo
You are reading The Rietta Blog, a publication about the web since 2005.

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:

1
2
3
4
5
>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():

1
2
3
4
5
>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.

1
2
3
4
5
>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.

1
2
3
4
5
>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

1
2
3
4
5
| 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.

About Matt Bettinson

Matt Bettinson's photo

Matt is a software developer from Canada. When he's not programming, he enjoys taking pictures of things and riding his bike.

His public key is available at mattbettinson on keybase.

If there is a topic you would like us to cover,