MySQL TIMEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?

This article looks at the difference between the MySQL TIMEDIFF() and TIMESTAMPDIFF() functions.

Both functions do a similar thing, but there are some significant differences between the two.

The following table summarizes the difference between these two functions:

TIMEDIFF()	TIMESTAMPDIFF()
Requires 2 arguments.	Requires 3 arguments.
Subtracts the 2nd argument from the 1st (date1 − date2).	Subtracts the 2nd argument from the 3rd (date2 − date1).
Result is expressed as a time value (and it has the limitations of the time data type).	Result is an integer, expressed by a number of units as provided by the first argument.
Accepts time or datetime expressions.	Accepts date or datetime expressions.
Both arguments must be the same type (either time or datetime).	Both arguments can be of a different type (date or datetime).

Example 1 – Basic Difference

Here’s an example that demonstrates the basic difference between these functions.

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| TIMEDIFF | TIMESTAMPDIFF |
+----------+---------------+
| 24:00:00 |           -24 |
+----------+---------------+

So we can see that TIMEEDIFF() returned a time value, and TIMESTAMPDIFF() returned an integer.

Also, TIMEEDIFF() subtracted the 2nd date from the 1st, while TIMESTAMPDIFF()subtracted the 1st date from the 2nd.

Example 2 – Changing the Unit

As mentioned, TIMESTAMPDIFF() allows us to specify which unit to represent the result in. Here are some examples:

SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds';

Result:

+----------+-------+---------+---------+
| TIMEDIFF | Hours | Minutes | Seconds |
+----------+-------+---------+---------+
| 36:15:35 |   -36 |   -2175 | -130535 |
+----------+-------+---------+---------+

However, here’s what happens if we use a unit that’s larger than the actual time difference:

SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(week, @date1, @date2) AS 'Weeks',
  TIMESTAMPDIFF(month, @date1, @date2) AS 'Months';

Result:

+----------+------+-------+--------+
| TIMEDIFF | Days | Weeks | Months |
+----------+------+-------+--------+
| 36:15:35 |   -1 |     0 |      0 |
+----------+------+-------+--------+

In this case, the time difference wasn’t big enough to affect the week or month values.

Example 3 – Wrong Argument Types

Here’s an example of what happens when you pass the wrong argument types to each function.

SET @date1 = '2010-10-11', @date2 = '2010-10-10', @time1 = '12:15:35', @time2 = '00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF Date',
  TIMESTAMPDIFF(hour, @time1, @time2) AS 'TIMESTAMPDIFF Time';

Result:

+---------------+--------------------+
| TIMEDIFF Date | TIMESTAMPDIFF Time |
+---------------+--------------------+
| 00:00:00      |               NULL |
+---------------+--------------------+

The TIMEDIFF() doesn’t support the ‘date’ data type, and so it returns 00:00:00.

And the TIMESTAMPDIFF() function doesn’t support the ‘time’ data type, so it returns NULL.

Example 4 – Mixed Argument Types

Here’s what happens if you provide two different data types to each function.

SET @thedate = '2010-10-11', @thetime = '12:15:35', @thedatetime = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@thetime, @thedatetime) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @thedate, @thedatetime) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| TIMEDIFF | TIMESTAMPDIFF |
+----------+---------------+
| NULL     |           -24 |
+----------+---------------+

So we can see that TIMESTAMPDIFF() handles mixed data types fine (as long as they’re either date or date-and-time).

However, TIMEDIFF() requires that both arguments are of the same type, so we get NULL, even though both arguments are of a type that the function supports (time and datetime).

We can confirm that both types are in fact supported by this function with the following example:

SET @thetime1 = '12:15:35', @thetime2 = '10:15:35', @thedatetime1 = '2010-10-12 00:00:00', @thedatetime2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@thetime1, @thetime2) AS 'time',
  TIMEDIFF(@thedatetime1, @thedatetime2) AS 'datetime';

Result:

+----------+----------+
| time     | datetime |
+----------+----------+
| 02:00:00 | 48:00:00 |
+----------+----------+

So it’s fine, as long as both arguments are the same type (either time or datetime values).