Rails ActiveRecord date between

Question

I need to query comments made in one day. The field is part of the standard timestamps, is created_at. The selected date is coming from a date_select. How can I use ActiveRecord to do that?

I need somthing like:

"SELECT * FROM comments WHERE created_at BETWEEN '2010-02-03 00:00:00' AND '2010-02-03 23:59:59'"

Answer 1

there are several ways. You can use this method:

start = @selected_date.beginning_of_day
end = @selected_date.end_of_day
@comments = Comment.where("DATE(created_at) BETWEEN ? AND ?", start, end)

Or this:

@comments = Comment.where(:created_at => @selected_date.beginning_of_day..@selected_date.end_of_day)

Answer 2

If you only want to get one day it would be easier this way:

Comment.all(:conditions => ["date(created_at) = ?", some_date])

Answer 3

Rails 5.1 introduced a new date helper method all_day, see: https://github.com/rails/rails/pull/24930

>> Date.today.all_day
=> Wed, 26 Jul 2017 00:00:00 UTC +00:00..Wed, 26 Jul 2017 23:59:59 UTC +00:00

If you are using Rails 5.1, the query would look like:

Comment.where(created_at: @selected_date.all_day)

Answer 4

This code should work for you:

Comment.find(:all, :conditions => {:created_at => @selected_date.beginning_of_day..@selected_date.end_of_day})

For more info have a look at Time calculations

Note: This code is deprecated. Use the code from the answer if you are using Rails 3.1/3.2

Answer 5

I have been using the 3 dots, instead of 2. Three dots gives you a range that is open at the beginning and closed at the end, so if you do 2 queries for subsequent ranges, you can't get the same row back in both.

2.2.2 :003 > Comment.where(updated_at: 2.days.ago.beginning_of_day..1.day.ago.beginning_of_day)
Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE ("comments"."updated_at" BETWEEN '2015-07-12 00:00:00.000000' AND '2015-07-13 00:00:00.000000')
=> #<ActiveRecord::Relation []> 
2.2.2 :004 > Comment.where(updated_at: 2.days.ago.beginning_of_day...1.day.ago.beginning_of_day)
Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE ("comments"."updated_at" >= '2015-07-12 00:00:00.000000' AND "comments"."updated_at" < '2015-07-13 00:00:00.000000')
=> #<ActiveRecord::Relation []> 

And, yes, always nice to use a scope!

Answer 6

There should be a default active record behavior on this I reckon. Querying dates is hard, especially when timezones are involved.

Anyway, I use:

  scope :between, ->(start_date=nil, end_date=nil) {
    if start_date && end_date
      where("#{self.table_name}.created_at BETWEEN :start AND :end", start: start_date.beginning_of_day, end: end_date.end_of_day)
    elsif start_date
      where("#{self.table_name}.created_at >= ?", start_date.beginning_of_day)
    elsif end_date
      where("#{self.table_name}.created_at <= ?", end_date.end_of_day)
    else
      all
    end
  }