ActiveRecord converts time to default zone on write; assumes UTC on read #6816

Closed
gisborne opened this Issue Jun 21, 2012 · 28 comments

Comments

Projects
None yet

3.1.3. I have:

def set_timezone
  Time.zone = "Pacific Time (US & Canada)"
end

My database (Postgres) has Timestamp without Time Zone fields.

Not sure where it's documented, but I find pervasive comments that Rails stores all dates in UTC (eg http://databasically.com/2010/10/22/what-time-is-it-or-handling-timezones-in-rails/).

However, this does not appear to be the case. ActiveRecord::ConnectionAdapters::Quoting:

 def quoted_date(value)
   if value.acts_like?(:time)
     zone_conversion_method = ActiveRecord::Base.default_timezone == :utc ? :getutc : :getlocal
     value.respond_to?(zone_conversion_method) ? value.send(zone_conversion_method) : value
   else
     value
   end.to_s(:db)
 end

Note :getlocal on line 3.

However, the code to translate date strings from the database does assume UTC:

ActiveRecord::AttributeMethods::TimeZoneConversion::ClassMethods::define_method_attribute:

     def define_method_attribute(attr_name)
       if create_time_zone_conversion_attribute?(attr_name, columns_hash[attr_name])
         method_body, line = <<-EOV, __LINE__ + 1
           def _#{attr_name}
             cached = @attributes_cache['#{attr_name}']
             return cached if cached
             time = _read_attribute('#{attr_name}')
             @attributes_cache['#{attr_name}'] = time.acts_like?(:time) ? time.in_time_zone : time
           end
           alias #{attr_name} _#{attr_name}
         EOV
         generated_attribute_methods.module_eval(method_body, __FILE__, line)
       else
         super
       end
     end

This is my diagnosis. The problem is that I've set a default time zone, but all my timestamps are off by 7 hours when I pull them from the database.

rafamvc commented Jun 21, 2012

+1

Member

steveklabnik commented Jun 22, 2012

Do you happen to know if this occurs on 3.2.x? 3.1 is security fixes only at this point.

Member

chancancode commented Jun 29, 2012

No repo on 3.2.6. I might be missing something thought.

$ rails new test_time_zone
$ cd test_time_zone
$ rails g model user
$ rake db:create && rake db:migrate
$ rails c
>> ActiveRecord::Base.default_timezone = :local
=> :local
>> Time.zone = "Pacific Time (US & Canada)"
=> "Pacific Time (US & Canada)"
>> Time.now
=> 2012-06-29 01:14:44 -0700
>> u = User.create
   (0.1ms)  begin transaction
  SQL (6.8ms)  INSERT INTO "users" ("created_at", "updated_at") VALUES (?, ?)  [["created_at", Fri, 29 Jun 2012 01:14:52 PDT -07:00], ["updated_at", Fri, 29 Jun 2012 01:14:52 PDT -07:00]]
   (1.3ms)  commit transaction
=> #<User id: 1, created_at: "2012-06-29 08:14:52", updated_at: "2012-06-29 08:14:52">
>> u.created_at
=> Fri, 29 Jun 2012 01:14:52 PDT -07:00
>> u.created_at = Time.now
=> 2012-06-29 01:15:49 -0700
>> u.save
   (0.1ms)  begin transaction
   (0.4ms)  UPDATE "users" SET "created_at" = '2012-06-29 01:15:49.164736', "updated_at" = '2012-06-29 01:15:51.762974' WHERE "users"."id" = 1
   (1.7ms)  commit transaction
=> true
>> reload!
Reloading...
=> true
>> u = User.first
  User Load (0.1ms)  SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 1, created_at: "2012-06-29 08:15:49", updated_at: "2012-06-29 08:15:51">
>> u.created_at
=> Fri, 29 Jun 2012 01:15:49 PDT -07:00
>> u.created_at = Time.utc(2012,12,31,23,59,59)
=> 2012-12-31 23:59:59 UTC
>> u.save
   (0.1ms)  begin transaction
   (0.4ms)  UPDATE "users" SET "created_at" = '2012-12-31 15:59:59.000000', "updated_at" = '2012-06-29 01:16:34.909236' WHERE "users"."id" = 1
   (1.7ms)  commit transaction
=> true
>> reload!
Reloading...
=> true
>> u = User.first
  User Load (0.2ms)  SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 1, created_at: "2012-12-31 23:59:59", updated_at: "2012-06-29 08:16:34">
>> u.created_at
=> Mon, 31 Dec 2012 15:59:59 PST -08:00
>> u.created_at.utc
=> 2012-12-31 23:59:59 UTC
>> 
Member

steveklabnik commented Jun 29, 2012

Did you also have this:

My database (Postgres) has Timestamp without Time Zone fields.

That might be the thing that actually causes the issue. looks like you were using sqlite in that example.

Member

chancancode commented Jun 29, 2012

My bad. Getting identical results from postgres though:

