mysql - How to select rows that have current day's timestamp?

Question

I am trying to select only today's records from a database table.

Currently I use

SELECT * FROM `table` WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 1 DAY));

But this takes results for the last 24 hours, and I need it to only select results from today, ignoring the time. How can I select results based on the date only ?

Answer 1

use DATE and CURDATE()

SELECT * FROM `table` WHERE DATE(`timestamp`) = CURDATE()

Warning! This query doesn't use an index efficiently. For the more efficient solution see the answer below

see the execution plan on the DEMO