Recipe 6.9. 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, but the types of the arguments must match.

A time interval expressed as a TIME value can be broken down into components using the techniques shown in Section 6.6. For example, to express a time interval in terms of its constituent hours, minutes, and seconds values, calculate time interval subparts in SQL using the HOUR⁠(⁠ ⁠ ⁠), MINUTE⁠(⁠ ⁠ ⁠), and SECOND⁠(⁠ ⁠ ⁠) functions. (Don't forget that if your intervals may be negative, you need to take that into account.) To determine the components of the interval between the t1 and t2 columns in the time_val table, the following SQL statement does the trick:

mysql> SELECT t1, t2,     -> TIMEDIFF(t2,t1) AS 't2 - t1 as TIME',     -> IF(TIMEDIFF(t2,t1) >= 0,'+','-') AS sign,     -> HOUR(TIMEDIFF(t2,t1)) AS hour,     -> MINUTE(TIMEDIFF(t2,t1)) AS minute,     -> SECOND(TIMEDIFF(t2,t1)) AS second     -> FROM time_val; +----------+----------+-----------------+------+------+--------+--------+ | t1       | t2       | t2 - t1 as TIME | sign | hour | minute | second | +----------+----------+-----------------+------+------+--------+--------+ | 15:00:00 | 15:00:00 | 00:00:00        | +    |    0 |      0 |      0 | | 05:01:30 | 02:30:20 | -02:31:10       | -    |    2 |     31 |     10 | | 12:30:20 | 17:30:45 | 05:00:25        | +    |    5 |      0 |     25 | +----------+----------+-----------------+------+------+--------+--------+

If you're working with date or date-and-time values, the TIMESTAMPDIFF⁠(⁠ ⁠ ⁠) function provides another way to calculate intervals, and it enables you to specify the units in which intervals should be expressed. It has this syntax:

TIMESTAMPDIFF(unit,val1,val2)

unit is the interval unit and val1 and val2 are the values between which to calculate the interval. With TIMESTAMPDIFF⁠(⁠ ⁠ ⁠), you can express an interval many different ways:

mysql> SET @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00'; mysql> SELECT     -> TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,     -> TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,     -> TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,     -> TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,     -> TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years; +---------+-------+------+-------+-------+ | minutes | hours | days | weeks | years | +---------+-------+------+-------+-------+ | 5258880 | 87648 | 3652 |   521 |    10 | +---------+-------+------+-------+-------+

The allowable unit specifiers are FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Note that each of these unit specifiers is given in singular form, not plural.

Be aware of these properties of TIMESTAMPDIFF⁠(⁠ ⁠ ⁠):

Its value is negative if the first temporal value is greater then the second, which is opposite the order of the arguments for DATEDIFF⁠(⁠ ⁠ ⁠) and TIMEDIFF⁠(⁠ ⁠ ⁠).
Despite the TIMESTAMP in its name, the TIMESTAMPDIFF⁠(⁠ ⁠ ⁠) function arguments are not limited to the range of the TIMESTAMP data type.
TIMESTAMPDIFF⁠(⁠ ⁠ ⁠) requires MySQL 5.0 or higher. For older versions of MySQL, use one of the other interval-calculation techniques described in this section.

Calculating intervals using basic units

Another strategy for calculating intervals is to work with basic units such as seconds or days using this strategy:

Convert the temporal values that you're working with to basic units.
Take the difference between the values to calculate the interval, also in basic units.
If you want the result as a temporal value, convert it from basic units to the appropriate type.

The conversion functions involved in implementing this strategy depend on the types of the values between which you're calculating the interval:

To convert between time values and seconds, use TIME_TO_SEC⁠(⁠ ⁠ ⁠) and SEC_TO_TIME⁠(⁠ ⁠ ⁠).
To convert between date values and days, use TO_DAYS⁠(⁠ ⁠ ⁠) and FROM_DAYS⁠(⁠ ⁠ ⁠).
To convert between date-and-time values and seconds, use UNIX_TIMESTAMP⁠(⁠ ⁠ ⁠) and FROM_UNIXTIME⁠(⁠ ⁠ ⁠).

For more information about those conversion functions (and limitations on their applicability), see the discussion in Section 6.8. The following material assumes familiarity with that discussion.

Time interval calculation using basic units

To calculate intervals in seconds between pairs of time values, convert them to seconds with TIME_TO_SEC⁠(⁠ ⁠ ⁠), and then take the difference. To express the resulting interval as a TIME value, pass it to SEC_TO_TIME⁠(⁠ ⁠ ⁠). The following statement calculates the intervals between the t1 and t2 columns of the time_val table, expressing each interval both in seconds and as a TIME value:

mysql> SELECT t1, t2,     -> TIME_TO_SEC(t2) - TIME_TO_SEC(t1) AS 't2 - t1 (in seconds)',     -> SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1)) AS 't2 - t1 (as TIME)'     -> FROM time_val; +----------+----------+----------------------+-------------------+ | t1       | t2       | t2 - t1 (in seconds) | t2 - t1 (as TIME) | +----------+----------+----------------------+-------------------+ | 15:00:00 | 15:00:00 |                    0 | 00:00:00          | | 05:01:30 | 02:30:20 |                -9070 | -02:31:10         | | 12:30:20 | 17:30:45 |                18025 | 05:00:25          | +----------+----------+----------------------+-------------------+

