Mysql Query back to midnight only

Question

I have this query that looks up results from a database for the last twenty minutes, now i know how to look up in hours, days, etc, but is it possible to look up only as far back as midnight of that day. so when ever the query is run and what ever time it only looks back as far as midnight?

SELECT * FROM ip_stats WHERE date >= ( NOW() - INTERVAL 20 MINUTE ) and ip='$ip'

This is my code but is there away in which i can replace the interval for a specific time.

Any help would be appreciated.

Solution 1

Looking back to midnight of the current day is the same as looking at the current date with no time component. You can therefore use DATE() to truncate the datetime column date to only the date portion, and compare it to CURDATE().

SELECT * FROM ip_stats WHERE DATE(date) = CURDATE() and ip='$ip'

Solution 2

SELECT * FROM ip_stats WHERE date >= ( NOW() - INTERVAL 20 MINUTE ) AND date >= CURDATE() and ip='$ip'

Comments

[Jason]

you're going to want a >=, not a >

[Admin]

that worked perfect thank you, i don't know why i didn't think of that.

[The Onin]

I think this is the perfect solution for getting records from the current day.