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

Answer 1

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'

Answer 2

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

Answer 3

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'