$ rails new test_time_zone -d postgresql
$ cd test_time_zone
$ rails g model user
$ vim config/database.yml
$ rake db:create && rake db:migrate
$ rails c
>> ActiveRecord::Base.default_timezone = :local
=> :local
>> Time.zone = "Pacific Time (US & Canada)"
=> "Pacific Time (US & Canada)"
>> Time.now
=> 2012-06-29 10:55:17 -0700
>> u = User.create
   (0.1ms)  BEGIN
  SQL (100.0ms)  INSERT INTO "users" ("created_at", "updated_at") VALUES ($1, $2) RETURNING "id"  [["created_at", Fri, 29 Jun 2012 10:55:27 PDT -07:00], ["updated_at", Fri, 29 Jun 2012 10:55:27 PDT -07:00]]
   (0.8ms)  COMMIT
=> #<User id: 1, created_at: "2012-06-29 17:55:27", updated_at: "2012-06-29 17:55:27">
>> u.created_at
=> Fri, 29 Jun 2012 10:55:27 PDT -07:00
>> u.created_at = Time.now
=> 2012-06-29 10:55:45 -0700
>> u.save
   (0.2ms)  BEGIN
   (0.6ms)  UPDATE "users" SET "created_at" = '2012-06-29 10:55:45.679852', "updated_at" = '2012-06-29 10:55:53.469210' WHERE "users"."id" = 1
   (0.9ms)  COMMIT
=> true
>> reload!
Reloading...
=> true
>> u = User.first
  User Load (0.3ms)  SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 1, created_at: "2012-06-29 17:55:45", updated_at: "2012-06-29 17:55:53">
>> u.created_at
=> Fri, 29 Jun 2012 10:55:45 PDT -07:00
>> u.created_at = Time.utc(2012,12,31,23,59,59)
=> 2012-12-31 23:59:59 UTC
>> u.save
   (0.3ms)  BEGIN
   (0.3ms)  UPDATE "users" SET "created_at" = '2012-12-31 15:59:59.000000', "updated_at" = '2012-06-29 10:56:27.964283' WHERE "users"."id" = 1
   (0.5ms)  COMMIT
=> true
>> reload!
Reloading...
=> true
>> u = User.first
  User Load (0.1ms)  SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 1, created_at: "2012-12-31 23:59:59", updated_at: "2012-06-29 17:56:27">
>> u.created_at
=> Mon, 31 Dec 2012 15:59:59 PST -08:00
>> u.created_at.utc
=> 2012-12-31 23:59:59 UTC
>> 
Member

steveklabnik commented Jun 29, 2012

Diving into the code, there's a small difference between {3.2,master} and 3.1:

9a10
>                   changed = read_attribute(:#{attr_name}) != time
10a12
>     #{attr_name}_will_change! if changed

@gisborne since we can't seem to reproduce this on a new app, is there any more context you might be able to share?

dgiunta commented Oct 3, 2012

I am having this issue as well currently. Postgres database + Rails 3.2.8.

The only thing I can add to this discussion is that I'm having trouble with time fields, but datetime / timestamp fields are coming out of the database and being converted to the current time zone as expected.

>> Time.zone
=> GMT-06:00 Central Time US  Canada
>> c = Course.last
  Course Load (0.2ms)  SELECT "courses".* FROM "courses" ORDER BY "courses"."id" DESC LIMIT 1
=> #<Course id: 2477, name: "alkjb", description: "", capacity: 10, price: #<BigDecimal:7febe500e690,'0.1E2',9(18)>, starts_at: "2000-01-01 16:00:00", ends_at: "2000-01-01 17:30:00", day_of_week: 1, session_id: 191, location_id: 21, created_at: "2012-10-03 02:00:47", updated_at: "2012-10-03 02:00:47", reg_code: "", costume_fee: nil>
>> c.created_at # normal timestamp
=> Tue, 02 Oct 2012 21:00:47 CDT -05:00
>> c.starts_at # a time field on the Course model
=> 2000-01-01 16:00:00 UTC

Thanks

Member

senny commented Oct 6, 2012

there is another issue #7301 which describes the situation for time fields.

I have a minimal Rails app reproducing this issue with a failing test.

https://github.com/deathbob/tz-troubles

rake test:units will demonstrate the failing test.

The only change I made after running rails new was to add the following lines to application.rb

    config.time_zone = 'Central Time (US & Canada)'
    config.active_record.default_timezone = 'Central Time (US & Canada)'

The datetime fields are wrong by my offset from UTC.
I believe this problem will be reproducible for anyone whose Time.now.zone != "UTC"

+1 - 3.2.8 app on Ruby 1.9.1, DB is in PST

Owner

pixeltrix commented Oct 14, 2012

This is a duplicate of #3145, closing in favour of that ticket.

@pixeltrix pixeltrix closed this Oct 14, 2012

This is not a duplicate of #3145, these are totally separate issues.
For one thing this doesn't involve 'time' columns at all.

The OP has the crux of the issue, that if you change ActiveRecord::Base.default_timezone to anything other than :utc , Rails will convert any Time / DateTime to the system local timezone before writing them to the database.
When pulling it back out it assumes the time was written in UTC, which means any times written to the db will be off by the offset of the users computer from UTC.

As above I have a minimal test that exhibits this behavior, which again is totally unrelated to the ticket #3145.

Do I need to open a new issue for this or can we reopen this issue?

Auch - missed this issue while googling the past 36hrs (UTC) :)

