Mysql Datediff query

Question

I need to get the result from the table, which the date should be difference of 5 from the current date.

ie., specific_date column is present in my table. The format of the date is YYYY-MM-DD.

I need the query something like,

SELECT * FROM `table_name` WHERE DATEDIFF(NOW(), specific_date) < 5

Solution 1

It looks like you are trying to do this:

WHERE specific_date < (NOW() + 5 days)

First of all, think carefully about the boundary cases. These boundary cases can bite your ankles in SQL. Do you actually want

WHERE specific_date <= (NOW() + 5 days)

Do your specific_date columns timestamps contain only days (that is dates with times equal to midnight) or do they contain dates and times? If you're going to get the results you want, you need to be careful about those details.

At any rate, try this:

WHERE specific_date <= DATE_ADD(NOW(), INTERVAL 5 DAY)

This is a good way to do such a lookup. The column value stands alone on one side of the inequality predicate (the <=) so mySQL can do an index range scan if you have an index on the column.

Date arithmetic in MySQL is quite flexible. You can do

   NOW() + INTERVAL 10 SECOND
   NOW() - INTERVAL 2 MINUTE
   NOW() + INTERVAL 4 HOUR
   CURDATE() - INTERVAL 1 WEEK /* midnight one week ago */
   LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH  /*first day of present month*/
   NOW() - INTERVAL 1 QUARTER
   CURDATE() - INTERVAL 5 YEAR

and so forth.

Solution 2

Have a look at the BETWEEN operator.

expr BETWEEN min AND max

Another way is to use the DATE_SUB operator

WHERE date_column > DATE_SUB(NOW(), INTERVAL 5 DAY)