Select records with a date in the last 24 hours

Question

I want to select from my table all the records where the date (mysql date-time of the format YYYY-MM-DD HH: MM: SS) is in the last 24 hours. I have a request, but it does not work completely

SELECT * FROM `my_table` WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR) 

why he returns such a date 2013-07-01 12:00:00. How can I do it? Thanks.

Answer 1

You already have a lower date limit, but since your table may have future dates, you also need an upper limit. This should work:

 SELECT * FROM my_table WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR) AND date <= NOW() 

Answer 2

MySQL:

SELECT * FROM my_table WHERE date> = now () - INTERVAL 24 HOUR;