Calculate a person's age in a MySQL query -- continued Fri, Aug 19. 2011
The serendipity cache blew up on me for this article so I had to split it into 2 parts.
MySQL knows how to work with the Gregorian calender, and I believe it is easier to let MySQL do the work, rather than trying to figure out how to implement the different cases. I chose to implement the "last day of the month" technique. In this case, we'll do this by creating a mysql date based on March 1st of the current year, and subtract one day from it to get to the last day of February.
If you read my article on "Finding next monday" using mysql date functions, you would have seen how DATE_ADD() can be used to help solve a lot of problems, even though the name can be misleading when you are actually using it to subtract. Using it with the following test dates proves that mysql has fully implemented the leap year logic accurately:
DATE_ADD to the rescue
MySQL knows how to work with the Gregorian calender, and I believe it is easier to let MySQL do the work, rather than trying to figure out how to implement the different cases. I chose to implement the "last day of the month" technique. In this case, we'll do this by creating a mysql date based on March 1st of the current year, and subtract one day from it to get to the last day of February.
If you read my article on "Finding next monday" using mysql date functions, you would have seen how DATE_ADD() can be used to help solve a lot of problems, even though the name can be misleading when you are actually using it to subtract. Using it with the following test dates proves that mysql has fully implemented the leap year logic accurately:
mysql> select DATE_ADD('2011-03-01', INTERVAL -1 DAY);
+-----------------------------------------+
| DATE_ADD('2011-03-01', INTERVAL -1 DAY) |
+-----------------------------------------+
| 2011-02-28 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_ADD('2012-03-01', INTERVAL -1 DAY);
+-----------------------------------------+
| DATE_ADD('2012-03-01', INTERVAL -1 DAY) |
+-----------------------------------------+
| 2012-02-29 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_ADD('2000-03-01', INTERVAL -1 DAY);
+-----------------------------------------+
| DATE_ADD('2000-03-01', INTERVAL -1 DAY) |
+-----------------------------------------+
| 2000-02-29 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_ADD('1900-03-01', INTERVAL -1 DAY);
+-----------------------------------------+
| DATE_ADD('1900-03-01', INTERVAL -1 DAY) |
+-----------------------------------------+
| 1900-02-28 |
+-----------------------------------------+
1 row in set (0.00 sec)
So all we need to do is build the correct string using CONCAT() to pass to DATE_ADD which we can do easily using YEAR() along with our hardwired March 1st day.
SELECT CONCAT(YEAR(CURDATE()), '-03-01');
+-----------------------------------+
| CONCAT(YEAR(CURDATE()), '-03-01') |
+-----------------------------------+
| 2011-03-01 |
+-----------------------------------+
1 row in set (0.00 sec)
This will always produce a valid mysql date string equivalent to March 1st for the current year. Now we substitute it into the DATE_ADD() function:
SELECT DATE_ADD(CONCAT(YEAR(CURDATE()), '-03-01'), INTERVAL -1 DAY);
+--------------------------------------------------------------+
| DATE_ADD(CONCAT(YEAR(CURDATE()), '-03-01'), INTERVAL -1 DAY) |
+--------------------------------------------------------------+
| 2011-02-28 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
And we'll only want the DAY() when we actually use this in the query:
SELECT DAY(DATE_ADD(CONCAT(YEAR(CURDATE()), '-03-01'), INTERVAL -1 DAY));
+-------------------------------------------------------------------+
| DAY(DATE_ADD(CONCAT(YEAR(CURDATE()), '-03-01'), INTERVAL -1 DAY)) |
+-------------------------------------------------------------------+
| 28 |
+-------------------------------------------------------------------+
Then we'll need to add a condition so we only return this calculation if the user's birthday is on Feb 29th.
SELECT *, IF(MONTH(birthdate) = 2 AND DAY(birthdate) = 29, 'Leap', 'Normal') as leaper FROM user;
+----+---------+------------+--------+
| id | name | birthdate | leaper |
+----+---------+------------+--------+
| 1 | Fran | 1967-10-31 | Normal |
| 2 | Bill | 1964-05-07 | Normal |
| 3 | Jimmy | 1965-04-27 | Normal |
| 4 | Stacy | 2002-11-30 | Normal |
| 5 | George | 2007-10-25 | Normal |
| 6 | Leapie | 2008-02-29 | Leap |
| 7 | BadLeap | 0000-00-00 | Normal |
+----+---------+------------+--------+
7 rows in set (0.00 sec)
Since we can see that our condition works properly, all that is left is to plug the proper functions in place of 'Leap' and 'Normal'. Leap will be our "Day before March 1st" calculation, and 'Normal' will be the original DAY(birthdate). It may look cryptic when finished but you can break it down into its component parts, as I've shown in this article, and hopefully it's clear that you can solve relatively complex problems using a series of simple functions glued together when needed using the mysql ternary function.
SELECT *, YEAR(CURDATE()) -
YEAR(birthdate) -
IF(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-',
IF(MONTH(birthdate) = 2 AND DAY(birthdate) = 29,
DAY(DATE_ADD(CONCAT(YEAR(CURDATE()), '-03-01'), INTERVAL -1 DAY)),
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 |
| 6 | Leapie | 2008-02-29 | 3 |
| 7 | BadLeap | 0000-00-00 | 2011 |
+----+---------+------------+------+
7 rows in set (0.00 sec)
Testing the edge case
It's a little hard to prove that this will actually work correctly for our edge case, so we have to artificially substitute leap year days for curdate() to test it, which is annoying but necessary. Search and replace certainly helps.
SELECT *, YEAR('2011-02-28') -
YEAR(birthdate) -
IF(STR_TO_DATE(CONCAT(YEAR('2011-02-28'), '-', MONTH(birthdate), '-',
IF(MONTH(birthdate) = 2 AND DAY(birthdate) = 29,
DAY(DATE_ADD(CONCAT(YEAR('2011-02-28'), '-03-01'), INTERVAL -1 DAY)),
DAY(birthdate))
) ,'%Y-%c-%e') > '2011-02-28', 1, 0)
AS age FROM user where id = 6;
+----+--------+------------+------+
| id | name | birthdate | age |
+----+--------+------------+------+
| 6 | Leapie | 2008-02-29 | 3 |
+----+--------+------------+------+
1 row in set (0.00 sec)
This shows that even when the date is the 28th in a non-leap year, the calculation correctly determines that it should be treated as the user's birthday.
What about 2012, which is a Leap year?
SELECT *, YEAR('2012-02-28') -
YEAR(birthdate) -
IF(STR_TO_DATE(CONCAT(YEAR('2012-02-28'), '-', MONTH(birthdate), '-',
IF(MONTH(birthdate) = 2 AND DAY(birthdate) = 29,
DAY(DATE_ADD(CONCAT(YEAR('2012-02-28'), '-03-01'), INTERVAL -1 DAY)),
DAY(birthdate))
) ,'%Y-%c-%e') > '2012-02-28', 1, 0)
AS age FROM user where id = 6;
+----+--------+------------+------+
| id | name | birthdate | age |
+----+--------+------------+------+
| 6 | Leapie | 2008-02-29 | 3 |
+----+--------+------------+------+
1 row in set (0.00 sec)
It correctly determines that 'Leapie' is still 3! The following day however, Leapie enjoys a Leap year birthday party:
SELECT *, YEAR('2012-02-29') -
YEAR(birthdate) -
IF(STR_TO_DATE(CONCAT(YEAR('2012-02-29'), '-', MONTH(birthdate), '-',
IF(MONTH(birthdate) = 2 AND DAY(birthdate) = 29,
DAY(DATE_ADD(CONCAT(YEAR('2012-02-29'), '-03-01'), INTERVAL -1 DAY)),
DAY(birthdate))
) ,'%Y-%c-%e') > '2012-02-29', 1, 0)
AS age FROM user where id = 6;
+----+--------+------------+------+
| id | name | birthdate | age |
+----+--------+------------+------+
| 6 | Leapie | 2008-02-29 | 4 |
+----+--------+------------+------+
1 row in set (0.00 sec)
Summary
MySQL can be used to calculate a person's accurate age for any day, automatically, even for people born on February 29th in a Leap Year. It is fast, and allows you to harness the power of the MySQL date type and associated functions so that you don't have to perform these calculations in serverside code. Here's the query one more time:
SELECT *, YEAR(CURDATE()) -
YEAR(birthdate) -
IF(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-',
IF(MONTH(birthdate) = 2 AND DAY(birthdate) = 29,
DAY(DATE_ADD(CONCAT(YEAR(CURDATE()), '-03-01'), INTERVAL -1 DAY)),
DAY(birthdate))
) ,'%Y-%c-%e') > CURDATE(), 1, 0)
AS age FROM user;
Defined tags for this entry: concat, curdate, date_add, day, month, mysql, mysql if(), sql, str_to_date, year
Related entries by tags:
- Calculate a person's age in a MySQL query
- SQL UPDATE for strings in MongoDB
- Finding "Next Monday" using MySQL Dates
- Centos Virtual LAMP server -- Part II
- Too much information about the MySQL TIMESTAMP
- Mysql Update: Null + 1 is Null!
- LAMP Tutorial Series originally published on PHPFreaks.com
- PHPlot, MySQL and the Dark Ages of Camelot
Trackbacks
Trackback specific URI for this entry
No Trackbacks
