MySQL Cookbook

5.20 Calculating Ages

5.20.1 Problem

You want to know how old someone is.

5.20.2 Solution

This is a problem of 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.

5.20.3 Discussion

Age determination is a type of date interval calculation, but one that cannot be done by computing a difference in days and dividing 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 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.

5.20.4 Determining Ages in Years

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 query 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 here—or 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 we 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))

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 query demonstrates how this formula works to calculate an age as of the beginning 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 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 |
+----------+------------+

The formula produces answers for questions such as the following:

• How old are the Smith children today?

  mysql> SELECT name, birth, CURDATE( ) AS today, 
      -> YEAR(CURDATE( )) - YEAR(birth)
      -> - IF(RIGHT(CURDATE( ),5) < RIGHT(birth,5),1,0)
      -> AS 'age in years'
      -> FROM sibling;
  +----------+------------+------------+--------------+
  | name     | birth      | today      | age in years |
  +----------+------------+------------+--------------+
  | Gretchen | 1942-04-14 | 2002-07-15 |           60 |
  | Wilbur   | 1946-11-28 | 2002-07-15 |           55 |
  | Franz    | 1953-03-05 | 2002-07-15 |           49 |
  +----------+------------+------------+--------------+

• How old were Gretchen and Wilbur when Franz was born?

  mysql> SELECT name, birth, '1953-03-05' AS 'Franz'' birthday', 
      -> YEAR('1953-03-05') - YEAR(birth)
      -> - IF(RIGHT('1953-03-05',5) < RIGHT(birth,5),1,0)
      -> AS 'age in years'
      -> FROM sibling WHERE name != 'Franz';
  +----------+------------+-----------------+--------------+
  | name     | birth      | Franz' birthday | age in years |
  +----------+------------+-----------------+--------------+
  | Gretchen | 1942-04-14 | 1953-03-05      |           10 |
  | Wilbur   | 1946-11-28 | 1953-03-05      |            6 |
  +----------+------------+-----------------+--------------+

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.

5.20.5 Determining Ages in Months

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 | 2002-07-15 |           723 |
| Wilbur   | 1946-11-28 | 2002-07-15 |           667 |
| Franz    | 1953-03-05 | 2002-07-15 |           592 |
+----------+------------+------------+---------------+