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

MySQL has a CONVERT_TZ function that takes a datetime and converts it from one timezone to another. You could build your query to convert from the stored value (UTC) to your local timezone (PST).

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

Answer 2

Tvanfosson,

Thanks... but when I try adding that to my (edited from original post) sql line

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

I get zero results (there should be some)... is this correct implementation?

Answer 3

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]

Answer 4

I know that this is an old question, but in answer to Streamlines query about CONVERT_TZ:

Unless you have the mySQL named timezone tables loaded (which is pretty unlikely on a vanilla install), you need to enter timezones as an offset from UTC.

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

Answer 5

I tried Ian's answer, but it did not quite work. Rails is correctly handling the timezone when records are saved and loaded, but doesn't seem to do the same for query parameters. Note that the application I'm working on is in Rails 2.1.1, so things may have improved somewhat since then.

With one small tweak, I was able to make Ian's solution work though: Get the time value in the UTC timezone using the getutc method, and then pass that as the parameter. This should also not cause any harm in any later versions of Rails that may handle the time parameter zone correctly, since UTC time is not altered by getutc, just the timezone.

Answer 6

Because Rails stores the dates in UTC in the database you should use the utc method on Time or DateTime

i.e.

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