-1

I know how to find records within a date range, and have seen answers to similar questions. My question is how to reformat my date value from "mm/dd/yy" to a format which can be compared to a date range, all within one query.

I have a column in my database that contains a date in "mm/dd/yy" format. I am trying to write a query that will select all records within a date range defined by start_date and end_date variables, which are Ruby Date objects.

This is the best I have so far, but it's very slow:

date_records = Record.select { |r| ( (r.division == division) && (Date.strptime(r.date, "%m/%d/%y") >= start_date) && (Date.strptime(r.date, "%m/%d/%y") <= end_date) ) }
pyro_lemur
  • 104
  • 1
  • 1
  • 9
  • 1
    possibly a duplicate of http://stackoverflow.com/questions/2381718/rails-activerecord-date-between Is there a particular reason why you are using the SQL statement directly, instead of ActiveRecord query helpers? – Marco Sandrini Sep 02 '15 at 20:46
  • No reason... I'm fine using `.where`. My main struggle is getting the date from the "mm/dd/yy" format to compare within the query. This is the best I could do, and it's very slow: `date_records = Record.select { |r| ( (Date.strptime(r.date, "%m/%d/%y") >= start_date) && (Date.strptime(r.date, "%m/%d/%y") <= end_date) ) }` – pyro_lemur Sep 02 '15 at 21:10
  • Seems like the first job should be to store these dates as dates, not as strings. – David Aldridge Sep 02 '15 at 22:39

1 Answers1

-1

I found that converting my records to JSON before doing the .select that parses and compares the dates improved performance vastly (~35s down to ~4s):

division_records = Record.where(division: division).as_json date_range_records = division_records.select { |r| ((start_date..end_date).include? Date.strptime(r["date"], "%m/%d/%y")) }

pyro_lemur
  • 104
  • 1
  • 1
  • 9