Querying for a date range (Ruby on Rails)
Date ranges are very odd when you first scratch your head at them. Sometimes the first thought that comes to mind might be a solution that would select every single date, one by one in a loop.
It doesn’t have to be this way — with a date() column, it is possible to do a one line select of a range of dates, and return the rows in-between.
def self.date_range(from,to)
Something.find(:all, :conditions => [ "BETWEEN ?
AND ?", from, to])
end
Drop that into a model, and where you need to, reference it:
@some_things = Something.date_range(from,to)
Update: As of 06-02-07 (Feb 06, 2007), you no longer need to do this. Its now built in if you upgrade to the latest and greatest Rails. The Ruby on Rails weblog gives the following example:
We’ve also brought over the enhancement to :conditions in Active Record that’ll allow you to pass in ranges and get them automatically converted to BETWEEN statements. Like:
Student.find(:all, :conditions => { :grade => 9..12 })…which then becomes:
"SELECT * FROM students WHERE grade BETWEEN 9 AND 12"
Great!
How do we declare a date ? that is the difficult question to search trought all the web. I mean 26-11-07..26-12-07 ? or 261107..261207 or 20071126..20071226 or 26/11/07..26/12/07 … and continues… but what’s right ?
Marco
May 2, 2007 at 10:47 pm
Go by the database format, (”date”) which is defined as YEAR-MONTH-DAY.. Then use the above finder to create a BETWEEN select/update/delete query
orly
May 2, 2007 at 10:51 pm
[...] To use the rails find method with conditional dates do the following. inspiration from here [...]
Scott Motte » Blog Archive » Rails find with conditional dates
May 30, 2008 at 9:06 pm
[...] Selecting a date range: http://morecode.wordpress.com/2007/01/14/querying-for-a-date-range-ruby-on-rails/ [...]
dates and times « Darinmurray’s Weblog
November 1, 2008 at 4:18 am
I have published a gem to GitHub that allows you to use ActiveRecord::Base to do this instead.
Model.find_all_by_date_range(1.month.ago,Time.now,{:limit => 20})
To install and use the gem simply type
gem install find_all_by_date_range –source http://gems.github.com
Happy coding everyone!
Tim
Tim Matheson
February 5, 2009 at 9:56 pm