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).