Penguin Penguin - 6 months ago 18
Ruby Question

Rails query between time but specify term

I want to query records but have specify term. For example

Home.where(created_at: Time.parse("12pm")..Time.parse("4:00pm"))


this active record will get all records from 12:00pm to 4:30pm. And result will be bellow records.

id | start_at
---+---------------------
1 | 2008-03-03 12:00:00
2 | 2008-03-04 12:10:00
3 | 2008-03-05 12:30:00
4 | 2008-03-08 12:50:00
5 | 2008-03-09 13:00:00
6 | 2008-03-10 13:10:00
7 | 2008-03-13 13:20:00
8 | 2008-03-14 13:50:00
9 | 2008-03-14 14:00:00
10 | 2008-03-14 14:10:00
11 | 2008-03-14 14:30:00
12 | 2008-03-14 14:50:00
13 | 2008-03-14 15:00:00
14 | 2008-03-14 15:30:00
15 | 2008-03-14 15:50:00
16 | 2008-03-14 16:00:00
17 | 2008-03-14 16:10:00


But I want to get all records but each 1 hours, like below result.

id | start_at
---+---------------------
1 | 2008-03-03 12:00:00
5 | 2008-03-09 13:00:00
9 | 2008-03-14 14:00:00
13 | 2008-03-14 15:00:00
16 | 2008-03-14 16:00:00


then how can I query with active record? Thanks.

Answer

You might have a tricky time creating a query for this. Keep in mind that your database likely stores time/date values as an integer, and has no understanding of 1728000 and 1814400 being the same time on different days, where we might see it as "2016-05-04 12PM" and "2016-05-05 12PM".

Depending on your dataset, I see two likely options:

  1. Create an array of values to query with

    def query_hours(start_date, end_date, start_hour, end_hour)
      cur_date = start_date
      range = []
    
      while cur_date <= end_date
        if cur_date.hour >= start_hour && cur_date.hour <= end_hour
          range << cur_date
        end
        cur_date += 3600
      end
    
      range
    end
    
    Home.where created_at: query_hours(
      Date.new(2016, 5, 1),
      Date.new(2016, 5, 31),
      12,
      16
    )
    

    This is messy, and is going to be horribly inefficient if you are only querying small portions of the day. You could optimize it to only loop through your set hour range, though.

  2. I'm guessing your best bet, honestly, would be adding a created_hour attribute to your Home model, and directly querying it. If this is an important query, it's the most efficient way to solve the problem

    # in your model class
    class Home
      after_commit :update_created_hour
    
      def update_created_hour
        if created_at.minute = 0 && created_at.sec == 0
          self.created_hour = created_at.hour
        else
          self.created_hour = nil
        end
        save!
      end
    end
    
    # to query
    from = Time.parse("12pm").hour
    to = Time.parse("8pm").hour
    Home.where('created_hour >= ? AND created_hour <= ?', from, to)