CODEWITHSUNDEEP
mysql
user99999
user99999

Reputation: 2024

Get results from whole hour 3 days ago

I have some table with a datetime column, and I want to fetch from it all results like this:

If I execute the query on 2016-01-28 11:12:24, I want to fetch all results from 3 days ago from that whole hour (11:00:00). So I'd get all rows between 2016-01-28 11:00:00 and 2016-01-28 11:59:59. How do I do this, so that I can benefit from index on this datetime column?

I have tried using WHERE DATE_FORMAT(o.created_at, '%Y-%m-%d %H:00:00') = DATE_FORMAT(NOW() INTERVAL -3 DAY, '%Y-%m-%d %H:00:00') but it is a function, so the query doesn't use an index.

Upvotes: 0

Views: 59

Answers (2)

Sameer Mirji
Sameer Mirji

Reputation: 2245

The best solution is to do this:

WHERE o.created_at >= DATE_FORMAT(date_sub(now(), interval 3 day), '%Y-%m-%d %H:00:00') AND o.created_at < DATE_FORMAT(date_add(date_sub(now(), interval 3 day), interval 1 hour), '%Y-%m-%d %H:00:00')

Alternately, you can also do this but only with precision up to seconds:

WHERE o.created_at BETWEEN DATE_FORMAT(date_sub(now(), interval 3 day), '%Y-%m-%d %H:00:00') AND DATE_FORMAT(date_sub(now(), interval 3 day), '%Y-%m-%d %H:59:59')

The BETWEEN AND clause here still utilizes the Index as we have not used DATE_FORMAT function on the column o.created_at. It is also much more efficient if the selected data is less than 1/3rd of the total rows. [Ref: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html ]

Note: Thanks to @Used_By_Already for pointing out the issue with precision up to microseconds.

Upvotes: 1

Simply Me
Simply Me

Reputation: 1587

Instead of formating a new date with NOW() Interval - 3...., take a look at DateDiff(). This allows you to set the difference from NOW() to other days and set the measure unit.

Upvotes: 0

Related Questions