Database Reference
In-Depth Information
To calculate an interval between
TIME
values as another
TIME
value, use the
TIME
DIFF()
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 is, 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
Recipe 6.8
. For example, to express a time interval in terms of
its constituent hours, minutes, and seconds values, calculate time interval subparts using
the
HOUR()
,
MINUTE()
, and
SECOND()
functions. (Don't forget that if your intervals may
be negative, you must take that into account.) The following SQL statement shows how
to determine the components of the interval between the
t1
and
t2
columns of the
time_val
table:
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 work with date or date-and-time values, the
TIMESTAMPDIFF()
function provides
another way to calculate intervals. It enables you to specify the units in which intervals
should be expressed:
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 in 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,
