184

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'"
Al Fahad
  • 1,434
  • 3
  • 19
  • 33
rtacconi
  • 12,528
  • 19
  • 64
  • 83

11 Answers11

429

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)
thutt
  • 640
  • 5
  • 16
ndbroadbent
  • 12,589
  • 3
  • 53
  • 77
51

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.

Marshall Shen
  • 1,173
  • 11
  • 16
34

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)
Bismark
  • 580
  • 6
  • 9
  • 3
    Glorious. This is actually part of the ActiveSupport gem (and subsequently the entire ActiveRecord ecosystem), so it works outside Rails too! Just `require 'active_record'` and you're all set! – Master of Ducks Mar 25 '18 at 20:47
25

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

Pigueiras
  • 17,275
  • 9
  • 58
  • 83
Irukandji
  • 1,156
  • 9
  • 16
  • Ok but from the form I get this: {"written_at(4i)"=>"18", "written_at(5i)"=>"56", "content"=>"rrrrrr", "written_at(1i)"=>"2010", "written_at(2i)"=>"5", "written_at(3i)"=>"4"} How can I build an object to use beginning_of_day? – rtacconi Mar 04 '10 at 19:05
  • This is what I need: http://purab.wordpress.com/2009/06/16/deconstructing-date_select-in-rails/ – rtacconi Mar 04 '10 at 19:23
  • 2
    I like this method over the accepted answer because it doesn't rely on a db-level `date()` function; it's potentially more db independent. – Craig Walker Mar 18 '11 at 17:19
11

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?

boulder_ruby
  • 33,076
  • 8
  • 66
  • 91
7
Comment.find(:all, :conditions =>["date(created_at) BETWEEN ? AND ? ", '2011-11-01','2011-11-15'])
Aziz Shaikh
  • 15,104
  • 9
  • 55
  • 73
kaushal sharma
  • 231
  • 2
  • 7
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!

Sam Starling
  • 5,042
  • 3
  • 31
  • 52
nroose
  • 1,516
  • 2
  • 18
  • 26
4

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

Comment.all(:conditions => ["date(created_at) = ?", some_date])
klew
  • 14,349
  • 7
  • 45
  • 59
4

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)
ben
  • 5,240
  • 3
  • 31
  • 42
4

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
  }
Augustin Riedinger
  • 16,966
  • 22
  • 99
  • 173
1

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.

Jenorish
  • 1,664
  • 14
  • 19