How to search for a date in timestamp field?

Question

I have a database and a table with below attributes:

Column: Date 
Type: TIMESTAMP 
Attributes: ON UPDATE CURRENT_TIMESTAMP 
Default: CURRENT_TIMESTAMP

So each data will showing up in this format: "2013-07-06 14:32:18"

I'm looking for a SQL query that shows retrieves rows in "2013-07-06".

What I'm tried:

select * from posts where date between "2013-07-05 00:00:00" and "2013-07-07 00:00:00";

Answer 1

You can use MySQL's DATE() function to obtain only the date part of your TIMESTAMP:

SELECT * FROM posts WHERE DATE(date) = '2013-07-06'

However, such a query cannot benefit from an index on the date column (if one exists); instead, one can use a range as follows:

SELECT * FROM posts WHERE date >= '2013-07-06' AND date < '2013-07-07'

MySQL will implicitly add a time of 00:00:00 to the date literals, but you can include them explicitly if so preferred.

Note that BETWEEN ... AND ... is inclusive of its operands, so BETWEEN "2013-07-05 00:00:00" AND "2013-07-07 00:00:00" will include all records on 2013-07-05 and 2013-07-06 as well as any records at 00:00:00 on 2013-07-07.