CODEWITHSUNDEEP
mysqlselect
Indy
Indy

Reputation: 4957

select records with date in the last 24 hours

I want to select from my table all records where date (datetime mysql format YYYY-MM-DD HH:MM:SS) is in the last 24 hours. I have a query, but it doesn't completely work

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

why it returns the date like that 2013-07-01 12:00:00. How would I do this? Thanks.

Upvotes: 9

Views: 24901

Answers (2)

northtree
northtree

Reputation: 9255

mysql:

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

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26333

You already have a lower limit on the date, but since your table can 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()

Upvotes: 27

Related Questions