MySQL Cookbook, 2nd Edition

Calculating Ages

Problem

You want to know how old someone is.

Solution

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.

Discussion

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: ...