MySQL - TIMEDIFF() Examples

The MySQL TIMEDIFF() function returns the difference between two time or datetime values.

The way it works is, you provide the two values to compare, and TIMEDIFF() subtracts the second value from the first, then returns the result as a time value.

Syntax

The syntax goes like this:

TIMEDIFF(expr1,expr2)

Where expr1 and expr2 are the two values to compare. The return value is expr2subtracted from expr1.

Basic Example

Here’s an example to demonstrate.

SELECT TIMEDIFF('11:35:25', '10:35:25');

Result:

+----------------------------------+
| TIMEDIFF('11:35:25', '10:35:25') |
+----------------------------------+
| 01:00:00                         |
+----------------------------------+

Elapsed Time

The time value can represent elapsed time, so it’s not limited to being less than 24 hours.

SELECT TIMEDIFF('500:35:25', '10:35:25');

Result:

+-----------------------------------+
| TIMEDIFF('500:35:25', '10:35:25') |
+-----------------------------------+
| 490:00:00                         |
+-----------------------------------+

Negative Time Difference

If the second value is larger than the first, you’ll get a negative value for the time difference. This is perfectly valid.

SELECT TIMEDIFF('10:35:25', '500:35:25');

Result:

+-----------------------------------+
| TIMEDIFF('10:35:25', '500:35:25') |
+-----------------------------------+
| -490:00:00                        |
+-----------------------------------+

Datetime Values

Here’s an example that uses datetime values as the arguments.

SELECT TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25');

Result:

+--------------------------------------------------------+
| TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| 744:00:00                                              |
+--------------------------------------------------------+

Note that both arguments must be of the same type. So you can’t have a time value for the first and a datetime value for the second (and vice-versa).

Also note that the time data type can only be in the range -838:59:59 to 838:59:59. Therefore, the following doesn’t work:

SELECT TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25');

Result:

+--------------------------------------------------------+
| TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| -838:59:59                                             |
+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

In this case, we get an incorrect result and a warning.