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);
Code language: SQL (Structured Query Language) (sql)
The TIMEDIFF function accepts two arguments that must be the same type, either TIME or DATETIME. The TIMEDIFF 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 is from -838:59:59 to 838:59:59.
It’s important to note that the TIMEDIFF 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)
Code language: SQL (Structured Query Language) (sql)
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 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)
Code language: SQL (Structured Query Language) (sql)
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)
Code language: SQL (Structured Query Language) (sql)
If you pass two arguments with different types, one is DATETIME and the other is TIME, the TIMEDIFF 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)
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
It returns the following result:
+------------+
| diff |
+------------+
| 838:59:59 |
+------------+
1 row in set, 1 warning (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
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)
Code language: SQL (Structured Query Language) (sql)
So the result should be 1416 hours, however, as we mentioned earlier, the result of the TIMEDIFF function is a TIME 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;
Code language: SQL (Structured Query Language) (sql)
The the following shows the difference between two DATETIME values in hour:
+------+
| diff |
+------+
| 1416 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL TIMEDIFF function to calculate the difference between two TIME or DATETIME values.