MySQL Cookbook, 2nd Edition

Calculating the Interval Between Two Dates or Times

Problem

You want to know how long it is between two dates or times. That is, you want to know the interval between two temporal values.

Solution

To calculate an interval, either use one of the temporal-difference functions, or convert your values to basic units and take the difference. The allowable functions depend on the types of the values for which you want to know the interval.

Discussion

The following discussion shows several ways to perform interval calculations.

Calculating intervals with temporal-difference functions

To calculate an interval in days between two date values, use the DATEDIFF() function:

mysql>SET @d1 = '2010-01-01', @d2 = '2009-12-01';
mysql> SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';
+---------+---------+
| d1 - d2 | d2 - d1 |
+---------+---------+
|      31 |     -31 |
+---------+---------+

DATEDIFF() also works with date-and-time values, but it ignores the time part. This makes it suitable for producing day intervals for DATE, DATETIME, or TIMESTAMP values.

To calculate an interval between TIME values as another TIME value, use the TIMEDIFF() function:

mysql>SET @t1 = '12:00:00', @t2 = '16:30:00';
mysql> SELECT TIMEDIFF(@t1,@t2) AS 't1 - t2', TIMEDIFF(@t2,@t1) AS 't2 - t1';
+-----------+----------+
| t1 - t2   | t2 - t1  |
+-----------+----------+
| -04:30:00 | 04:30:00 |
+-----------+----------+

TIMEDIFF() also works for date-and-time values. That it, it accepts either time or date-and-time values, ...