Calculate a person's age in a MySQL query
Recently a question was posed on the Phpfreaks.com forums, as to the best way for someone to store a user's birth date using the MySQL database. One person suggested using a varchar as they were most familiar with string functions. My answer was to use the MySQL date type.
I provided a quick "advantages of using date" comparison list:
Storing as a DATE
•A MySQL Date requires 3 Bytes of storage
•You can do Date arithmetic inside mysql (search for dates within ranges) and use mysql functions to calculate values directly in a query
•It intrinsically will only store valid dates
•You can format it in numerous ways
Storing as a Varchar
•A string will require minimum 8 bytes, or 10 with separators
•can't do any form of efficient native range queries
•can't reformat it easily in SQL
•will allow completely invalid dates
I thought a great proof of the benefit of this approach was to show that you could have MySQL calculate the person's current age in a query, using their birthday. Certainly with a string, using PHP for example, you would typically query the database to pull out the string and for each row, turn it into a PHP date and do some calculations in your code, and you'd arrive at the same place, but I wanted to show just how capable SQL -- and in this case MySQL can be especially when you use the native data types and some functions.
I provided a quick "advantages of using date" comparison list:
Storing as a DATE
•A MySQL Date requires 3 Bytes of storage
•You can do Date arithmetic inside mysql (search for dates within ranges) and use mysql functions to calculate values directly in a query
•It intrinsically will only store valid dates
•You can format it in numerous ways
Storing as a Varchar
•A string will require minimum 8 bytes, or 10 with separators
•can't do any form of efficient native range queries
•can't reformat it easily in SQL
•will allow completely invalid dates
I thought a great proof of the benefit of this approach was to show that you could have MySQL calculate the person's current age in a query, using their birthday. Certainly with a string, using PHP for example, you would typically query the database to pull out the string and for each row, turn it into a PHP date and do some calculations in your code, and you'd arrive at the same place, but I wanted to show just how capable SQL -- and in this case MySQL can be especially when you use the native data types and some functions.
Calculate a person's age in a MySQL query
I've written previously about CURDATE(), which gives you access to the "current server date" as a basis for comparison calculations.
To calculate a person's current age the simple recipe is "this year" minus "birth year".
The advantage of using a MySQL Date type to store the user birthdates, is that you have simple functions like YEAR() that give you any portion of the date you need.
mysql> select YEAR(CURDATE());
+-----------------+
| YEAR(CURDATE()) |
+-----------------+
| 2011 |
+-----------------+
1 row in set (0.00 sec)
In order to test out this concept, we'll create a simple user table with a key, name and the birthdate.
CREATE TABLE USER (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), birthdate DATE);
mysql> describe user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| birthdate | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.27 sec)
Now we'll add some user rows:
INSERT INTO USER (name, birthdate) VALUES ('Fran', '1967-10-31');
INSERT INTO USER (name, birthdate) VALUES ('Bill', '1964-05-07');
INSERT INTO USER (name, birthdate) VALUES ('Jimmy', '1965-04-27');
INSERT INTO USER (name, birthdate) VALUES ('Stacy', '2002-11-30');
INSERT INTO USER (name, birthdate) VALUES ('George', '2007-10-25');
mysql> select * from user;
+----+--------+------------+
| id | name | birthdate |
+----+--------+------------+
| 1 | Fran | 1967-10-31 |
| 2 | Bill | 1964-05-07 |
| 3 | Jimmy | 1965-04-27 |
| 4 | Stacy | 2002-11-30 |
| 5 | George | 2007-10-25 |
+----+--------+------------+
5 rows in set (0.00 sec)
Our first pass on the age calculation will just involve implementing current year - birth year:
As this tutorial ages, be aware that your results of these queries may vary based on the current date, should you choose to run them yourself (which I highly recommend). These results were relative to:
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2011-08-11 |
+------------+
1 row in set (0.00 sec)
SELECT *, YEAR(CURDATE()) - YEAR(birthdate) AS age FROM USER;
mysql> SELECT *, YEAR(CURDATE()) - YEAR(birthdate) AS age FROM user;
+----+--------+------------+------+
| id | name | birthdate | age |
+----+--------+------------+------+
| 1 | Fran | 1967-10-31 | 44 |
| 2 | Bill | 1964-05-07 | 47 |
| 3 | Jimmy | 1965-04-27 | 46 |
| 4 | Stacy | 2002-11-30 | 9 |
| 5 | George | 2007-10-25 | 4 |
+----+--------+------------+------+
5 rows in set (0.00 sec)
Adjusting the age for someone with an upcoming birthday
Now if you look at these results, you may already have noticed a problem. For example, "George" who was born October 25th of 2007 is not really 4 years old yet because it is only August 11th, and his birthday hasn't occurred.
Therefore George is still 3 years old. So it's important in this calculation to determine whether "today" is less than your birthday anniversary. As I've demonstrated previously, mysql provides a useful ternary function IF() that lets you work around these "if then else" scenarios. But first we need to determine whether a birth date for a user has passed?
What we can do, is have MySQL manufacture a date based on your birth day (month and day) with the current year, and compare that to the curdate(). If the curdate() is > our manufactured "birthday" for a user, we will simply
subtract a year from the age to reflect that their birthday has not yet occurred in the current year.
Assembling a query piece by piece
We'll use STR_TO_DATE (introduced in MySQL version 4.1.1) to create the mysql calculated "birthday" date. STR_TO_DATE takes a string in a format of your choosing and converts it into a mysql Date. We'll use the 'YEAR-MO-DA' with leading zeros for month and day as indicated by the '%Y-%c-%e' parameter. The dashes aren't required, but I've used them to make it easy to compare our manufactured date string to the birthdate column. Internally mysql is working with numbers and the format of the final output is something you can control in your queries using a function like DATE_FORMAT.
SELECT *, STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-', DAY(birthdate)) ,'%Y-%c-%e') AS birthday FROM USER;
mysql> SELECT *, STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-', DAY(birthdate)) ,'%Y-%c-%e') as birthday FROM user;
+----+--------+------------+------------+
| id | name | birthdate | birthday |
+----+--------+------------+------------+
| 1 | Fran | 1967-10-31 | 2011-10-31 |
| 2 | Bill | 1964-05-07 | 2011-05-07 |
| 3 | Jimmy | 1965-04-27 | 2011-04-27 |
| 4 | Stacy | 2002-11-30 | 2011-11-30 |
| 5 | George | 2007-10-25 | 2011-10-25 |
+----+--------+------------+------------+
5 rows in set (0.00 sec)
Now we will utilize IF() to use the comparison and subtract a year if necessary. IF() takes 3 parameters:
•Condition to check
•Value to return if condition is true
•Value to return if condition is false
Just for visual verification, let's start by adding the ternary function to the end of the prior query. One will indicate the user's birthday hasn't occurred, and zero will indicate it has. Of course these are also the values
we will subtract for the result of the age calculation to fix it!
SELECT *, STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-', DAY(birthdate)) ,'%Y-%c-%e') AS birthday,
IF(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-', DAY(birthdate)) ,'%Y-%c-%e') > CURDATE(), 1, 0) AS adjustment
FROM USER;
+----+--------+------------+------------+------------+
| id | name | birthdate | birthday | adjustment |
+----+--------+------------+------------+------------+
| 1 | Fran | 1967-10-31 | 2011-10-31 | 1 |
| 2 | Bill | 1964-05-07 | 2011-05-07 | 0 |
| 3 | Jimmy | 1965-04-27 | 2011-04-27 | 0 |
| 4 | Stacy | 2002-11-30 | 2011-11-30 | 1 |
| 5 | George | 2007-10-25 | 2011-10-25 | 1 |
+----+--------+------------+------------+------------+
5 rows in set (0.00 sec)
The 3 people who have birthdays in October & November should have their ages adjusted correctly. We add that to the original age calculation and now our ages are accurate:
SELECT *, YEAR(CURDATE()) -
YEAR(birthdate) -
IF(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-', DAY(birthdate)) ,'%Y-%c-%e') > CURDATE(), 1, 0)
AS age FROM USER;
+----+--------+------------+------+
| id | name | birthdate | age |
+----+--------+------------+------+
| 1 | Fran | 1967-10-31 | 43 |
| 2 | Bill | 1964-05-07 | 47 |
| 3 | Jimmy | 1965-04-27 | 46 |
| 4 | Stacy | 2002-11-30 | 8 |
| 5 | George | 2007-10-25 | 3 |
+----+--------+------------+------+
5 rows in set (0.00 sec)
We are calculating Age based on the mysql birthdate date column!
Leap Year
Leap years in the Gregorian Calender are an adjustment to synchronize our 365 day calender to reality since it actually takes a bit more than 365 days for the earth to circle the sun once. The math works out so that you
simply need to add one day every 4 years, and that day is February 29th. 2012 is a leap year because it is evenly divisible by 4. Leap year rules aren't quite that simple unfortunately:
•if the year is a centennial year, it is not a leap year
•unless that year is evenly divisible by 400
•otherwise, if evenly divisible by 4 it is a leap year
There is one edge case that complicates this task. People born on February 29th of a leap year, only have a birthday on their actual "birth day" once every 4 years. Typically the solution for people born on a leap
year is to celebrate their birthday on the last day of February be it the 28th or the 29th. For our age calculation, this is really only a problem on one day a year. We could for example, just have the query assume
that if you're a leap year baby, we will just consider your birth date to be the 28th, and your age calculation will only be incorrect for one day every 4 years. This is a tradeoff that could easily be justified. As shown
above the query will work even for February 29th babies, surprisingly enough, even though we're using an invalid date for the purposes of comparing them to the curdate(). MySQL can however, do better than that.
Adding to the calculation to handle Leap Year babies
Let's add a leap year baby to the database so we can test things out:
INSERT INTO USER (name, birthdate) VALUES ('Leapie', '2008-02-29');
mysql> insert into user (name, birthdate) values ('Leapie', '2008-02-29');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+------------+
| id | name | birthdate |
+----+--------+------------+
| 1 | Fran | 1967-10-31 |
| 2 | Bill | 1964-05-07 |
| 3 | Jimmy | 1965-04-27 |
| 4 | Stacy | 2002-11-30 |
| 5 | George | 2007-10-25 |
| 6 | Leapie | 2008-02-29 |
+----+--------+------------+
6 rows in set (0.00 sec)
What happens if you try and insert an invalid date? MySQL will generate a warning, and create a date full of zeros. It will not store an invalid date unless you configure it to allow invalid dates. So if you try and insert a birthdate of February 29th 2011, mysql won't let you.
mysql> insert into user (name, birthdate) values ('BadLeap', '2011-02-29');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'birthdate' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+---------+------------+
| id | name | birthdate |
+----+---------+------------+
| 1 | Fran | 1967-10-31 |
| 2 | Bill | 1964-05-07 |
| 3 | Jimmy | 1965-04-27 |
| 4 | Stacy | 2002-11-30 |
| 5 | George | 2007-10-25 |
| 6 | Leapie | 2008-02-29 |
| 7 | BadLeap | 0000-00-00 |
+----+---------+------------+
7 rows in set (0.00 sec)
Surprisingly, MySQL will allow you to construct invalid dates and even use them with STR_TO_DATE, even though it won't allow an invalid date to be stored:
mysql> SELECT STR_TO_DATE('2011-02-29', '%Y-%c-%e') as birthday;
+------------+
| birthday |
+------------+
| 2011-02-29 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-02-31', '%Y-%c-%e') as birthday;
+------------+
| birthday |
+------------+
| 2011-02-31 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-09-30', '%Y-%c-%e') as birthday;
+------------+
| birthday |
+------------+
| 2011-09-30 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-09-31', '%Y-%c-%e') as birthday;
+------------+
| birthday |
+------------+
| 2011-09-31 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-09-32', '%Y-%c-%e') as birthday;
+----------+
| birthday |
+----------+
| NULL |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-13-32', '%Y-%c-%e') as birthday;
+----------+
| birthday |
+----------+
| NULL |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-13-11', '%Y-%c-%e') as birthday;
+----------+
| birthday |
+----------+
| NULL |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT STR_TO_DATE('2011-00-11', '%Y-%c-%e') as birthday;
+------------+
| birthday |
+------------+
| 2011-00-11 |
+------------+
1 row in set (0.00 sec)
This is partly design, and partly a configuration issue. Current versions of MySQL allow you to tweak settings in the server that dictate how it will handle invalid dates. Much of the concern over Leap years is academic.
We don't really need to worry about leap year rules other than when the year is evenly divisible by 4, and even then due to the fact that we can safely construct an invalid date for comparison purposes, even without
a leap year adjustment, the age calculation for a leap year birthday will only be incorrect for one day a year, even if we just use the query provided above that ignores the February 29th problem.
There are at least a couple of different ways we could fix this:
•Determine if the current year is a non-leap year and use the 28th
•Have mysql figure out what the last day of February is for the current year and use that as the day.
In either case, we will need to add to the existing query using the ternary IF().
Continued Here:
Defined tags for this entry: concat, curdate, date_add, day, month, mysql, mysql if(), sql, str_to_date, year
Comments
Display comments as Linear | Threaded
Jay on :
This also takes in to account the leap year and if you want the user's exact age you can just remove the FLOOR().
James Mickle on :
Alvin567 on :
Barry Andrew (Barand) on :
SELECT TIMESTAMPDIFF(YEAR, dob, CURDATE()) as age
stan walls on :
sql is kicking my butt!!