To calculate an interval between two dates or times, 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.

The following discussion shows several ways to perform interval
calculations.

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

function:

mysql>mysql>`SET @d1 = '2010-01-01', @d2 = '2009-12-01';`

+---------+---------+ | d1 - d2 | d2 - d1 | +---------+---------+ | 31 | -31 | +---------+---------+`SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';`

`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>mysql>`SET @t1 = '12:00:00', @t2 = '16:30:00';`

+-----------+----------+ | t1 - t2 | t2 - t1 | +-----------+----------+ | -04:30:00 | 04:30:00 | +-----------+----------+`SELECT TIMEDIFF(@t1,@t2) AS 't1 - t2', TIMEDIFF(@t2,@t1) AS 't2 - t1';`

`TIMEDIFF()`

also works
for date-and-time values. That's 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. 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`

+----------+----------+-----------------+------+------+--------+--------+ | 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 | +----------+----------+-----------------+------+------+--------+--------+`FROM time_val;`

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

*and*

`val1`

*are the values between which to calculate the interval. With*

`val2`

`TIMESTAMPDIFF()`

, you can
express an interval many different ways:mysql>mysql>`SET @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00';`

->`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,`

+---------+-------+------+-------+-------+ | minutes | hours | days | weeks | years | +---------+-------+------+-------+-------+ | 5258880 | 87648 | 3652 | 521 | 10 | +---------+-------+------+-------+-------+`TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years;`

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.

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 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)'`

+----------+----------+----------------------+-------------------+ | 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 | +----------+----------+----------------------+-------------------+`FROM time_val;`

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`

UTC—the date of the Unix epoch—can 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>+------+ | days | +------+ | 210 | +------+`SELECT TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05') AS days;`

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

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

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>mysql>`SET @dt1 = '1984-01-01 09:00:00';`

mysql>`SET @dt2 = @dt1 + INTERVAL 14 DAY;`

+---------+ | seconds | +---------+ | 1209600 | +---------+`SELECT UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1) AS seconds;`

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>mysql>`SET @interval = UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1);`

->`SELECT @interval AS seconds,`

->`@interval / 60 AS minutes,`

->`@interval / (60 * 60) AS hours,`

->`@interval / (24 * 60 * 60) AS days,`

+---------+------------+----------+---------+--------+ | seconds | minutes | hours | days | weeks | +---------+------------+----------+---------+--------+ | 1209600 | 20160.0000 | 336.0000 | 14.0000 | 2.0000 | +---------+------------+----------+---------+--------+`@interval / (7 * 24 * 60 * 60) AS weeks;`

To produce integer values (no fractional part), use the
`FLOOR()`

function. 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 × 60 × 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>mysql>`SET @dt1 = '1800-02-14 07:30:00';`

mysql>`SET @dt2 = @dt1 + INTERVAL 7 DAY;`

->`SET @interval =`

->`((TO_DAYS(@dt2) - TO_DAYS(@dt1)) * 24*60*60)`

mysql>`+ TIME_TO_SEC(@dt2) - TIME_TO_SEC(@dt1);`

+---------+-----------+ | seconds | TIME | +---------+-----------+ | 604800 | 168:00:00 | +---------+-----------+`SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;`

## Learn more about this topic from **MySQL Cookbook, 2nd Edition**.

A handy resource when you need quick solutions or techniques, this Cookbook addresses specific questions in using MySQL. You'll find dozens of short, focused pieces of code and hundreds of worked-out examples that are perfect for programmers of all levels who don't have the time (or expertise) to solve MySQL problems from scratch. The new edition covers MySQL 5.0 and the older but still widespread MySQL 4.1.