Recipe 6.11. Calculating Ages
ProblemYou want to know how old someone is. SolutionThis is a date-arithmetic problem. It amounts to computing the interval between dates, but with a twist. For an age in years, it's necessary to account for the relative placement of the start and end dates within the calendar year. For an age in months, it's also necessary to account for the placement of the months and the days within the month. DiscussionAge determination is a type of date interval calculation, but you cannot simply compute a difference in days and divide by 365. That doesn't work because leap years throw off the calculation. (The interval from 1995-03-01 to 1996-02-29 spans 365 days, but is not a year in age terms.) Using 365.25 is slightly more accurate, but still not correct for all dates. Instead, it's necessary to determine the difference between dates in years and then adjust for the relative location of the dates within the calendar year. (Suppose that Gretchen Smith was born on April 14, 1942. To compute how old Gretchen is now, we must account for where the current date falls within the calendar year: She's one age up through April 13 of the year, and one year older from April 14 through the end of the year.) This section shows how to perform this kind of calculation to determine ages in units of years or months. The easiest way to calculate ages is to use the TIMESTAMPDIFF( ) function because you can pass it a birth date, a current date, and the unit in which you want the age to be expressed: TIMESTAMPDIFF(unit,birth,current) TIMESTAMPDIFF( ) handles the calculations necessary to adjust for differing month and year lengths and relative positions of the dates within the calendar year. Suppose that we have a sibling table that lists the birth dates of Gretchen Smith and her brothers Wilbur and Franz: +----------+------------+ | name | birth | +----------+------------+ | Gretchen | 1942-04-14 | | Wilbur | 1946-11-28 | | Franz | 1953-03-05 | +----------+------------+ Using TIMESTAMPDIFF( ), we can answer questions such as these:
The preceding queries produce ages in years, but you can request other interval units if you like. For example, the current ages of the Smith children in months can be calculated like this: mysql> SELECT name, birth, CURDATE() AS today, -> TIMESTAMPDIFF(MONTH,birth,CURDATE()) AS 'age in months' -> FROM sibling; +----------+------------+------------+---------------+ | name | birth | today | age in months | +----------+------------+------------+---------------+ | Gretchen | 1942-04-14 | 2006-05-30 | 769 | | Wilbur | 1946-11-28 | 2006-05-30 | 714 | | Franz | 1953-03-05 | 2006-05-30 | 638 | +----------+------------+------------+---------------+ TIMESTAMPDIFF( ) requires MySQL 5.0 or higher. For older versions of MySQL, you can calculate ages without it, but as the following discussion shows, it's necessary to do more work. In general, given a birth date birth, an age in years on a target date d can be computed by the following logic: if (d occurs earlier in the year than birth) age = YEAR(d) - YEAR(birth) - 1 if (d occurs on or later in the year than birth) age = YEAR(d) - YEAR(birth) For both cases, the difference-in-years part of the calculation is the same. What distinguishes them is the relative ordering of the dates within the calendar year. However, this ordering cannot be determined with DAYOFYEAR( ), because that only works if both dates fall during years with the same number of days. For dates in different years, different calendar days may have the same DAYOFYEAR( ) value, as the following statement illustrates: mysql> SELECT DAYOFYEAR('1995-03-01'), DAYOFYEAR('1996-02-29'); +-------------------------+-------------------------+ | DAYOFYEAR('1995-03-01') | DAYOFYEAR('1996-02-29') | +-------------------------+-------------------------+ | 60 | 60 | +-------------------------+-------------------------+ The fact that ISO date strings compare naturally in the proper order comes to our rescue hereor more precisely, the fact that the rightmost five characters that represent the month and day also compare properly: mysql> SELECT RIGHT('1995-03-01',5), RIGHT('1996-02-29',5); +-----------------------+-----------------------+ | RIGHT('1995-03-01',5) | RIGHT('1996-02-29',5) | +-----------------------+-----------------------+ | 03-01 | 02-29 | +-----------------------+-----------------------+ mysql> SELECT IF('02-29' < '03-01','02-29','03-01') AS earliest; +----------+ | earliest | +----------+ | 02-29 | +----------+ This means that you can perform the "earlier-in-year" test for two dates, d1 and d2, like this: RIGHT(d2,5) < RIGHT(d1,5) The expression evaluates to 1 or 0, depending on the result of the test, so the result of the < comparison can be used to perform an age-in-years calculation: YEAR(d2) - YEAR(d1) - (RIGHT(d2,5) < RIGHT(d1,5)) If you want to make it more obvious what the comparison result evaluates to, wrap it in an IF( ) function that explicitly returns 1 or 0: YEAR(d2) - YEAR(d1) - IF(RIGHT(d2,5) < RIGHT(d1,5),1,0) The following statement demonstrates how this formula works to calculate an age as of the beginning of 1975 for someone born on 1965-03-01. It shows the unadjusted age difference in years, the adjustment value, and the final age: mysql> SET @birth = '1965-03-01'; mysql> SET @target = '1975-01-01'; mysql> SELECT @birth, @target, -> YEAR(@target) - YEAR(@birth) AS 'difference', -> IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0) AS 'adjustment', -> YEAR(@target) - YEAR(@birth) -> - IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0) -> AS 'age'; +------------+------------+------------+------------+------+ | @birth | @target | difference | adjustment | age | +------------+------------+------------+------------+------+ | 1965-03-01 | 1975-01-01 | 10 | 1 | 9 | +------------+------------+------------+------------+------+ Let's try the age-in-years formula with the sibling table. We can check the formula using the same questions that we answered earlier with TIMESTAMPDIFF( ). The formula produces answers for questions such as the following:
When performing calculations of this nature, be sure to remember that for comparisons on the MM-DD part of date strings to yield correct results, you must use ISO values like 1987-07-01 and not close-to-ISO values like 1987-7-1. For example, the following comparison produces a result that is correct in lexical terms but incorrect in temporal terms: mysql> SELECT RIGHT('1987-7-1',5) < RIGHT('1987-10-01',5); +---------------------------------------------+ | RIGHT('1987-7-1',5) < RIGHT('1987-10-01',5) | +---------------------------------------------+ | 0 | +---------------------------------------------+ The absence of leading zeros in the month and day parts of the first date makes the substring-based comparison fail. If you need to canonize not-quite-ISO date values, see Section 6.18. The formula for calculating ages in months is similar to that for ages in years, except that we multiply the years difference by 12, add the months difference, and adjust for the relative day-in-month values of the two dates. In this case, we need to use the month and day part of each date separately, so we may as well extract them directly using MONTH( ) and DAYOFMONTH( ) rather than performing a comparison on the MM-DD part of the date strings. The current ages of the Smith children in months thus can be calculated like this: mysql> SELECT name, birth, CURDATE() AS today, -> (YEAR(CURDATE()) - YEAR(birth)) * 12 -> + (MONTH(CURDATE()) - MONTH(birth)) -> - IF(DAYOFMONTH(CURDATE()) < DAYOFMONTH(birth),1,0) -> AS 'age in months' -> FROM sibling; +----------+------------+------------+---------------+ | name | birth | today | age in months | +----------+------------+------------+---------------+ | Gretchen | 1942-04-14 | 2006-05-30 | 769 | | Wilbur | 1946-11-28 | 2006-05-30 | 714 | | Franz | 1953-03-05 | 2006-05-30 | 638 | +----------+------------+------------+---------------+ |
