How to compare a string to dates in a find clause ?

Question

Hi,

I want to get models whom date is within a date range. So I want to do something like

MyModel.find_all_by_field1_id_and_field2_id(value1, value2, :conditions => { :date => nb_days_ago..Date.yesterday })

The thing is, the date attribute of my model is a string (with the format "08-24-2010"), and I can't modify this. So to compare it to my range of dates, I tried this:

MyModel.find_all_by_field1_id_and_field2_id(value1, value2, :conditions => { Date.strptime(:date, "%m-%d-%Y") => nb_days_ago..Date.yesterday })

But I get an error that basically says that strptime can't process the :date symbol. I think my solution is not good.

How can I compare my string to my range of dates ?

Thanks

Answer 1

First of all I do not envy your situation. That's a pretty ugly date format. The only thing I can think of is to generate an array of strings, in that format, representing ALL the days between your starting date and your finish date, then use the SQL "IN" syntax to find dates in that set (which you can do from within ActiveRecord's :conditions param).

For example, if you wanted to search to 10 days ago:

num = 10 #number of days ago for search range
# range starts at 1 because you specified yesterday
matching_date_strings = (1..num).to_a.map{|x| x.days.ago.strftime("%m-%d-%Y")}
=> ["08-24-2010", "08-23-2010", "08-22-2010", "08-21-2010", "08-20-2010"]
# then...
records = MyModel.all(:conditions => { :date => matching_date_strings })
# or in your case with field1 and field2
records = MyModel.find_all_by_field1_id_and_field2_id(value1, value2, :conditions => { :date => matching_date_strings })

The idea is this should generate SQL with something like "... WHERE date IN ("08-24-2010", "08-23-2010", "08-22-2010", "08-21-2010", "08-20-2010")

Answer 2

You have to convert the DB string to date in the database rather than in Ruby code:

Model.all(:conditions => [ "STR_TO_DATE(date,'%m-%d-%Y') BETWEEN ? AND ? ",
               nb_days_ago, Date.yesterday])

Better solution is to normalize your model by adding a shadow field.

class Model

  after_save :create_shadow_fields
  def create_shadow_fields
    self.date_fld = Date.strptime(self.date_str, "%m-%d-%Y")
  end
end

Now your query can be written as follows:

Model.all(:conditions => {:date_fld => nb_days_ago..Date.yesterday})

Don't forget to add an index on the date_fld column.

Edit 1

For SQLLite, first solution can be rewritten as follows:

Model.all(:conditions => [ "STRFTIME('%m-%d-%Y', date) BETWEEN ? AND ? ",
               nb_days_ago, Date.yesterday])