MySQL TIMEDIFF Function

 MySQL TIMEDIFF Function

Summary: This tutorial shows you how to use the MySQL TIMEDIFF function and provides you with some important usage notes of the function.

Introduction to MySQL TIMEDIFF function

The TIMEDIFF returns the difference between two TIME or DATETIME values. See the following syntax of TIMEDIFF function.

TIMEDIFF(dt1, dt2);

The TIMEDIFF the function accepts two arguments that must be the same type, either TIME or DATETIME. The TIMEDIFF the function returns the result of dt1 - dt2 expressed as a time value.

Because the TIMEDIFF function returns a TIME value, its result is limited to the range allowed for TIME values which are from -838:59:59 to 838:59:59.

It’s important to note that the TIMEDIFF the function accepts values with TIME or DATETIME types. To compare a difference between two DATE or DATETIME values, you use the DATEDIFF function.

MySQL TIMEDIFF function examples

Let’s take an example that calculates the difference between two-time values.

mysql> SELECT TIMEDIFF('12:00:00','10:00:00') diff;
+----------+
| diff     |
+----------+
| 02:00:00 |
+----------+
1 row in set (0.00 sec)

In this example, we calculated the difference between 12:00:00 and 10:00:00 that results in 02:00:00.

The following example calculates the difference between the two DATETIME values:

mysql> SELECT 
            TIMEDIFF('2010-01-01 01:00:00',
                     '2010-01-02 01:00:00') diff;
+-----------+
| diff      |
+-----------+
| -24:00:00 |
+-----------+
1 row in set (0.00 sec)

The TIMEDIFF function returns NULL if either argument is NULL.

mysql> SELECT TIMEDIFF('2010-01-01',NULL) diff;
+------+
| diff |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

If you pass two arguments with different types, one is DATETIME and the other is TIME, the TIMEDIFF the function also returns NULL.

mysql> SELECT TIMEDIFF(
                '2010-01-01 10:00:00',
                '10:00:00') diff;
+------+
| diff |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

MySQL TIMEDIFF function and truncated incorrect time value

Consider the following example.

SELECT 
    TIMEDIFF('2009-03-01 00:00:00', 
             '2009-01-01 00:00:00') diff;

It returns the following result:

+------------+
| diff       |
+------------+
| 838:59:59 |
+------------+
1 row in set, 1 warning (0.00 sec)

As you can see, we got one warning. Let’s see what it is by using the SHOW WARNINGS statement.

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '1416:00:00'  |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

So the result should be 1416 hours, however, as we mentioned earlier, the result of the TIMEDIFF function is a TIME the value which ranges from -838:59:59 to 838:59:59. Therefore, MySQL truncated the result.

To fix this problem, you need to use the TIMESTAMPDIFF function as follows:

SELECT TIMESTAMPDIFF(
            HOUR, 
            '2009-01-01 00:00:00', 
            '2009-03-01 00:00:00') diff;

The following shows the difference between the two DATETIME values in an hour:

+------+
| diff |
+------+
| 1416 |
+------+
1 row in set (0.00 sec)

In this tutorial, you have learned how to use the MySQL TIMEDIFF function to calculate the difference between two TIME or DATETIME values.