Showing posts with label Mysql DATEDIFF. Show all posts
Showing posts with label Mysql DATEDIFF. Show all posts

Tuesday, 30 July 2019

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

DATEDIFF() Examples – MySQL

In MySQL, you can use the DATEDIFF() function to find the difference between two dates. The way it works is, you provide two arguments (one for each date), and DATEDIFF() will return the number of days between the two dates.
Examples below.

Syntax

First, here’s the syntax:
DATEDIFF(expr1,expr2)
Where expr1 is the first date, and expr2 is the second date.

Example 1 – Basic Usage

Here’s an example to demonstrate.
SELECT DATEDIFF('2020-10-30', '2020-10-01') AS 'Result';
Result:
+--------+
| Result |
+--------+
|     29 |
+--------+
In this example, the first date is later than the second date. In this case we get a positive return value.

Example 2 – Comparison with an Earlier Date

The first date doesn’t have to be a later date than the second date. You can use an earlier date for the first argument and it will return a negative value. If we swap those two arguments around, we get the following:
SELECT DATEDIFF('2020-10-01', '2020-10-30') AS 'Result';
Result:
+--------+
| Result |
+--------+
|    -29 |
+--------+

Example 3 – Datetime Values

When used with datetime values, only the date part is used to compare the dates. Example:
SELECT 
  DATEDIFF('2020-10-30 23:59:59', '2020-10-01') AS 'Result 1',
  DATEDIFF('2020-10-01 23:59:59', '2020-10-30') AS 'Result 2';
Result:
+----------+----------+
| Result 1 | Result 2 |
+----------+----------+
|       29 |      -29 |
+----------+----------+

Example 4 – Database Query

Here’s an example of using DATEDIFF() in a database query. In this example, I compare the payment_date column with today’s date (by using the CURDATE() function to return today’s date):
USE sakila;
SELECT
  DATE(payment_date) AS 'Date/Time',
  CURDATE(),
  DATEDIFF(payment_date, CURDATE()) AS 'Date'
FROM payment
WHERE payment_id = 1;
Result:
+------------+------------+-------+
| Date/Time  | CURDATE()  | Date  |
+------------+------------+-------+
| 2005-05-25 | 2018-06-25 | -4779 |
+------------+------------+-------+

Subscribe to: Posts (Atom)