How to select rows that have current day s timestamp

Question

I am trying to select only today s records from a database table   Currently I use  SELECT   FROM  table  WHERE   timestamp   gt  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

User · Answer

Or you could use the CURRENT DATE alternative  with the same result   SELECT   FROM yourtable WHERE created  gt   CURRENT DATE   Examples from database guide

User · Answer

How many ways can we skin this cat   Here is yet another variant   SELECT   FROM table WHERE DATE FROM UNIXTIME timestamp      2015-11-18

User · Answer

If you want an index to be used and the query not to do a table scan   WHERE timestamp  gt   CURDATE     AND timestamp  lt  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  lt     AND timestamp  lt     EXPLAIN SELECT   FROM test  WHERE timestamp  gt   CURDATE     AND timestamp  lt  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

User · Answer

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

User · Answer

SELECT   FROM  table  WHERE timestamp  gt   CURDATE     it is shorter   there is no need to use  AND timestamp  lt  CURDATE     INTERVAL 1 DAY   because CURDATE   always return current day   MySQL CURDATE   Function

User · Answer

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

User · Answer

If you want to compare with a particular date   You can directly write it like    select   from  table name  where timestamp  gt    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  gt    CURDATE

User · Answer

This could be the easiest in my opinion   SELECT   FROM  table  WHERE  timestamp  like concat CURDATE

User · Answer

Simply cast it to a date   SELECT   FROM  table  WHERE CAST  timestamp  TO DATE     CAST NOW   TO DATE

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

Examples related to mysql

Examples related to sql

Examples related to timestamp