Adapter always reads datetime in local timezone

[hrp]

I have a SQLServer 2008 database that has a datetime stored in UTC. However when I access this data (via a stored procedure) it is always picked up in the web server's timezone.

For instance:

"2010-10-30 11:30:00" => "2010-10-30 11:30:00 -0700"

The adapter seems to be assuming that the time is stored in the SQL DB in the same timezone as the web server. This has been confirmed by changing the server's timezone.

No setting in Rails seems to impact this. Also this seems to be counter Rails defaults which assume that times in the DB are stored in UTC.

[hrp]

Solved it: TinyTds::Client.default_query_options[:timezone] = :utc

Is there a way to access this setting via ActiveRecord?

[metaskills]

Yes, in application.rb there is a config.time_zone that controls all other setters, including ActiveRecord. It should already be there and commented out. Default should also be UTC too unless someone configured something differently deeper down.

[hrp]

I wasn't finding success with config.time_zone but I'm working with a fairly large app with custom gems and engines. Setting utc on TinyTds did the trick. Thanks!

For reference (and whomever may come here next), here is the line in TinyTds that was my problem: https://github.com/rails-sqlserver/tiny_tds/blob/master/lib/tiny_tds/client.rb#L34

@@default_query_options = {
  :as => :hash,
  :symbolize_keys => false,
  :cache_rows => true,
  :timezone => :local,
  :empty_sets => true
}

[metaskills]

I wasn't finding success with config.time_zone

Like I said, there are others that could have been set somewhere else. Specifically, ActiveRecord. A quick Google search shows so here.

The SQL Server adapter should do all that for you once those are set. For instance here and here if you are using RubyODBC. If you are using TinyTDS it does so here.

Your solution is a workaround at too low a level. If I were you, I would find out where in my code (or gem) a different value is being set for ActiveRecord::Base.default_timezone and fix it. If that fails, then specifically set config.active_record.default_timezone your self.

[hrp]

I completely agree that the workaround is too low-level. I no longer touch
that codebase however.