mysql - 获取 3 天前整小时的结果

Question

我有一些带有日期时间列的表，我想从中获取所有结果，如下所示:

如果我在 2016-01-28 11:12:24 执行查询，我想获取 3 天前这一整小时 (11:00:00 )。因此，我会获取 2016-01-28 11:00:00 和 2016-01-28 11:59:59 之间的所有行。我该如何执行此操作，以便我可以从此日期时间列上的索引受益？

我尝试使用WHERE DATE_FORMAT(o.created_at, '%Y-%m-%d %H:00:00') = DATE_FORMAT(NOW() INTERVAL -3 DAY, '%Y-% m-%d %H:00:00') 但它是一个函数，因此查询不使用索引。

Answer 1

最好的解决方案是这样做:

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

或者，您也可以执行此操作，但精度只能达到秒:

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

这里的 BETWEEN AND 子句仍然使用索引，因为我们没有在 o.created_at 列上使用 DATE_FORMAT 函数。如果所选数据少于总行数的 1/3，效率也会更高。[引用:http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html ]

<小时/>

注:感谢@Used_By_Already以高达微秒的精度指出问题。