How to correctly convert or set the date range for DateTime Rails / MySQL columns

Question

I have a rails application where I store created_at as datetime (standard). I am creating a search form and I believe that I need to use find_by_sql to execute complex subqueries. The form has a date range (no time) for searching the created_at fields.

The problem I find is that if I only pass a date string for a range request ...

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

I am returning records that have the created_at date 2009-01-23 17:10:39 -0800, because it is stored in db as 2009-01-24 01:10:39 (UTC)

How can I fix this so that the result does not return this record?

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

Any members?

John

Answer 1

I know this is an old question, but in response to a Streamlines request for CONVERT_TZ:

If you do not have mySQL time zone tables loaded (which is rather unlikely when installing on vanilla), you need to enter time intervals as an offset from UTC.

 CONVERT_TZ(status_changes.created_at, '+00:00', '+08:00') between '2009-01-24' and '2009-03-12') 

Answer 2

The modern way ActiveRecord:

 Model.where(time_field: date1..date2) 

Answer 3

If you do not use find_by_sql , but rather use find with the :conditions clause, which allows Rails to do replacements, it will automatically convert everything.

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

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

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

Answer 4

MySQL has a CONVERT_TZ function that takes time and converts it from one time zone to another. You can create your request to convert from a stored value (UTC) to your local time zone (PST).

 CONVERT_TZ(status_changes.created_at,'UTC',?) between ? and ?, 'PST, '2009-01-24', '2009-03-10' 

Answer 5

I tried to answer Ian, but it didn’t quite work. Rails correctly handles the time zone when records are saved and loaded, but do not seem to do the same for query parameters. Please note that the application I'm working on is in Rails 2.1.1, so the situation may improve a little since then.

With one small tweak, I was able to work with Ian: Get the time value in the UTC timezone using the getutc method, and then pass this as a parameter. This should also not be harmful to any later versions of Rails that can correctly handle the time zone, since UTC does not change using getutc, but just the time zone.

Answer 6

Since Rails stores dates in UTC in the database, you must use the utc method on Time or DateTime

i.e.

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

Answer 7

 Model.where(:from => "2012-01-01".to_date.."2012-06-30".to_date) 

worked for me (: from this is a database field of type 'date')