CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
Penguin
Penguin

Reputation: 953

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.

Upvotes: 0

Views: 102

Answers (2)

Adam Lassek
Adam Lassek

Reputation: 35505

Since you selected PostgreSQL as your database, you are in luck. There is a very easy, built-in way of doing this using the extract function.

Home.where("extract(hour from created_at) BETWEEN :min AND :max", min: 12, max: 16)
    .where("extract(minute from created_at) = 0")

If this is something you do a lot, perhaps a slightly more sophisticated scope would make sense:

class Home < ActiveRecord::Base
  scope :hour_range, ->(min, max){
    created_hour = Arel::Nodes::NamedFunction.new('extract', [Arel::Nodes::SqlLiteral.new('hour from "homes"."created_at"')])

    where created_hour.between(min..max)
  }

  scope :top_of_hour, ->{
    where Arel::Nodes::NamedFunction.new('extract', [Arel::Nodes::SqlLiteral.new('minute from "homes"."created_at"')]).eq(0)
  }
end

Yeah plain Arel is ugly without the ActiveRecord DSL on top, but it is vastly more powerful. I have seen plenty of situations where you simply have no choice but to color outside of ActiveRecord's lines, and knowing Arel can really help in those cases.

Upvotes: 1

Pravesh Khatri
Pravesh Khatri

Reputation: 2244

try this

    24.times do |t|
       result = Home.where(created_at: (Date.today.beginning_of_day+ (3600*t)))
       puts result
    end

Upvotes: 0

Related Questions