How do I do a query where the time differece is 1 day

Question

How would I make it so that I can search a table and get the values where the date difference equals 1

an example of what I want to do is

SELECT * FROM table where DATEDIFF('2006-04-01','2006-03-01') = 1

I found this but I don't know how to implement it or if there is a better way. Still looking.

SELECT DATEDIFF('2006-04-01','2006-03-01')

also found:

SELECT OrderId,DATE_SUB(2014-11-22 13:23:44.657,INTERVAL 5 DAY) AS SubtractDate
FROM Orders

but I want to get a result when the time difference between both dates is 1

Answer 1

You can implement this as follows:

SELECT OrderId,DATE_SUB(NOW(),INTERVAL 1 DAY) AS SubtractDate
FROM Orders;

Now () : For today's date INTERVAL 1 DAY : For you need to get data with one day difference

Or you can see more here

OR SELECT * FROM tablename WHERE DATE(lastModified) = DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) );

You can see more methods click here

Answer 2

If you need a select where the diff is one day ..

select * from my_table 
where datediff(my_date1, my_date2) =1

Answer 3

To complete the answer from scaisEdge

If you want to take the time into account, and thus only get records with a difference of exactly 24 hours (86400 seconds)

SELECT * FROM table 
WHERE TIME_TO_SEC(TIMEDIFF(my_date1, my_date2)) = 86400