Working with Dates and Times - MySQL - page 215

+------------+---------------+---------------+
| 1980-01-01 | 1980-01-08 | 1979-12-25 |
+------------+---------------+---------------+
TO_DAYS() also can convert date-and-time values to days, if you don't mind having it
chop off the time part.
To preserve the time, you can use UNIX_TIMESTAMP() and FROM_UNIXTIME() instead, if
the initial and resulting values both lie in the permitted range for TIMESTAMP values
(from 1970 partially through 2038). The following statement shifts a DATETIME value
forward and backward by an hour (3,600 seconds):
mysql> SET @dt = '1980-01-01 09:00:00';
mysql> SELECT @dt AS datetime,
-> FROM_UNIXTIME(UNIX_TIMESTAMP(@dt) + 3600) AS 'datetime + 1 hour',
-> FROM_UNIXTIME(UNIX_TIMESTAMP(@dt) - 3600) AS 'datetime - 1 hour';
+---------------------+---------------------+---------------------+
| datetime | datetime + 1 hour | datetime - 1 hour |
+---------------------+---------------------+---------------------+
| 1980-01-01 09:00:00 | 1980-01-01 10:00:00 | 1980-01-01 08:00:00 |
+---------------------+---------------------+---------------------+
6.13. 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 nec‐
essary to account for the placement of the months and the days within the month.
Discussion
Age determination is a type of date-interval calculation. However, you cannot simply
compute a difference in days and divide by 365 because leap years throw off the calcu‐
lation. (It is 365 days from 1995-03-01 to 1996-02-29, but that is not a year in age terms.)
Dividing by 365.25 is slightly more accurate, but still not correct for all dates.
To calculate ages, use the TIMESTAMPDIFF() function. Pass it a birth date, a current date,
and the unit in which you want the age expressed:
TIMESTAMPDIFF( unit , birth , current )