Two date functions included in MySQL are DATEDIFF() and TIMEDIFF().
Both functions do a similar thing, but with some meaningful differences.
The following table summarizes the difference between these two functions:
DATEDIFF() |
TIMEDIFF() |
|---|---|
| Result is expressed as a value in days. | Result is expressed as a time value. |
| Compares only the date value of its arguments. | Compares the time value of its arguments. |
| Accepts date or date-and-time expressions. | Accepts time or date-and-time expressions. |
| Both arguments can be of a different type (date or date-and-time). | Both arguments must be the same type (either time or date-and-time). |
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 DATEDIFF(@date1, @date2) AS 'DATEDIFF', TIMEDIFF(@date1, @date2) AS 'TIMEDIFF';
Result:
+----------+----------+ | DATEDIFF | TIMEDIFF | +----------+----------+ | 1 | 24:00:00 | +----------+----------+
So we can see that DATEDIFF() returned 1, meaning “1 day”, and TIMEDIFF() returned 24:00:00 which is the time representation of exactly 1 day.
Example 2 – Specifying a Time Value
Let’s see what happens if we increase the time value of one of the variables.
SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00'; SELECT DATEDIFF(@date1, @date2) AS 'DATEDIFF', TIMEDIFF(@date1, @date2) AS 'TIMEDIFF';
Result:
+----------+----------+ | DATEDIFF | TIMEDIFF | +----------+----------+ | 1 | 36:15:35 | +----------+----------+
So DATEDIFF() returns the same result as in the previous example. This is because it only compares the date values (it ignores any time values).
The TIMEDIFF() function, on the other hand, compares the time, and therefore it returns a more precise result. It shows us that there are 36 hours, 15 minutes, and 35 seconds between the two date-and-time 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 DATEDIFF(@date1, @date2) AS 'DATEDIFF Date', DATEDIFF(@time1, @time2) AS 'DATEDIFF Time', TIMEDIFF(@date1, @date2) AS 'TIMEDIFF Date', TIMEDIFF(@time1, @time2) AS 'TIMEDIFF Time';
Result:
+---------------+---------------+---------------+---------------+ | DATEDIFF Date | DATEDIFF Time | TIMEDIFF Date | TIMEDIFF Time | +---------------+---------------+---------------+---------------+ | 1 | NULL | 00:00:00 | 12:15:35 | +---------------+---------------+---------------+---------------+
The first and last results are fine, because the correct argument types were passed in. However, the middle two results had the wrong data type passed in and therefore the correct result couldn’t be calculated.
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 DATEDIFF(@thedate, @thedatetime) AS 'DATEDIFF', TIMEDIFF(@thetime, @thedatetime) AS 'TIMEDIFF';
Result:
+----------+----------+ | DATEDIFF | TIMEDIFF | +----------+----------+ | 1 | NULL | +----------+----------+
So we can see that DATEDIFF() 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 date-and-time).
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 | +----------+----------+