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

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

This question is related to mysql datetime date

The answer is


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'

SELECT * FROM table WHERE timestamp >= '2012-05-05 00:00:00' 
    AND timestamp <= '2012-05-05 23:59:59'

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

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')

 WHERE cast(timestamp as date) = '2012-05-05'

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>='2012-05-05' AND date<'2012-05-06'

Use

SELECT * FROM table WHERE DATE(2012-05-05 00:00:00) = '2012-05-05' 

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 >= timestamp(CURRENT_DATE()) AND
    log_timestamp < 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.


Use a conversion function of MYSQL :

SELECT * FROM table WHERE DATE(timestamp) = '2012-05-05' 

This should work


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?