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

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

回答1:

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)

回答2:

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.

回答3:

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

回答4:

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)

回答5:

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?

回答6:

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

回答7:

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!

回答8:

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

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

回答9:

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)

回答10:

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
  }

回答11:

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.