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!

Advertisements

6 responses to “Querying for a date range (Ruby on Rails)

  1. Marco

    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 ?

  2. orly

    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

  3. Pingback: Scott Motte » Blog Archive » Rails find with conditional dates

  4. Pingback: dates and times « Darinmurray’s Weblog

  5. 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

  6. You could also do something like:

    named_scope :date_range, lambda { {:conditions => [“BETWEEN ? AND ?”, Time.now.beginning_of_day, 1.day.from_now.at_midnight]} }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: