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

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 ?

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

If you want to compare with a particular date , You can directly write it like :

select * from `table_name` where timestamp >= '2018-07-07';

// here the timestamp is the name of the column having type as timestamp

or

For fetching today date , CURDATE() function is available , so :

select * from `table_name` where timestamp >=  CURDATE();

This could be the easiest in my opinion:

SELECT * FROM `table` WHERE `timestamp` like concat(CURDATE(),'%');

Simply cast it to a date:

SELECT * FROM `table` WHERE CAST(`timestamp` TO DATE) == CAST(NOW() TO DATE)

How many ways can we skin this cat? Here is yet another variant.

SELECT * FROM table WHERE DATE(FROM_UNIXTIME(timestamp)) = '2015-11-18';

On Visual Studio 2017, using the built-in database for development I had problems with the current given solution, I had to change the code to make it work because it threw the error that DATE() was not a built in function.

Here is my solution:

where CAST(TimeCalled AS DATE) = CAST(GETDATE() AS DATE)

SELECT * FROM table WHERE FROM_UNIXTIME(your_column_with_unix_time,'%Y-%m-%d') = CURDATE()