This project is archived and is in readonly mode.

#2479 ✓wontfix

Inconsistant time zone handling

Reported by JackC | April 10th, 2009 @ 08:39 PM | in 3.0.2

I'm storing datetimes in MySQL in UTC. I want to retrieve them based on date.

Here's the problem. Time objects ignore the time zone in to_s(:db).

>> Time.local( 2009,1,1, 0,0,0 )
=> Thu Jan 01 00:00:00 -0600 2009
>> Time.local( 2009,1,1, 0,0,0 ).to_s(:db)
=> "2009-01-01 00:00:00"

TimeWithZone objects preserve the time zone in to_s(:db).

>> 2009,1,1, 0,0,0 )
=> Thu, 01 Jan 2009 00:00:00 CST -06:00
>> 2009,1,1, 0,0,0 ).to_s(:db)
=> "2009-01-01 06:00:00"

All the to_time helpers return Time objects which have their time zone thrown away when used in an ActiveRecord query. This leads to problems when using to_time


=> Fri Apr 10 00:00:00 -0500 2009
=> Fri, 10 Apr 2009 00:00:00 CDT -05:00
>> ==
=> true

>> Person.all :conditions => [ "created_at > ?", ]
Generated SQL => SELECT * FROM "people" WHERE (created_at > '2009-04-10 00:00:00')

Person.all :conditions => [ "created_at > ?", ]
Generated SQL => SELECT * FROM "people" WHERE (created_at > '2009-04-10 05:00:00')

It would seem to me that Time#to_s(:db) should convert the time zone and/or to_time should return a TimeWithZone.

Comments and changes to this ticket

  • Geoff Buesing

    Geoff Buesing April 11th, 2009 @ 02:27 PM

    • Milestone cleared.
    • Assigned user set to “Geoff Buesing”
    • State changed from “new” to “open”

    I agree this is inconsistent -- TimeWithZone and Time.utc instances report themselves to the database as UTC, whereas Time.local instances report themselves in system local time.

    Ideally, all of these objects would intelligently report themselves to the database either as UTC or system local time, depending upon the ActiveRecord::Base.default_timezone setting.

    to_time isn't the place to add this kind of logic, given that it's used in contexts other than database serialization -- #to_s(:db) would be a better choice.

    Another possibility -- maybe the cleanest choice -- would be to add logic to quoted_date to coerce the time object to utc or local before the #to_s(:db) call.

    Of course, this kind of change could break some apps, if the app, say, uses Time.local instances as container objects to send utc values to the db -- kind of wonky, but there's nothing stopping you from doing this now.

    Interested in hearing thoughts on this.

  • JackC

    JackC April 13th, 2009 @ 04:28 PM

    I added this line in an initializer and it seems to solve the problem.

    ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS[:db] = lambda { |time| time.utc.strftime( "%Y-%m-%d %H:%M:%S" ) }

    This gives Time and TimeWithZone the same behavior and the behavior exists in to_s(:db) for both of them.

  • Geoff Buesing

    Geoff Buesing April 14th, 2009 @ 12:32 AM

    That would work. Though you should use the non-destructive #getutc instead of #utc, otherwise you might get some unexpected behavior.

  • JackC

    JackC May 22nd, 2009 @ 02:43 PM

    I've been running this code with the change to #getutc for the last month in production without any issues. Any chance of getting this made the default?

  • Fernando Guillen

    Fernando Guillen July 31st, 2009 @ 12:02 PM

    The @JackC fix doesn't work for me.

    I had to define a new time_format:

    Time::DATE_FORMATS[:m] = "%Y-%m-%d %H:%M:%S"

    And use it on my views

  • JackC

    JackC July 31st, 2009 @ 02:07 PM

    I don't see how just a format string would would also change a local time to UTC time.

    Anyway this is the code I'm loading in an initializer and it is definitely sending UTC to the database.

    ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS[:db] = lambda { |time| time.getutc.strftime( "%Y-%m-%d %H:%M:%S" ) }
  • Geoff Buesing

    Geoff Buesing July 31st, 2009 @ 02:34 PM

    • State changed from “open” to “wontfix”

    We're going to go forward with a different approach: quoted_date will do the conversion, instead of TimwWithZone#to_s(:db), see

  • Jeremy Kemper

    Jeremy Kemper October 15th, 2010 @ 11:01 PM

    • Milestone set to 3.0.2
    • Importance changed from “” to “Medium”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket