6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Arif
  • 1,369
  • 14
  • 39

4 Answers4

5

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

Greg Navis
  • 2,818
  • 10
  • 10
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')
Charlie
  • 498
  • 2
  • 10
  • 24
1

Try this 

Order.where(order_date: Time.parse("7am")..Time.parse("9am"))
Ajay Barot
  • 1,681
  • 1
  • 21
  • 37
  • This seems to be incorrect as `Time` in Ruby is a date _and_ time. I tested it's incorrect locally. The generated query is `SELECT "teachers".* FROM "teachers" WHERE ("teachers"."created_at" BETWEEN $1 AND $2)` and `[["created_at", 2017-01-03 06:00:00 UTC], ["created_at", 2017-01-03 08:00:00 UTC]]` are substituted. Your query selects orders places _today_ between 7 am and 9 am. @Arif needs orders places on _any_ day. – Greg Navis Jan 03 '17 at 08:38
1

Use MySql HOUR function:

Order.where("HOUR(order_date) between 7 and 9")

Pay attention to the Time Zone you are using while querying!

Yuri Karpovich
  • 382
  • 4
  • 10