Skip to content

Calculate a person's age in a MySQL query -- continued

The serendipity cache blew up on me for this article so I had to split it into 2 parts.

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 server-side 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;
 


Does it really have to be this complicated?


My primary goal with this article was to demonstrate some of the functions and features available to you within the context of a MySQL query. But the reality is there are far simpler queries you can use to derive current age from a MySQL Date.

The first query comes from Barry Andrews (Barand at phpfreaks.com):

He points out that MySQL has the TIMESTAMPDIFF function, which takes 2 DateTime/Timestamp values and computes the difference between them. Since TIMESTAMPDIFF utilizes internal timestamp calculations, this function is able to return the difference in a number of different quantities.

This technique utilizes the fact that MySQL can up/downscale Date values to DateTime/Timestamp values as required. If you furnish a Date where MySQL wants a DateTime, it will set the time to be "start of day" or "00:00:00". In this use case, time does not matter. If you are creating a scheduling or timing application where hours/minutes/seconds/microseconds are important, you might need to be aware of these differences. I talked about how MySQL upscales dates in this article.

MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.


In this case, it should be obvious we want YEAR.

SELECT *, TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE) AS age FROM USER


Another alternative technique comes from reader "Jay":

Jay utilized the observation that the Julian and Gregorian Calendars were based on a Solar year, which takes 365.2422 "days" for the earth to travel around the sun.

So you can determine a person's age by taking the number of days between their birthdate and today, and dividing that number by 365.25 (or if you plan to use the fractional part, 365.2425).

MySQL has the DATEDIFF function, which helpfully returns the number of Days between 2 Dates.

You can take this value, divide by 365.25 and throw away the fractional part using FLOOR() to calculate the person's age.

SELECT *, FLOOR(DATEDIFF(CURRENT_DATE, birthdate)/365.25) AS age FROM USER

Defined tags for this entry: , , , , , , , , ,

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

Pavatar, Gravatar, Favatar, MyBlogLog, Pavatar author images supported.
BBCode format allowed
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
Form options