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

mysql datetime date
Michael picture Michael · May 7, 2012 · Viewed 178.2k times · Source

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

Marcus Adams picture Marcus Adams · May 7, 2012

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 an index on the timestamp column if you have one:

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

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

Read More