You might have noticed that MySQL has a TIMEDIFF() function and a SUBTIME() function. And you may have noticed that in many cases, they both return the same result. So you may be wondering what the difference is between these two functions?

Let’s find out.

Syntax

First let’s look at the syntax of each function.

SUBTIME()

SUBTIME(expr1,expr2)

Where expr1 is a time or datetime expression, and expr2 is a time expression.

TIMEDIFF()

TIMEDIFF(expr1,expr2)

Where expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

The Difference

So here’s the difference:

• When using SUBTIME(), the second argument must be a time expression.
• When using TIMEDIFF() the second argument can be either time or date-and-time expression, but in either case, it must be the same type as the first expression.

Example 1 – Both Arguments are ‘time’ Values

Here’s a comparison of both functions, where both arguments are time values.

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

Result:

+----------+----------+
| TIMEDIFF | SUBTIME  |
+----------+----------+
| 01:00:00 | 01:00:00 |
+----------+----------+

As expected, both functions return the same result.

Example 2 – Both Arguments are ‘datetime’ Values

Here’s a comparison of both functions, where both arguments are datetime values.

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

Result:

+-----------+---------+
| TIMEDIFF  | SUBTIME |
+-----------+---------+
| 744:00:00 | NULL    |
+-----------+---------+

In this case, SUBTIME() returned a null value because its second argument wasn’t a time value (it was a datetime value).

Example 3 – First Argument is ‘datetime’, Second is ‘time’

Here, the first argument is a datetime value. The second argument is a time value.

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

Result:

+----------+---------------------+
| TIMEDIFF | SUBTIME             |
+----------+---------------------+
| NULL     | 2021-02-01 01:00:00 |
+----------+---------------------+

In this case, TIMEDIFF() returned a null value because both arguments were of a different type.

Example 4 – First Argument is ‘time’, Second is ‘datetime’

And here, we swap it around. The first argument is a time value. The second argument is a datetime value.

SELECT 
    TIMEDIFF('09:35:25', '2021-01-01 10:35:25') AS 'TIMEDIFF',
    SUBTIME('09:35:25', '2021-01-01 10:35:25') AS 'SUBTIME';

Result:

+----------+---------+
| TIMEDIFF | SUBTIME |
+----------+---------+
| NULL     | NULL    |
+----------+---------+

In this case:

• TIMEDIFF() returned a null value because both arguments were of a different type.
  SUBTIME() returned a null value because it only accepts a time value for its second argument.