我怎样写一个ActiveRecord查询，只有着眼于日期时间字段的时间部分？ [英] How do I write an activerecord query that only looks at the time component of a datetime field?

问题描述

是否有可能做一个ActiveRecord查询，只有着眼于日期时间字段的时间部分？

Is it possible to do an activerecord query that only looks at the time component of a datetime field?

例如。 Battle.where（START_TIME＆LT;和START_TIME＆GT;？，12:00，06:00'）

找早上6点和中午12点之间开始了，无论他们发生之日起的所有战斗？在这个例子中，start_time的被定义为一个日期时间。

to find all battles that were started between 6am and 12pm regardless of the day they occurred? In this example, start_time is defined as a datetime.

推荐答案

要做到这一点的唯一方法是使用SQL函数，如果你在MySQL中，你能做到这一点是这样的：

The only way to do this is using a SQL function, if you're on MySQL you could do it like this:

Battle.where( 'HOUR( start_time ) >= ? AND HOUR( start_time ) <= ?', 12, 6 )

但是，这是非常低效的，并且总是会生成一个全表扫描，然后你当然不希望出现这种情况。

But this is hugely inefficient and is always going to generate a full table scan and you surely don't want that.

最好的办法是用单独的小时值，在你的战斗模式，对其进行索引和查询，直接对他们增加一列是这样的：

The best solution is to add columns with the hour values alone at your battle model, index them and query directly on them like this:

Battle.where( 'start_time_hour >= ? start_time_hour <= ?', 12, 6 )

只需添加一个before_save回调节约的基础上START_TIME财产你的战斗模型之前设置这个值。

Just add an before_save callback that sets this values before saving your Battle model based on the start_time property.

修改

顺便说一句，如果你使用PostgreSQL，它能够创建一个函数的结果指标，所以，如果你不介意与PostgreSQL的坚持，你可以只使用SQL函数。而且我可以告诉大家，不，我不知道它是如何工作的，我只知道数据库允许你这样做，这不是用在MySQL。

BTW, if you're using PostgreSQL, it's capable of creating an index on the result of a function, so, if you don't mind having to stick with PostgreSQL you could just use the SQL function. And before you ask, no, i don't know how it works, I only know the database allows you to do so and that this is not available on MySQL.

这篇关于我怎样写一个ActiveRecord查询，只有着眼于日期时间字段的时间部分？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！