Date or date-and-time interval calculation using basic units

When you calculate an interval between dates by converting both dates to a common unit in relation to a given reference point and take the difference, the range of values that you're working with determines which conversions are available:

DATE, DATETIME, or TIMESTAMP values dating back to 1970-01-01 00:00:00 UTCthe date of the Unix epochcan be converted to seconds elapsed since the epoch. If both dates lie within that range, you can calculate intervals to an accuracy of one second.
Older dates from the beginning of the Gregorian calendar (1582) on can be converted to day values and used to compute intervals in days.
Dates that begin earlier than either of these reference points present more of a problem. In such cases, you may find that your programming language offers computations that are not available or are difficult to perform in SQL. If so, consider processing date values directly from within your API language. (For example, the Date::Calc and Date::Manip modules are available from CPAN for use within Perl scripts.)

To calculate an interval in days between date or date-and-time values, convert them to days with TO_DAYS⁠(⁠ ⁠ ⁠), and take the difference:

mysql> SELECT TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05') AS days; +------+ | days | +------+ |  210 | +------+

For an interval in weeks, do the same thing and divide the result by seven:

mysql> SELECT (TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05')) / 7 AS weeks; +---------+ | weeks   | +---------+ | 30.0000 | +---------+

You cannot convert days to months or years by simple division, because those units vary in length. For calculations to yield date intervals expressed in those units, use the TIMESTAMPDIFF⁠(⁠ ⁠ ⁠) function discussed earlier in this section.

For date-and-time values occurring within the TIMESTAMP range of 1970 to 2037, you can determine intervals to a resolution in seconds using the UNIX_TIMESTAMP⁠(⁠ ⁠ ⁠) function. For example, the number of seconds between dates that lie two weeks apart can be computed like this:

mysql> SET @dt1 = '1984-01-01 09:00:00'; mysql> SET @dt2 = @dt1 + INTERVAL 14 DAY; mysql> SELECT UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1) AS seconds; +---------+ | seconds | +---------+ | 1209600 | +---------+

To convert the interval in seconds to other units, perform the appropriate arithmetic operation. Seconds are easily converted to minutes, hours, days, or weeks:

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 | +---------+------------+----------+---------+--------+

To produce integer values (no fractional part), use the FLOOR⁠(⁠ ⁠ ⁠) function, as shown in Section 6.8. This applies to several of the following examples as well.

For values that occur outside the TIMESTAMP range, you can use an interval calculation method that is more general (but messier):

Take the difference in days between the date parts of the values and multiply by 24 x 60 x 60 to convert to seconds.
Offset 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 a week apart:

mysql> SET @dt1 = '1800-02-14 07:30:00'; mysql> SET @dt2 = @dt1 + INTERVAL 7 DAY; 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      | +---------+-----------+ | 604800  | 168:00:00 | +---------+-----------+

Do You Want an Interval or a Span?

When you take a difference between dates (or times), consider whether you want an interval or a span. Taking a difference between dates gives you the interval from one date to the next. If you want to know the range spanned by the two dates, you must add a unit. For example, it's a three-day interval from 2002-01-01 to 2002-01-04, but together they span a range of four days. If you're not getting the results you expect from a difference-of-values calculation, consider whether you need to apply an "off-by-one" correction.