MySQL DATEDIFF function returns the number of days between two given dates.
The DATEDIFF takes two arguments as shown in the example below:
SELECT DATEDIFF(CURDATE(), "2018-03-10");
In the above example, the CURDATE() function took the current date from the system where MySQL database is installed.
The first should be recent than the second date, otherwise, DATEDIFF returns a negative number. I will show you in the example below.
The example of using static date value in DATEDIFF function
In the first simple example, I used a static date to get the difference of days.
The first is the current date by using the CURDATE() function while the other is given “2018-02-16”. See the result as I executed this query:
The DATEDIFF query:
SELECT DATEDIFF(CURDATE(), "2018-02-16") AS "Difference in Days";
The result (assuming the current date is 6th May 2018):
What if CURDATE() was used as the second argument?
Now see the outcome as I used CURDATE() function as the second argument in MySQL DATEDIFF function.
The other date is the same as in the above example; just the order is changed:
SELECT DATEDIFF("2018-02-16", CURDATE()) AS "Difference in Days";
The result:
The result is -79. So, you should use the higher date as the first argument to get the difference as the positive number.
In the case of a future date in the above example, the DATEDIFF() should produce a positive result. For example:
Using the table column data with DATEDIFF function
For this example, we will use the table data for calculating the difference in number of days.
For that, the tbl_products table is used that stores product information including the last modified date.
In the query, the CURDATE() function is used with DATEDIFF() function and the second argument is the date_modified column.
The query returns the difference in days for complete table data (dummy).
The query with DATEDIFF():
SELECT Product_Name, Product_quality, date_modified, DATEDIFF(CURDATE(), date_modified) AS "Difference with Current Date" FROM `tbl_products` WHERE 1;
The result-set:
How to get months difference in MySQL?
Well, until now we have seen the difference in terms of days between the two given dates while using the DATEDIFF function. What about getting the difference in months?
One way can be getting the days difference and dividing by 28, 30, or 31, however, this sounds tricky and hard.
The easy way to get the month difference is using the TIMESTAMPDIFF() function of MySQL.
A sample query to get the Months difference is:
SELECT TIMESTAMPDIFF(MONTH, "2016-02-16", CURDATE()) AS "Difference in Month";
The example of getting Month, Year, Days, Hours, and Minutes difference
By using the same table as in above example, we will calculate the difference between the two dates in terms of:
- Years
- Months
- Days
- Hours
- Minutes
by using the TIMESTAMPDIFF() function as follows:
SELECT Product_Name, Product_quality, date_modified, TIMESTAMPDIFF(YEAR, date_modified, CURDATE()) AS "Difference in Years", TIMESTAMPDIFF(MONTH, date_modified, CURDATE()) AS "Difference in Months", TIMESTAMPDIFF(DAY, date_modified, CURDATE()) AS "Difference in Days", TIMESTAMPDIFF(HOUR, date_modified, CURDATE()) AS "Difference in Hours", TIMESTAMPDIFF(MINUTE, date_modified, CURDATE()) AS "Difference in Minutes" FROM `tbl_products` WHERE 1;
Assume the current date is the 6th of May 2018. You may compare the date in the “date_modified” column and the differences in the respective columns.
The result-set:
Is not that pretty simple!