In MariaDB, TIMEDIFF() is a built-in date and time function that returns the difference between two time values or datetime values, expressed as a time value.
It’s similar to the DATEDIFF() function, except that DATEDIFF()‘s return value is expressed in days.
TIMEDIFF() accepts two arguments, both of which are time or datetime expressions. It then subtracts the second time from the first.
Syntax
The syntax goes like this:
TIMEDIFF(expr1,expr2)It then returns expr1 - expr2.
Example
Here’s an example:
SELECT TIMEDIFF('10:30:17', '1:10:10');Result:
+---------------------------------+
| TIMEDIFF('10:30:17', '1:10:10') |
+---------------------------------+
| 09:20:07 |
+---------------------------------+
Here it is again, but with the time values swapped around:
SELECT TIMEDIFF('1:10:10', '10:30:17');Result:
+---------------------------------+
| TIMEDIFF('1:10:10', '10:30:17') |
+---------------------------------+
| -09:20:07 |
+---------------------------------+
Datetime Values
Here’s an example that uses datetime values:
SELECT TIMEDIFF('2030-01-25 00:00:00', '2030-01-20 23:59:59');Result:
+--------------------------------------------------------+
| TIMEDIFF('2030-01-25 00:00:00', '2030-01-20 23:59:59') |
+--------------------------------------------------------+
| 96:00:01 |
+--------------------------------------------------------+
Mixed Value Types
While TIMEDIFF() works on both time and datetime values, both arguments must be of the same type. Mixing types results in null.
Example:
SELECT TIMEDIFF('2030-01-25 00:00:00', '23:59:59');Result:
+---------------------------------------------+
| TIMEDIFF('2030-01-25 00:00:00', '23:59:59') |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
Out of Range
The range for time values is '-838:59:59.999999' to '838:59:59.999999'. If the result is outside that range, the return value is the upper limit of its relevant range, and a warning is returned.
Example:
SELECT TIMEDIFF('2040-01-25 00:00:00', '2030-01-25 23:59:59');Result:
+--------------------------------------------------------+
| TIMEDIFF('2040-01-25 00:00:00', '2030-01-25 23:59:59') |
+--------------------------------------------------------+
| 838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.003 sec)
Let’s take a look at the warning:
SHOW WARNINGS;Result:
+---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1292 | Truncated incorrect time value: '87624:00:01' | +---------+------+-----------------------------------------------+
Current Date
Here, we pass NOW() as the second argument:
SELECT
NOW(),
TIMEDIFF('2021-05-27 10:00:00', NOW());Result:
+---------------------+----------------------------------------+
| NOW() | TIMEDIFF('2021-05-27 10:00:00', NOW()) |
+---------------------+----------------------------------------+
| 2021-05-27 14:29:38 | -04:29:38 |
+---------------------+----------------------------------------+
Invalid Arguments
When passed any invalid arguments, TIMEEDIFF() returns null:
SELECT TIMEDIFF('Homer', 'Simpson');Result:
+------------------------------+
| TIMEDIFF('Homer', 'Simpson') |
+------------------------------+
| NULL |
+------------------------------+
Missing Argument
Calling TIMEDIFF() the wrong number of arguments, or without passing any arguments, results in an error:
SELECT TIMEDIFF();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TIMEDIFF'
And:
SELECT TIMEDIFF('2030-05-21');Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TIMEDIFF'