Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql Datediff query

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
like image 201
sathish Avatar asked Oct 10 '11 12:10

sathish


People also ask

How does MySQL calculate datediff?

To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days. In this case, the enddate is arrival and the startdate is departure .

Does datediff working in MySQL?

The DATEDIFF function can be used in the following versions of MySQL: MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1. 1.

How do I subtract one date from another in MySQL?

DATE_SUB() function in MySQL is used to subtract a specified time or date interval to a specified date and then returns the date.

How do I use datediff in SQL Workbench?

But if you still want to use DATEDIFF , you can write something like this: SELECT FROM_DAYS(DATEDIFF(NOW(),C. END_DT)) FROM mytable C; DATEDIFF here return the differences in total days and using FROM_DAYS(), it will return the total year, month and day from the specific date in the comparison.


2 Answers

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.

like image 164
O. Jones Avatar answered Sep 27 '22 19:09

O. Jones


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)
like image 24
Marcus Avatar answered Sep 27 '22 21:09

Marcus