CODEWITHSUNDEEP
mysqldatetimedate
Michael
Michael

Reputation: 1328

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

Upvotes: 117

Views: 305650

Answers (11)

MUGABA
MUGABA

Reputation: 891

Let me leave here it may help someone For people coming from nodejs and expressjs

  getDailyIssueOperations(dateName, date, status) {
    const queryText = `
    select count(*) as total from issues
    where date(${dateName})='${date}' and status='${status}';
    `;
  },

in case date and column name are variables please find the implementation usefull

Upvotes: 0

Jhamman Sharma
Jhamman Sharma

Reputation: 167

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

It will work but not used index on "timestamp" column if you have any because of DATE function. below query used index and give better performance

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

OR

SELECT * FROM table
WHERE timestamp >= '2012-05-05' AND timestamp < '2012-05-06'

Try running these to check stats

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

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

Upvotes: 2

Marcus Adams
Marcus Adams

Reputation: 53840

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'

Upvotes: 190

asnyder
asnyder

Reputation: 711

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.

Upvotes: 1

Muhammad Fahad
Muhammad Fahad

Reputation: 1412

Use 

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

Upvotes: 0

Tom Kitson
Tom Kitson

Reputation: 161

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'

Upvotes: 16

KeyMaker00
KeyMaker00

Reputation: 6462

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

Upvotes: 0

devesh
devesh

Reputation: 106

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

Upvotes: 0

Cfreak
Cfreak

Reputation: 19309

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

Upvotes: 7

juergen d
juergen d

Reputation: 204766

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

Upvotes: 13

adrien
adrien

Reputation: 4439

Use a conversion function of MYSQL :

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

This should work

Upvotes: 2

Related Questions