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

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

If you want an index to be used and the query not to do a table scan:

WHERE timestamp >= CURDATE()  AND timestamp < CURDATE() + INTERVAL 1 DAY

To show the difference that this makes on the actual execution plans, we'll test with an SQL-Fiddle (an extremely helpful site):

CREATE TABLE test                            --- simple table    ( id INT NOT NULL AUTO_INCREMENT    ,`timestamp` datetime                    --- index timestamp    , data VARCHAR(100) NOT NULL           DEFAULT 'Sample data'    , PRIMARY KEY (id)    , INDEX t_IX (`timestamp`, id)    ) ;INSERT INTO test    (`timestamp`)VALUES    ('2013-02-08 00:01:12'),    ---                                      --- insert about 7k rows    ('2013-02-08 20:01:12') ;

Lets try the 2 versions now.

Version 1 with DATE(timestamp) = ?

EXPLAINSELECT * FROM test WHERE DATE(timestamp) = CURDATE()            ---  using DATE(timestamp)ORDER BY timestamp ;

Explain:

ID  SELECT_TYPE  TABLE  TYPE  POSSIBLE_KEYS  KEY  KEY_LEN  REF 1   SIMPLE       test   ALLROWS  FILTERED  EXTRA6671  100       Using where; Using filesort

It filters all (6671) rows and then does a filesort (that's not a problem as the returned rows are few)

Version 2 with timestamp <= ? AND timestamp < ?

EXPLAINSELECT * FROM test WHERE timestamp >= CURDATE()  AND timestamp < CURDATE() + INTERVAL 1 DAYORDER BY timestamp ;

Explain:

ID  SELECT_TYPE  TABLE  TYPE  POSSIBLE_KEYS  KEY  KEY_LEN  REF 1   SIMPLE       test   range t_IX           t_IX    9 ROWS  FILTERED  EXTRA2     100       Using where

It uses a range scan on the index, and then reads only the corresponding rows from the table.

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

it is shorter , there is no need to use 'AND timestamp < CURDATE() + INTERVAL 1 DAY'

because CURDATE() always return current day

MySQL CURDATE() Function