CODEWITHSUNDEEP
mysqlsqltimestamp
Jackie Honson
Jackie Honson

Reputation: 1459

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 ?

Upvotes: 139

Views: 274191

Answers (10)

Darksymphony
Darksymphony

Reputation: 2683

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

Upvotes: 0

John Woo
John Woo

Reputation: 263703

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

Upvotes: 240

kochauf
kochauf

Reputation: 356

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

SELECT * FROM yourtable WHERE created >= CURRENT_DATE

Examples from database.guide

Upvotes: 4

ViditAgarwal
ViditAgarwal

Reputation: 49

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();

Upvotes: 3

RedNet
RedNet

Reputation: 52

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)

Upvotes: 0

Jerry Jones
Jerry Jones

Reputation: 796

This could be the easiest in my opinion:

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

Upvotes: 2

Stephane Gosselin
Stephane Gosselin

Reputation: 9148

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

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

Upvotes: 1

Hamed Persia
Hamed Persia

Reputation: 285

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

Upvotes: 15

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 79

MarcDefiant
MarcDefiant

Reputation: 6889

Simply cast it to a date:

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

Upvotes: 1

Related Questions