How to compare timestamp dates with date-only parameter in MySQL

Question

In a SQL statement  how do I compare a date saved as TIMESTAMP with a date in YYYY-MM-DD format  Ex   SELECT   FROM table WHERE timestamp    2012-05-25  I want this query returns all rows having timestamp in the specified day  but it returns only rows having midnight timestamp  thanks

User · Answer

When I read your question  I thought your were on Oracle DB until I saw the tag  MySQL   Anyway  for people working with Oracle here is the way   SELECT   FROM table where timestamp   to timestamp  21 08 2017 09 31 57    dd-mm-yyyy hh24 mi ss

User · Answer

In case you are using SQL parameters to run the query then this would be helpful  SELECT   FROM table WHERE timestamp between concat date           00 00 00   and concat date           23 59 59

User · Answer

Use a conversion function of MYSQL    SELECT   FROM table WHERE DATE timestamp     2012-05-05     This should work

User · Answer

You can use the DATE   function to extract the date portion of the timestamp  SELECT   FROM table WHERE DATE timestamp     2012-05-25   Though  if you have an index on the timestamp column  this would be faster because it could utilize an index on the timestamp column if you have one  SELECT   FROM table WHERE timestamp BETWEEN  2012-05-25 00 00 00  AND  2012-05-25 23 59 59

User · Answer

WHERE cast timestamp as date     2012-05-05

User · Answer

SELECT   FROM table WHERE timestamp  gt    2012-05-05 00 00 00       AND timestamp  lt    2012-05-05 23 59 59

User · Answer

Use    SELECT   FROM table WHERE DATE 2012-05-05 00 00 00     2012-05-05

User · Answer

As suggested by some  by using DATE timestamp  you are applying manipulation to the column and therefore you cannot rely on the index ordering   However  using BETWEEN would only be reliable if you include the milliseconds  In the example timestamp BETWEEN  2012-05-05 00 00 00  AND  2012-05-05 23 59 59  you exclude records with a timestamp between 2012-05-05 23 59 59 001 and 2012-05-05 23 59 59 999  However  even this method has some problems  because of the datatypes precision  Occasionally 999 milliseconds is rounded up   The best thing to do is   SELECT   FROM table WHERE date gt   2012-05-05  AND date lt  2012-05-06

User · Answer

As I was researching this I thought it would be nice to modify the BETWEEN solution to show an example for a particular non-static string date  but rather a variable date  or today s such as CURRENT DATE    This WILL use the index on the log timestamp column   SELECT   FROM some table WHERE     log timestamp     BETWEEN          timestamp CURRENT DATE         AND   Adds 23 9999999 HRS of seconds to the current date         timestamp DATE ADD CURRENT DATE    INTERVAL  86399 999999  SECOND MICROSECOND      I did the seconds microseconds to avoid the 12AM case on the next day  However  you could also do  INTERVAL  1 DAY  via comparison operators for a more reader-friendly non-BETWEEN approach   SELECT   FROM some table WHERE     log timestamp  gt   timestamp CURRENT DATE    AND     log timestamp  lt  timestamp DATE ADD CURRENT DATE    INTERVAL 1 DAY      Both of these approaches will use the index and should perform MUCH faster  Both seem to be equally as fast

[mysql] How to compare timestamp dates with date-only parameter in MySQL?

Examples related to mysql

Examples related to datetime

Examples related to date