How to Properly Convert or Query Date Range for Rails / MySQL DateTime Column

Question

I have a rails application where I store created_at as datetime (standard). I am building a form for searching and I find I have to use find_by_sql to do some complex subqueries. The form has a date range (no time) to search on for items created_at field.

The problem I find is that if I pass in just the date string for range to query...

... status_changes.created_at between '2009-01-24' and '2009-03-12' ...

I am getting back records that have a created_at date of 2009-01-23 17:10:39 -0800 because this is stored in the db as 2009-01-24 01:10:39 (UTC)

How can I fix this so that the result is not returning the record in question?

It seems I either need to convert the date range to be UTC specific or tell the find_by_sql to search based on current time zone instead of reading the column as utc...

Any takers?

John

Answer 1

The modern ActiveRecord way of doing this is:

Model.where(time_field: date1..date2)

Answer 2

If you don't use find_by_sql, but rather use a find with a :conditions clause that let's Rails do substitutions, it will convert everything automatically.

Model.find :all, 
  :conditions => ["created_at between ? and ?", start_date, end_date]

Worst case, if Rails is confused by the dates, you can convert them to times and it should play nicely:

Model.find :all, 
  :conditions => ["created_at between ? and ?", 
                   start_date.to_time, end_date.to_time]