从日期时间获取所有结果

Question

I would like to be able to get all the results from a MySql database that have the same day as a certain DateTime.

For example if the DateTime's date is "2/5/2018 10:15pm" all MySql results who's DateTime day is the 5th and in the same month and year (Ignoring the time) are returned.

I tried using

SELECT * FROM posts WHERE timestamp=:timestamp, Array(':timestamp' => $targetTime)

But that only gives results with the exact same DateTime without ignoring the time.

Answer 1

Use >= and < so it will preserve the index if you have.

WHERE timestamp >= DATE(:timestamp) 
      AND timestamp < DATE(:timestamp) + INTERVAL 1 DAY

DATE() will strip the time.

Here's a Demo.