How do I write an activerecord query that only looks at the time component of a datetime field?

Question

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

e.g. Battle.where('start_time < ? and start_time > ?','12:00','06:00')

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.

Answer 1

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 )

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

EDIT

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.