Michael Michael - 11 days ago 5
MySQL Question

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


I want this query returns all rows having timestamp in the specified day, but it returns only rows having midnight timestamp.

thanks

Answer

You can use the DATE() function to extract the date portion of the timestamp:

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

Though, if you have an index on the timestamp column, this would be faster because it could utilize the index:

SELECT * FROM table
WHERE timestamp BETWEEN '2012-05-05 00:00:00' AND '2012-05-05 23:59:59'