EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL DATEDIFF

MySQL DATEDIFF

MySQL DATEDIFF

Introduction to MySQL DATEDIFF

MySQL DATEDIFF is used to find the difference between the specified dates. The DATEDIFF function has two arguments in it; we specify the dates for which we want to find the difference. The arguments are <date1> and <date2>. In the DATEDIFF function, only the date portion is calculated; the time portion in the column will be ignored. We can use the in-built function in the DATEDIFF function as an argument, for example, CURDATE (). Here CURDATE () gets the current system date and calculates the difference with the specified second argument.

Syntax:

Given below is the syntax for DATEDIFF:

SELECT DATEDIFF (<Date argument 1>, <Date argument 2>);

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Here above syntax is used to find the difference between the specified dates.

SELECT DATEDIFF (<COLUMN_NAME_1>, <COLUMN_NAME_2>) FROM TABLE_NAME;

Here above syntax is used to find the difference between specified column dates.

How does MySQL DATEDIFF works?

Now let us see how the DATEDIFF works in MySQL.

Code:

select DATEDIFF( '2020-01-31 00:00:00', '2020-01-01 11:00:00') AS "DATE DIFFERENCE";

Here in the above query, it returns the date difference between them. Here it returns 30. Here only the date portion is calculated, and the time part is omitted. The screenshot is for the same.

Output:

MySQL DATEDIFF 1

Code:

select DATEDIFF( '2020-01-31', '2020-01-01 11:00:00') AS "DATE DIFFERENCE";
/ * - - - DateDiff - - - * /

Here in the above query, it returns the date difference between them. The starting date argument doesn’t have a time portion, and the lateral argument has a time portion. It returns 30 even after we perform as the DateDiff will omit time. Here only the date portion is calculated, and the time part is omitted. The screenshot is for the same.

Output:

MySQL DATEDIFF 2

Code:

select DATEDIFF( '2020-01-31 12:00:78', '2020-01-01') AS "DATE DIFFERENCE";
/ * - - - DateDiff - - - * /

Here in the above query, it returns the date difference between them. The starting date argument has a time portion, and the lateral argument doesn’t have a time portion. It returns 30 even after we perform as the DateDiff will omit time. Here only the date portion is calculated, and the time part is omitted. The screenshot is for the same.

Output:

MySQL DATEDIFF 3

Using CURDATE() in DATEDIFF() Function

Here now, let us use the CURDATE () function and try to get the date difference.

Code:

select DATEDIFF( CURDATE() , '2020-07-01 11:00:00') AS "DATE DIFFERENCE";
/ * - - - DateDiff - - - * /

Here we have mentioned the in-built function in the <date1> argument, and it returns the system’s current date and calculates with the second argument <date2>, and returns the output.

Output:

CURDATE () function

Note: Here, the output of the DATEDIFF will be specified in the ‘number of days’ difference between the dates.

Examples of MySQL DATEDIFF

Now let us see how the DATEDIFF works on the table columns.

Let us create a table as below:

Code:

CREATE TABLE DUE_CUSTOMERS
(
CUST_ID INT,
INITIATION_DATE DATETIME,
DUE_DATE datetime
);

The above table consists of the data about the customers whose due date is pending. This can be calculated as CURDATE() – DUE_DATE.

Let us insert the below rows into the above table:

Code:

INSERT INTO DUE_CUSTOMERS VALUES ( 1, '2020-01-01 12:00:00', '2020-02-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 2, '2019-03-01 12:00:00', '2019-05-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 3, '2019-08-01 12:00:00', '2020-02-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 4, '2019-06-01 12:00:00', '2020-01-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 5, '2020-02-01 12:00:00', '2020-04-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 6, '2020-03-01 12:00:00', '2020-07-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 7, '2019-09-01 12:00:00', '2020-02-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 8, '2020-01-01 12:00:00', '2020-08-01 12:00:00' );

select * from DUE_CUSTOMERS;

Output:

MySQL DATEDIFF 5

Now let us perform the DATEDIFF function on the mentioned table:

Example #1

Here we are planning to get the customers who have breached the due_date. The values that are positive represent that the customer still has those many days to breach the due. If the values are in negative values, then it shows that the customer has already breached the due with the mentioned days.

Code:

SELECT *, datediff(DUE_DATE, CURDATE()) AS BREACHED_COUNT_OF_DAYS FROM DUE_CUSTOMERS; / * - - - DateDiff - - - * /

Output:

get the customers who have breached the due_date

Example #2

Here the values that are negative values mean that customer has breached the due date. Now the last record has a negative value which means that the customer has not yet breached the due date.

We can get the duration of due time that has been given to the customers.

Code:

SELECT *, datediff( DUE_DATE, INITIATION_DATE ) AS Duration FROM DUE_CUSTOMERS;
/ * - - - DateDiff - - - * /

The above query gets the duration of time given to the customer DUE_DATE – INITIATION_DATE.

Output:

negative values means that customer has breached the due date

Here that time portion has been omitted from the above columns data. Only the Date difference has been calculated from the above table columns.

Recommended Articles

This is a guide to MySQL DATEDIFF. Here we discuss the introduction, working, using CURDATE() in the DATEDIFF() function and examples. You may also have a look at the following articles to learn more –

  1. MySQL Workbench
  2. MySQL FULLTEXT
  3. MySQL Dump
  4. MySQL REVOKE
0 Shares
Share
Tweet
Share