Peter Marklund

Peter Marklund's Home

Tue January 06, 2009
Programming

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.

Comments

Rob Guthrie said over 5 years ago:

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

--------------------------------------------------------------------------------

Daniel Fone said over 5 years ago:

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

--------------------------------------------------------------------------------

Kent said over 4 years ago:

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!

--------------------------------------------------------------------------------

Adam Hill said over 4 years ago:

Thanks for this post, it had me stumped.

--------------------------------------------------------------------------------

Richie Vos said over 4 years ago:

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

--------------------------------------------------------------------------------

Franz Strebel said over 4 years ago:

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

--------------------------------------------------------------------------------

Farhad said over 4 years ago:

Thanks - just got the error also.

--------------------------------------------------------------------------------

Frank said over 4 years ago:

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

--------------------------------------------------------------------------------

LeslieM said over 4 years ago:

Yep, me too!

--------------------------------------------------------------------------------

Malc said over 4 years ago:

Another victim here

--------------------------------------------------------------------------------

Jamie Flournoy said over 4 years ago:

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.

--------------------------------------------------------------------------------

direk indir said over 4 years ago:

good thanks very good website

--------------------------------------------------------------------------------

direk indir said over 4 years ago:

very good website www.direkindir.gen.tr

--------------------------------------------------------------------------------

Cecile said over 4 years ago:

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

--------------------------------------------------------------------------------

Olivier Amblet said over 3 years ago:

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.

--------------------------------------------------------------------------------

Lin Jen-Shin said over 3 years ago:

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.

--------------------------------------------------------------------------------

Colin Kelley said over 3 years ago:

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.

--------------------------------------------------------------------------------