Peter Marklund

Peter Marklund's Home

2009-01-06

Rails Timezone Gotcha: ActiveRecord::Base.find does not convert Time objects to UTC

With the timezone support introduced in Rails 2.1 the idea is that all dates in the database are stored in UTC and all dates in Ruby are in a local timezone. The local timezone can be specified by config.timezone in environment.rb or set to the user timezone with Time.zone= in a before filter. Typicaly, when reading/writing from/to the database ActiveRecord will transparently convert time attributes back and forth to UTC for you. However, there is a gotcha with datetimes in ActiveRecord::Base.find conditions. They will only be converted to UTC for you if they are ActiveSupport::TimeWithZone objects, not if they are Time objects. This means that you are fine if you use Time.zone.now, 1.days.ago, or Time.parse("2008-12-23").utc, but not if you use Time.now or Time.parse("2008-12-23"). Example:

  Event.all(:conditions => ["start_time > ?", Time.zone.now])
  Event.all(:conditions => ["start_time > ?", Time.parse("2008-12-23").utc])

Apparently this issue has been reported and marked as invalid. I think it's quite unfortunate that ActiveRecord doesn't do this conversion for us. I suspect other application developers will be bitten by this as well. The difference in behaviour between Time and TimeWithZone objects boils down to the to_s(:db) call:

>> Time.now.to_s(:db)
Time.now.to_s(:db)
=> "2009-01-06 17:52:19"
>> Time.zone.now.to_s(:db)
Time.zone.now.to_s(:db)
=> "2009-01-06 16:52:23"

One way to fix the issue would be to monkey patch the Quoting module in ActiveRecord like this:

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    module Quoting
      # Convert dates and times to UTC so that the following two will be equivalent:
      # Event.all(:conditions => ["start_time > ?", Time.zone.now])
      # Event.all(:conditions => ["start_time > ?", Time.now])
      def quoted_date(value)
        value.respond_to?(:utc) ? value.utc.to_s(:db) : value.to_s(:db)
      end
    end
  end
end

However I'm not sure that this is a good idea and that it won't break anything else. I've at least verified that it doesn't break assignment of ActiveRecord attributes.

18 comment(s)

Comments

Colin Kelley said 2010-12-02 22:42:

We've been through the same trauma, and just monkey-patched Rails 2.3.4 right at the source of the problem in activesupport/lib/active_support/core_ext/time/conversions.rb. We changed the last line of this method: def to_formatted_s(format = :default) return to_default_s unless formatter = DATE_FORMATS[format] formatter.respond_to?(:call) ? formatter.call(self).to_s : strftime(formatter) end to these 2 lines: def to_formatted_s(format = :default) return to_default_s unless formatter = DATE_FORMATS[format] instance = format == :db && ActiveRecord::Base.default_timezone == :utc ? dup.utc : self formatter.respond_to?(:call) ? formatter.call(instance).to_s : instance.strftime(formatter) end This does couple ActiveSupport to ActiveRecord, but that's ok for our project. To keep them completely decoupled there could always be a default_timezone setting in ActiveSupport.
--------------------------------------------------------------------------------

Lin Jen-Shin said 2010-11-11 07:55:

You might want to call dup on the value, because Time#utc has side-effect. It will change the time object which you've passed into the query. For example: t = Time.now # local time User.first(:conditions => {:created_at => t}) t # now it's an UTC time, and there's no way to going back. Change this: value.utc.to_s(:db) into this: value.dup.utc.to_s(:db) could solve this problem.
--------------------------------------------------------------------------------

Olivier Amblet said 2010-10-11 10:10:

As of Rails 3.0 at least you can also use: Time.zone.parse('2007-02-01 15:30:45') The goal is to have everything taking timezone into account a zone submodule, which is a good design I think.
--------------------------------------------------------------------------------

Cecile said 2010-07-29 16:16:

Thank you for this article, it saved me a lot of time
--------------------------------------------------------------------------------

direk indir said 2010-04-11 12:11:

very good website www.direkindir.gen.tr
--------------------------------------------------------------------------------

direk indir said 2010-04-11 12:10:

good thanks very good website
--------------------------------------------------------------------------------

Jamie Flournoy said 2010-04-09 18:38:

Awesome. I think you can use a SQL function like current_time() for a similar effect but that's pretty icky and DB-specific. Time.zone.now is much nicer.
--------------------------------------------------------------------------------

Malc said 2010-03-19 08:17:

Another victim here
--------------------------------------------------------------------------------

LeslieM said 2010-03-19 00:21:

Yep, me too!
--------------------------------------------------------------------------------

Frank said 2010-02-22 14:26:

Yep...people (me) still getting burned by this.
--------------------------------------------------------------------------------

Farhad said 2010-02-10 00:33:

Thanks - just got the error also.
--------------------------------------------------------------------------------

Franz Strebel said 2010-01-27 09:02:

Thanks for this post. This issue caused me some troubles yesterday.
--------------------------------------------------------------------------------

Richie Vos said 2009-12-01 20:38:

Thanks. This helped me figure out wtf was going on with my tests once I started making my app timezone aware.
--------------------------------------------------------------------------------

Adam Hill said 2009-11-11 06:22:

Thanks for this post, it had me stumped.
--------------------------------------------------------------------------------

Kent said 2009-09-05 16:39:

So good to know! Searched google for the error I got from rails 'ActiveSupport::TimeWithZone failed' and returned no results! But the answer was here in your blog. Thanks Mark!
--------------------------------------------------------------------------------

Daniel Fone said 2009-08-07 00:25:

Wow, just read this after spending an hour trying to figure out why my "daily" digest wasn't working. "I suspect other application developers will be bitten by this as well." Yep. :-S
--------------------------------------------------------------------------------

Rob Guthrie said 2009-07-04 09:26:

Thankyou so much for clarifying this for me.. Its nice to know timezones dont actually hate me... you saved me a lot of pain.
--------------------------------------------------------------------------------