MySQL - DATEDIFF() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL DATEDIFF() function accepts two date or, date-time values as parameters, calculates the difference between them (argument1-argument2) and returns the result. This function returns difference between the given date values in the form of days. This function includes only the date parts of the arguments while calculating the difference.

Syntax

Following is the syntax of the above function –

DATEDIFF(expr1, expr2)

Example 1

Following example demonstrates the usage of the DATEDIFF() function –

mysql> SELECT DATEDIFF('2015-09-05', '1989-03-25');
+--------------------------------------+
| DATEDIFF('2015-09-05', '1989-03-25') |
+--------------------------------------+
|                                 9660 |
+--------------------------------------+
1 row in set (0.09 sec)

Example 2

Following is another example of this function –

mysql> SELECT DATEDIFF('2019-05-25', '2019-05-05');
+--------------------------------------+
| DATEDIFF('2019-05-25', '2019-05-05') |
+--------------------------------------+
|                                   20 |
+--------------------------------------+
1 row in set (0.00 sec)

Example 3

In the following example we are passing DATETIME value as argument to this function–

mysql> SELECT DATEDIFF('2018-05-23 20:40:32', '1996-12-07');
+-----------------------------------------------+
| DATEDIFF('2018-05-23 20:40:32', '1996-12-07') |
+-----------------------------------------------+
|                                          7837 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2018-05-23 20:40:32', '1996-12-07 20:15:40');
+--------------------------------------------------------+
| DATEDIFF('2018-05-23 20:40:32', '1996-12-07 20:15:40') |
+--------------------------------------------------------+
|                                                   7837 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are passing the result of CURDATE() as an argument to the DATEDIFF() function —

mysql> SELECT DATEDIFF(CURDATE(), '1995-11-15');
+-----------------------------------+
| DATEDIFF(CURDATE(), '1995-11-15') |
+-----------------------------------+
|                              9374 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2050-03-25', CURDATE());
+-----------------------------------+
| DATEDIFF('2050-03-25', CURDATE()) |
+-----------------------------------+
|                             10480 |
+-----------------------------------+
1 row in set (0.00 sec)

Example 5

We can also pass current timestamp values as arguments to this function –

mysql> SELECT DATEDIFF(NOW(), '2015-09-05');
+-------------------------------+
| DATEDIFF(NOW(), '2015-09-05') |
+-------------------------------+
|                          2140 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF(CURRENT_TIMESTAMP(), '2015-09-05');
+---------------------------------------------+
| DATEDIFF(CURRENT_TIMESTAMP(), '2015-09-05') |
+---------------------------------------------+
|                                        2140 |
+---------------------------------------------+
1 row in set (0.00 sec)

Example 6

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query calculates the age of the players in days —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, DATEDIFF(CURDATE(), Date_Of_Birth) as Age_In_Days FROM MyPlayers;
+------------+------------+---------------+-------------+-------------+
| First_Name |  Last_Name | Date_Of_Birth |     Country | Age_In_Days |
+------------+------------+---------------+-------------+-------------+
|    Shikhar |     Dhawan |    1981-12-05 |       India |       14463 |
|   Jonathan |      Trott |    1981-04-22 | SouthAfrica |       14690 |
|     Kumara | Sangakkara |    1977-10-27 |    Srilanka |       15963 |
|      Virat |      Kohli |    1988-11-05 |       India |       11936 |
|      Rohit |     Sharma |    1987-04-30 |       India |       12491 |
|   Ravindra |     Jadeja |    1988-12-06 |       India |       11905 |
|      James |   Anderson |    1982-06-30 |     England |       14256 |
+------------+------------+---------------+-------------+-------------+
7 rows in set (0.11 sec)

Example 7

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following is another example of this function —

mysql> SELECT ProductName, CustomerName, DispatchDate, Price, DATEDIFF(CURDATE(), DispatchDate) as difference_in_days FROM sales;
+-------------+--------------+--------------+-------+--------------------+
| ProductName | CustomerName | DispatchDate | Price | difference_in_days |
+-------------+--------------+--------------+-------+--------------------+
|   Key-Board |         Raja |   2019-09-01 |  7000 |                679 |
|   Earphones |         Roja |   2019-05-01 |  2000 |                802 |
|       Mouse |         Puja |   2019-03-01 |  3000 |                863 |
|      Mobile |       Vanaja |   2019-03-01 |  9000 |                863 |
|     Headset |       Jalaja |   2019-04-06 |  6000 |                827 |
+-------------+--------------+--------------+-------+--------------------+
5 rows in set (0.00 sec)

Example 8

Suppose we have created a table named Subscribers with 5 records in it using the following queries –

mysql> CREATE TABLE Subscribers(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date
);
insert into Subscribers values('Raja', 'Premium', Date('2020-10-21'));
insert into Subscribers values('Roja', 'Basic', Date('2020-11-26'));
insert into Subscribers values('Puja', 'Moderate', Date('2021-03-07'));
insert into Subscribers values('Vanaja', 'Basic', Date('2021-02-21'));
insert into Subscribers values('Jalaja', 'Premium', Date('2021-01-30'));

Following query calculates and displays the remaining number of days for the subscription to complete —

mysql> SELECT SubscriberName, PackageName, SubscriptionDate, DATEDIFF(CURDATE(), SubscriptionDate) as Remaining_Days FROM Subscribers;
+----------------+-------------+------------------+----------------+
| SubscriberName | PackageName | SubscriptionDate | Remaining_Days |
+----------------+-------------+------------------+----------------+
|           Raja |     Premium |       2020-10-21 |            263 |
|           Roja |       Basic |       2020-11-26 |            227 |
|           Puja |    Moderate |       2021-03-07 |            126 |
|         Vanaja |       Basic |       2021-02-21 |            140 |
|         Jalaja |     Premium |       2021-01-30 |            162 |
+----------------+-------------+------------------+----------------+
5 rows in set (0.11 sec)