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

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 |
+----------+----------+

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 |
+----------+---------------+

CURDATE() Examples – MySQL

In MySQL, the CURDATE() function is used to return the current date.

More specifically, it returns the current date as a value in ‘YYYY-MM-DD’ or YYYYMMDDformat, depending on whether the function is used in a string or numeric context.

Syntax

The syntax goes like this:

CURDATE()

So no arguments are accepted or required.

However, as mentioned, the data type of the return value will depend on the context with which it’s used. More on this below.

You can also use either of the following if you prefer:

CURRENT_DATE
CURRENT_DATE()

These are synonyms for CURDATE().

Example – String Context

Here’s an example of using CURDATE() in a string context.

SELECT CURDATE();

Result:

+------------+
| CURDATE()  |
+------------+
| 2018-06-22 |
+------------+

Example – Numeric Context

Here’s an example of using CURDATE() in a numeric context.

SELECT CURDATE() + 0;

Result:

+---------------+
| CURDATE() + 0 |
+---------------+
|      20180622 |
+---------------+

In this example I added zero to the date. But I could also have added another number.

Here’s an example where I add 3 to the current date:

SELECT CURDATE() + 3;

Result:

+---------------+
| CURDATE() + 3 |
+---------------+
|      20180625 |
+---------------+

CURRENT_DATE and CURRENT_DATE()

As mentioned, both CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

Here’s an example with all three together:

SELECT 
    CURRENT_DATE,
    CURRENT_DATE(),
    CURDATE();

Result:

+--------------+----------------+------------+
| CURRENT_DATE | CURRENT_DATE() | CURDATE()  |
+--------------+----------------+------------+
| 2018-06-22   | 2018-06-22     | 2018-06-22 |
+--------------+----------------+------------+

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

MySQL - TIMESTAMPDIFF() Examples

The MySQL TIMESTAMPDIFF() function is used to find the difference between two date or datetime expressions. You need to pass in the two date/datetime values, as well as the unit to use in determining the difference (e.g., day, month, etc). The TIMESTAMPDIFF()function will then return the difference in the specified unit.

Syntax

First, here’s how the syntax goes:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Here, unit is the unit to use in expressing the difference (e.g. day, month, year, etc). datetime_expr1 is the first date/datetime value, and datetime_expr2 is the second.

This function subtracts datetime_expr1 from datetime_expr2 and returns the result in units. The result is returned as an integer.

Valid Units

The unit argument can be any of the following:

• MICROSECOND
•  SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

Example 1 – Difference in Days

Here’s an example to demonstrate the basic usage of this function. Here we compare two date expressions and return the difference between them in days.

SELECT 
  TIMESTAMPDIFF(DAY,'2022-02-01','2022-02-21')
  AS 'Difference in Days';

Result:

+--------------------+
| Difference in Days |
+--------------------+
|                 20 |
+--------------------+

Example 2 – Difference in Hours

In this example we compare the same values as in the previous example, except here, we return the difference in hours.

SELECT 
  TIMESTAMPDIFF(HOUR,'2022-02-01','2022-02-21')
  AS 'Difference in Hours';

Result:

+---------------------+
| Difference in Hours |
+---------------------+
|                 480 |
+---------------------+

Example 3 – A ‘datetime’ Example

Here’s an example that returns the difference in minutes. In this case, we compare two datetime values (as opposed to just the date values as in the previous examples).

SELECT 
  TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27')
  AS 'Difference in Minutes';

Result:

+-----------------------+
| Difference in Minutes |
+-----------------------+
|                    15 |
+-----------------------+

Example 4 – Fractional Seconds

You can go right down to the microsecond (6 digits) if you need to.

SELECT 
  TIMESTAMPDIFF(MICROSECOND,'2022-02-01 10:30:27.000000','2022-02-01 10:30:27.123456') 
  AS 'Difference in Microseconds';

Result:

+----------------------------+
| Difference in Microseconds |
+----------------------------+
|                     123456 |
+----------------------------+

Example 5 – Negative Results

As would be expected, if the first date/time argument is greater than the second, the result will be a negative integer.

SELECT 
  TIMESTAMPDIFF(DAY,'2022-02-21','2022-02-01')
  AS 'Difference in Days';

Result:

+--------------------+
| Difference in Days |
+--------------------+
|                -20 |
+--------------------+

MySQL - TIMEDIFF() Examples

The MySQL TIMEDIFF() function returns the difference between two time or datetime values.

The way it works is, you provide the two values to compare, and TIMEDIFF() subtracts the second value from the first, then returns the result as a time value.

Syntax

The syntax goes like this:

TIMEDIFF(expr1,expr2)

Where expr1 and expr2 are the two values to compare. The return value is expr2subtracted from expr1.

Basic Example

Here’s an example to demonstrate.

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

Result:

+----------------------------------+
| TIMEDIFF('11:35:25', '10:35:25') |
+----------------------------------+
| 01:00:00                         |
+----------------------------------+

Elapsed Time

The time value can represent elapsed time, so it’s not limited to being less than 24 hours.

SELECT TIMEDIFF('500:35:25', '10:35:25');

Result:

+-----------------------------------+
| TIMEDIFF('500:35:25', '10:35:25') |
+-----------------------------------+
| 490:00:00                         |
+-----------------------------------+

Negative Time Difference

If the second value is larger than the first, you’ll get a negative value for the time difference. This is perfectly valid.

SELECT TIMEDIFF('10:35:25', '500:35:25');

Result:

+-----------------------------------+
| TIMEDIFF('10:35:25', '500:35:25') |
+-----------------------------------+
| -490:00:00                        |
+-----------------------------------+

Datetime Values

Here’s an example that uses datetime values as the arguments.

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

Result:

+--------------------------------------------------------+
| TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| 744:00:00                                              |
+--------------------------------------------------------+

Note that both arguments must be of the same type. So you can’t have a time value for the first and a datetime value for the second (and vice-versa).

Also note that the time data type can only be in the range -838:59:59 to 838:59:59. Therefore, the following doesn’t work:

SELECT TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25');

Result:

+--------------------------------------------------------+
| TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| -838:59:59                                             |
+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

In this case, we get an incorrect result and a warning.