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) = ?
EXPLAIN
SELECT * 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 ALL
ROWS FILTERED EXTRA
6671 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 < ?
EXPLAIN
SELECT * FROM test
WHERE timestamp >= CURDATE()
AND timestamp < CURDATE() + INTERVAL 1 DAY
ORDER 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 EXTRA
2 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