CODEWITHSUNDEEP
ruby-on-railsrails-activerecordruby-on-rails-2
rtacconi
rtacconi

Reputation: 14769

Rails ActiveRecord date between

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 something like:

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

Upvotes: 217

Views: 229701

Answers (11)

Bismark
Bismark

Reputation: 740

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)

Upvotes: 50

ndbroadbent
ndbroadbent

Reputation: 13793

Just a note that the currently accepted answer is deprecated in Rails 3. You should do this instead:

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

Or, if you want to or have to use pure string conditions, you can do:

Comment.where('created_at BETWEEN ? AND ?', @selected_date.beginning_of_day, @selected_date.end_of_day)

Upvotes: 486

Augustin Riedinger
Augustin Riedinger

Reputation: 22148

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
  }

Upvotes: 4

nroose
nroose

Reputation: 1792

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!

Upvotes: 5

Jenorish
Jenorish

Reputation: 1714

You could use below gem to find the records between dates,

This gem quite easy to use and more clear By star am using this gem and the API more clear and documentation also well explained. 

Post.between_times(Time.zone.now - 3.hours,  # all posts in last 3 hours
                  Time.zone.now)

Here you could pass our field also Post.by_month("January", field: :updated_at)

Please see the documentation and try it.

Upvotes: 1

ben
ben

Reputation: 6180

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)

Upvotes: 5

Marshall Shen
Marshall Shen

Reputation: 1333

I would personally created a scope to make it more readable and re-usable:

In you Comment.rb, you can define a scope:

scope :created_between, lambda {|start_date, end_date| where("created_at >= ? AND created_at <= ?", start_date, end_date )}

Then to query created between:

@comment.created_between(1.year.ago, Time.now)

Hope it helps.

Upvotes: 61

Irukandji
Irukandji

Reputation: 1198

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

Upvotes: 27

boulder_ruby
boulder_ruby

Reputation: 39695

I ran this code to see if the checked answer worked, and had to try swapping around the dates to get it right. This worked--

Day.where(:reference_date => 3.months.ago..Time.now).count
#=> 721

If you're thinking the output should have been 36, consider this, Sir, how many days is 3 days to 3 people?

Upvotes: 11

kaushal sharma
kaushal sharma

Reputation: 231

Comment.find(:all, :conditions =>["date(created_at) BETWEEN ? AND ? ", '2011-11-01','2011-11-15'])

Upvotes: 7

klew
klew

Reputation: 14967

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

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

Upvotes: 4

Related Questions