Working with Dates and Times - MySQL - page 209

For date-and-time values occurring within the TIMESTAMP range from 1970 partially
through 2038, you can determine intervals to a resolution in seconds using the
UNIX_TIMESTAMP() function. For intervals in other units, seconds are easily converted
to minutes, hours, days, or weeks, as this expression shows for dates that lie two weeks
apart:
mysql> SET @dt1 = '1984-01-01 09:00:00';
mysql> SET @dt2 = @dt1 + INTERVAL 14 DAY;
mysql> SET @interval = UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1);
mysql> SELECT @interval AS seconds,
-> @interval / 60 AS minutes,
-> @interval / (60 * 60) AS hours,
-> @interval / (24 * 60 * 60) AS days,
-> @interval / (7 * 24 * 60 * 60) AS weeks;
+---------+------------+----------+---------+--------+
| seconds | minutes | hours | days | weeks |
+---------+------------+----------+---------+--------+
| 1209600 | 20160.0000 | 336.0000 | 14.0000 | 2.0000 |
+---------+------------+----------+---------+--------+
Use FLOOR() on the division results if you prefer integer values that have no fractional
part.
For values that occur outside the TIMESTAMP range, this interval calculation method is
more general (but messier):
1. Take the difference in days between the date parts of the values and multiply by 24
× 60 × 60 to convert to seconds.
2. Adjust the result by the difference in seconds between the time parts of the values.
Here's an example, using two date-and-time values that lie slightly less than three days
apart:
mysql> SET @dt1 = '1800-02-14 07:30:00';
mysql> SET @dt2 = '1800-02-17 06:30:00';
mysql> SET @interval =
-> ((TO_DAYS(@dt2) - TO_DAYS(@dt1)) * 24*60*60)
-> + TIME_TO_SEC(@dt2) - TIME_TO_SEC(@dt1);
mysql> SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;
+---------+----------+
| seconds | TIME |
+---------+----------+
| 255600 | 71:00:00 |
+---------+----------+