Rails 4 ActiveRecord Querying Between Times

Question

I have an orders table with order_date datetime column. Need a query that fetches records across all dates within a specific time range.

Example: fetch orders across all dates between 7am ET and 9am ET.

Something like:

Order.where('time(order_date) BETWEEN ? AND ?', '7am', '9am')

start and end times are coming from text fields where user can enter 2am, 3pm, 6am etc string values

Answer 1

Method 1: EXTRACT

You can use EXTRACT to get the hour from order_date:

Order.where('EXTRACT(hour FROM order_date) BETWEEN ? AND ?', 7, 20)

Note that I specified 20 (8 pm) as the upper range. If I used 21 then order_date with a time component of 21:30 would match which is not what we want.

You can wrap the query above in a scope:

class Order < ActiveRecord::Base
  scope :between, -> (start_hour, end_hour) {
    where('EXTRACT(hour FROM order_date) BETWEEN ? AND ?', start_hour, end_hour - 1)
  }
end

There are also some other concerns you may like to address, for example checking start_hour and end_hour for valid values (e.g an hour of -1 is invalid).

This method is simple but is limited to full hours (you can add minutes and seconds but the method would become convoluted).

Method 2: Explicit cast to time

Time in Ruby stores both a date and time. For example:

Time.parse('15:30:45') # => 2017-01-02 15:30:45 +0100

You can pass Time to PostgreSQL and cast it to Postgres time type explicitly:

class Order < ActiveRecord::Base
  scope :between, -> (start_time, end_time) {
    where('order_date::time BETWEEN ?::time AND ?::time', start_time, end_time)
  }
end

The advantage of this method is flexibility. The downside is a bit more complexity in handling the time in the UI. The scope expects a pair of Time objects. There are many ways to obtain them, for example: Time.parse('12:17').

Answer 2

As an example, this will search for orders between now and 1 month ago:

Order.where('order_date < ?', Time.now).where('order_date > ?', Time.now - 1.month)

Use Time#parse if you need to convert a string like "9am" into an object.

irb(main):006:0> Time.parse('9 AM')
=> 2016-12-27 09:00:00 -0700

The final code would probably look something like this:

morning = Time.parse('9 AM')
# 2016-12-27 09:00:00 -0700

night = Time.parse('10 PM')
# 2016-12-27 22:00:00 -0700

Order.where('order_date > ?', morning).where('order_date < ?', night)


Order Load (0.2ms)  SELECT `orders`.* FROM `orders ` WHERE (order_date > '2016-12-27 16:00:00.000000') AND (order_date < '2016-12-28 05:00:00.000000')