This 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.
Age 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:
How old are the Smith children today?
mysql>SELECT name, birth, CURDATE() AS today,
->TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS 'age in years'
->FROM sibling;
+----------+------------+------------+--------------+ | name | birth | today | age in years | +----------+------------+------------+--------------+ | Gretchen | 1942-04-14 | 2006-05-30 | 64 | | Wilbur | 1946-11-28 | 2006-05-30 | 59 | | Franz | 1953-03-05 | 2006-05-30 | 53 | +----------+------------+------------+--------------+
How old were Gretchen and Wilbur when Franz was born?
mysql>SELECT name, birth, '1953-03-05' AS 'Franz'' birthday',
->TIMESTAMPDIFF(YEAR,birth,'1953-03-05') 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 | +----------+------------+-----------------+--------------+
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 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 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:
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 | 2006-05-30 | 64 | | Wilbur | 1946-11-28 | 2006-05-30 | 59 | | Franz | 1953-03-05 | 2006-05-30 | 53 | +----------+------------+------------+--------------+
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. If you need to canonize not-quite-ISO date values, see Canonizing Not-Quite-ISO Date Strings.
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 | +----------+------------+------------+---------------+
18.117.186.46