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

This article looks at the difference between two MySQL functions; DATEDIFF() and TIMESTAMPDIFF().

Both functions return the difference between two dates and/or times, but the result is different between the two functions.

The following table summarizes the difference between these two functions:

`DATEDIFF()`	`TIMESTAMPDIFF()`
Requires 2 arguments.	Requires 3 arguments.
Subtracts the 2nd argument from the 1st (expr1 − expr2).	Subtracts the 2nd argument from the 3rd (expr2 − expr1).
Result is expressed as a value in days.	Result is expressed as the unit provided by the first argument.
Can compare only the date value of its arguments.	Can compare the date and time value of its arguments.

Example 1 – Basic Operation

Here’s an example that demonstrates how these functions work, and how the results are different, even when using the same unit.

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

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |            -1 |
+----------+---------------+

So both functions return the difference in days, however one result is positive and the other negative. This is because DATEDIFF() subtracts the second date from the first, whereas TIMESTAMPDIFF() subtracts the first date from the second.

Example 2 – Changing the Unit

As the previous example demonstrates, the TIMESTAMPDIFF() allows you to specify a unit for the results to be returned as (in fact, it requires you to specify the unit). On the other hand, DATEDIFF() doesn’t allow you to specify a unit. It only returns the result in days.

So we could modify the previous example so that TIMESTAMPDIFF() returns the number of hours instead of days:

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

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |           -24 |
+----------+---------------+

You can go all the way to microseconds:

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

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |  -86400000000 |
+----------+---------------+

Example 3 – Precision

The precision of DATEDIFF() is one day, and TIMESTAMPDIFF() can go down to the microsecond. However the precision of TIMESTAMPDIFF() (and the unit that it compares) still depends on the specified unit.

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-10 23:59:59';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'Microseconds';

Result:

+----------+------+-------+---------+---------+--------------+
| DATEDIFF | Days | Hours | Minutes | Seconds | Microseconds |
+----------+------+-------+---------+---------+--------------+
|        0 |    0 |    23 |    1439 |   86399 |  86399000000 |
+----------+------+-------+---------+---------+--------------+

And here’s the result if we increment the 2nd date by one second (which brings it to the next day):

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-11 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'Microseconds';

Result:

+----------+------+-------+---------+---------+--------------+
| DATEDIFF | Days | Hours | Minutes | Seconds | Microseconds |
+----------+------+-------+---------+---------+--------------+
|       -1 |    1 |    24 |    1440 |   86400 |  86400000000 |
+----------+------+-------+---------+---------+--------------+

Here’s another example, this time seeing how it looks when we return months, quarters, and years when the difference is one month (or 31 days):

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-11-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(month, @date1, @date2) AS 'Month',
  TIMESTAMPDIFF(quarter, @date1, @date2) AS 'Quarter',
  TIMESTAMPDIFF(year, @date1, @date2) AS 'Year';

Result:

+----------+------+-------+---------+------+
| DATEDIFF | Days | Month | Quarter | Year |
+----------+------+-------+---------+------+
|      -31 |   31 |     1 |       0 |    0 |
+----------+------+-------+---------+------+

Example 4 – Wrong Argument Types

Both functions return null if they are passed the wrong argument type.

SET @time1 = '12:15:35', @time2 = '00:00:00';
SELECT 
  DATEDIFF(@time1, @time2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @time1, @time2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|     NULL |          NULL |
+----------+---------------+

Example 5 – Mixed Argument Types

Both functions allow you to provide a date as one argument and a datetime as another argument.

SET @thedate = '2010-10-11', @thedatetime = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@thedate, @thedatetime) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @thedate, @thedatetime) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |            -1 |
+----------+---------------+