started out, documenting things here - then decided to throw it into this gist for your perusal pleasure :)

suffice to say: Rails 3.2.8 on Ruby 1.9.2 does what it says it does!

cheers

Owner

pixeltrix commented Nov 20, 2012

@deathbob sorry for the late reply - the accepted values for config.active_record.default_timezone are either :utc or :local. If you set it to something other than :utc it will assume :local so what you're seeing is the expected behavior.

I have put together a simple demo app to show this issue: https://github.com/Chipairon/fun_with_time_zones/blob/master/README.md

This illustrates a problem I am facing with a model that has a datetime field. Putting this field in a form as a textfield (so you can easily use a javascript datepicker), does not work as expected.

The textfield shows the value without offset -> When you submit the form and save, a new offset is applied to the wrong value of the textfield.

Parsing the value manually does the trick as I get rid of the time part, so no offset is applied...

In application_helper create a helper to format the date:

module ApplicationHelper
  def format_date_for_text_field(the_date)
     return the_date.strftime('%d-%m-%Y')
  end
end

Then in the form use the helper:

    <% if @fun.crazy_time and not @fun.crazy_time.blank? %>
        <%= f.text_field :crazy_time, :value => format_date_for_text_field(@fun.crazy_time) %>
    <% else %>
        <%= f.text_field :crazy_time %>
    <% end %>

MrHubble commented Aug 4, 2013

I'm unsure if this is an issue or if I'm just doing it wrong.

In application.rb I set:
config.time_zone = 'UTC'
config.active_record.default_timezone :utc

but when deployed to heroku my app inserts/writes in local. This is different to development which inserts/writes in utc.

Owner

pixeltrix commented Aug 5, 2013

@MrHubble how do you know it writes in local? Do you have any logs or are you just looking at the row data itself? I'm assuming that you're using PostgreSQL?

MrHubble commented Aug 5, 2013

Thanks for the reply. I am using PostgreSQL. I use psql to look at my table
on Heroku and the datetime is stored to my local time (I'm selecting a
start_time and end_time in my app).

Whereas, when in development, if I select the same start_time and end_time,
and then use psql to perform the same query on the table, the datetime is
stored in UTC.

It is also a problem when trying to retrieve the correct data from the
tables using:

Time.at(date_time.to_i).to_formatted_s(:db)

saturday commented Dec 5, 2013

@MrHubble Have you figured out what the issue is with this? I'm running into the same problem - ie) everything working fine on write and read locally; however on Heroku it writes as local and reads as UTC. You can take a look at my SO question for the code.

saturday commented Dec 6, 2013

Take a look at my previously mentioned SO question and answer. This might be the result of the way we're building a custom Time object.

adz commented Jun 10, 2014

While "time" attrs are not supposed to convert across timezones, I found a subtle cross-related bug in dirty tracking.

When assigning a time attribute a new time, you'll see a zone-converted "was" value but not for the new value. This commit appears to correct the problem (is rails 4.1.2?):
cb400b0

Leaving here for anyone digging into the issue...

unrau commented Jul 17, 2014

I have updated Rails in my app, and on Heroku, to 4.1.4, and I am still getting this exact same issue.

bvirlet commented Sep 15, 2014

Seeing the same issue here with activerecord (4.1.6). The time zone information is lost when writing to the database.

Same here. The strange thing is that Time.now and many other Time calls uses the system timezone.
It works by manually setting the system timezone on config/application.rb:

    config.time_zone = File.read('/etc/timezone').split("\n").first
    config.active_record.default_timezone = config.time_zone

Shouldn't rails do that automatically?

Owner

pixeltrix commented Oct 27, 2014

@brauliobo your code doesn't make sense - the valid values for default_timezone are :utc and :local. Also you might want an application to run in a particular timezone whereas your servers are configured for UTC.

@pixeltrix. Right, correcting. But why rails don't follow the system timezone by default?

 config.time_zone = File.read('/etc/timezone').split("\n").first
 config.active_record.default_timezone = :local

brauliobo added a commit to noosfero/noosfero that referenced this issue Oct 27, 2014

Set default timezone as needed by rails 3.2
Without this, rails won't convert back to system timezone when reading
timestamṕ/datatime fields from database.

rails/rails#6816

config.time_zone = 'Chennai'
config.active_record.default_timezone = :local
these two are not working .It still taking in development system time and in production it taking default server time

@agiratech-nagarani utc should be used instead for active record

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment