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

Or you could use the CURRENT_DATE alternative, with the same result:

SELECT * FROM yourtable WHERE created >= CURRENT_DATE

Examples from database.guide

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

Related

Recent Posts