What is MySQL DATEDIFF function?

The 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");

Both arguments are dates that you want to get the difference in terms of the number of days.

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 for getting the difference of days. The one 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 6^th 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 DATEDIFF MySQL function. The other date is the same as in 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 above example, the DATEDIFF() should produce the positive result. For example:

DATEDIFF(“2019-02-16”, CURDATE())

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 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 getting 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, 31, however, this sounds tricky and hard.

The easy way of getting the months difference is using the TIMESTAMPDIFF() function of MySQL.

The TIMESTAMPDIFF() function allows you specifying which unit difference you require. For example, Month, Year, Day, Minute, Hour etc.

A sample query for getting 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 get the difference between 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 6^th  of May 2018. You may compare the date in the “date_modified” column and differences in respective columns. The result-set:

Is not that pretty simple!