Filtering Active Records by date

rubyonrails-talk
1

How do you filter active records by date? I'm trying to get only entries where the date is between two other dates, but the filtering in the code below does not actually filter anything:

@from_date = Date.new(params[:from_date][:year].to_i, params[:from_date][:month].to_i, params[:from_date][:day].to_i) @to_date = Date.new(params[:to_date][:year].to_i, params[:to_date] [:month].to_i, params[:to_date][:day].to_i) @entries = @account.entries.find_all{|entry|entry.date > @from_date and entry.date < @to_date}

@entries contains everything from @account.entries, and does not filter anything out, regardless of the dates. If I use the ruby script/console, and pull out a random entry from the database. I can compare its date with a created date on the console, yielding a correct true or false, but inside of find_all, I do not get the correct results.

I've tried "hardcoding" the @from_date and @to_date like so: (@from_date = Date.new(2007,9,1), but it seems to have no effect. The date field on entries is of type "date" (it was created using a migration). I'm using SQLite as the database.

Any help is appreciated!

2

How do you filter active records by date? I'm trying to get only entries where the date is between two other dates, but the filtering in the code below does not actually filter anything:

@from_date = Date.new(params[:from_date][:year].to_i, params[:from_date][:month].to_i, params[:from_date][:day].to_i) @to_date = Date.new(params[:to_date][:year].to_i, params[:to_date] [:month].to_i, params[:to_date][:day].to_i) @entries = @account.entries.find_all{|entry|entry.date > @from_date and entry.date < @to_date}

You're using a deprecated form of the API here and syntactically giving a block (which will be ignored). Try this:

@entries = @account.entries.find(:all,                              :conditions => [ "date BETWEEN ? and ?",                                               @from_date.to_s(:db),                                               @to_date.to_s(:db) ])

@entries contains everything from @account.entries, and does not filter anything out, regardless of the dates. If I use the ruby script/console, and pull out a random entry from the database. I can compare its date with a created date on the console, yielding a correct true or false, but inside of find_all, I do not get the correct results.

I've tried "hardcoding" the @from_date and @to_date like so: (@from_date = Date.new(2007,9,1), but it seems to have no effect. The date field on entries is of type "date" (it was created using a migration). I'm using SQLite as the database.

Any help is appreciated!

I hope this helps! The SQL syntax for something like this should be the same as MySQL (which I use).

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

3

I am filtering records by date with the find method and with the newer API mentioned above, but I do not convert the date to a string with the 'to_s' method. I believe that Active Record with the 'find' method does this for you. If, on the other hand, you were to use 'find_by_sql', you would need to format the date as a string with single quotes and in a format that your particular database can recognize and evaluate correctly. For that reason, the 'find_by_sql' method seems less portable to another database whose date string formats may not completely overlap with anothers' formats.