ActiveRecord按日期时间按时间查找记录 [英] Activerecord find records by time in datetime

问题描述

我正在尝试执行活动记录查询，该查询通过在datetime字段中查找按时间返回记录。具体来说，我想获取在凌晨1点到凌晨6点之间创建的所有记录，而不在乎它们的创建日期，只是确保时间在该范围内。

I am trying to perform an active record query that returns records by time by looking in the datetime field. Specifically I want to get all records that were created between 1am and 6am, not caring what day they were created just making sure that the time falls within the range.

因此，如果我有记录：

1: car=> "Honda", color: "blue", created_at => "2014-11-13 01:15:00"
2: car=> "Toyota", color: "red", created_at => "2014-11-17 04:15:00"
3: car=> "Honda", color: "red", created_at => "2014-11-20 10:15:00"

应该返回第一条记录和第二条记录。

The first and second records should be returned.

推荐答案

要直接执行此操作，您需要使用大量特定于供应商的SQL。

To do this directly you'll need to resort to a splash of vendor-specific SQL.

例如：

• postgresql： Car.where（'extract（hour from created_at）在1到6'之间）


• mysql： Car.where（'hour（created_at）在1到6'之间

• postgresql: Car.where('extract(hour from created_at) between 1 and 6')
• mysql: Car.where('hour(created_at) between 1 and 6')

这在大表上不会很快，除非您可以在表达式上建立索引。例如，如果您使用的是MySQL，则可能希望将小时存储在单独的列中，以便可以对其进行索引。然后，条件也将简化为 Car.where（created_hour：1..6）。

This won't be fast on a large table, however, unless you can index on the expression. If you are using MySQL, e.g., you might want to store the hour in a separate column so you can index on it. Then the condition would also simplify to Car.where(created_hour: 1..6).

这篇关于ActiveRecord按日期时间按时间查找记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！