datetimeoffset not preserving offset

[devmitchell]

I'm in the process of transitioning a column to use the datetimeoffset datatype. However, the datatype keeps getting set to UTC, having its offset ignored. Here are a couple of interesting observations that may or may not help with this:

1. The schema.rb file never shows the right datatype.

   The migration file specifies the new type, and the migrations succeed.

   # Migration file
   t.datetimeoffset :special_datetime

   Checking the database, the type is as it should be: datetimeoffset(7). But the schema.rb file never reflects this.

   # schema.rb
   t.datetime "special_datetime", precision: 7

2. The model types reference datetime.

   When just checking the model in a rails console, it shows the field as datetime:

   TestModel(id: integer, special_datetime: datetime, created_at: datetime, updated_at: datetime) 

3. Tiny TDS works.

   2.3.1 :005 > client = TinyTds::Client.new host: '192.168.63.155', username: 'user123', password: 'somethingspecial', database: 'project_development'
    => #<TinyTds::Client:0x007fa681d942f0 @query_options={:as=>:hash, :symbolize_keys=>false, :cache_rows=>true, :timezone=>:local, :empty_sets=>true}> 
   2.3.1 :006 > result = client.execute('update test_models set special_datetime = "2017-02-06 23:08:18 +06:00" where 1 = 1')
    => #<TinyTds::Result:0x007fa681d75bc0 @query_options={:as=>:hash, :symbolize_keys=>false, :cache_rows=>true, :timezone=>:local, :empty_sets=>true}> 

   I can find that this succeeded as it should when I check out the database.

4. The SQL getting generated never mentions the offset.

   When I check the SQL being generated in a workflow, the data being referenced to ultimately be part of the SQL has no offset: ["special_datetime", "02-06-2017 23:46:18.0"]

Under the Hood

• activerecord (5.0.0.1)
• activerecord-sqlserver-adapter (5.0.3)
• tiny_tds (1.1.0)

[metaskills]

Great issue report! Thanks for all the details. There is an issue and it came about during this commit 22ed615. I created #559 to address these. Can you test out that branch please?

[devmitchell]

Happy to help!

It's better, but not quite there. Everything seems to say that this attribute is now datetimeoffset, which is great. It even will keep from changing the timezone automatically on assignment. But something is happening when I actually save the object:

2.3.1 :001 > tm = TestModel.last
 => #<TestModel id: 13, special_datetime: "2017-02-08 00:27:06", created_at: "2017-02-07 14:12:01", updated_at: "2017-02-07 14:12:07"> 
2.3.1 :002 > tm.special_datetime
 => 2017-02-07 13:59:13 +0000 
2.3.1 :003 > tm.special_datetime = Time.now
 => 2017-02-07 08:13:13 -0600 
2.3.1 :004 > tm.special_datetime
 => 2017-02-07 08:13:13 -0600 
2.3.1 :005 > tm.save
 => true 
2.3.1 :006 > tm.special_datetime
 => 2017-02-07 14:13:13 UTC 
2.3.1 :007 > exit

Prior to the current change, the timezone would get casted on assignment.

Just to check, I also tried using the skip_time_zone_conversion_for_attributes method, to see if that hook is appropriate, but the behavior was the same.

http://api.rubyonrails.org/classes/ActiveRecord/Timestamp.html

[devmitchell]

One more piece of data. I used a bunch of callbacks to see if I could help pinpoint where in the lifecycle this is happening...

Before validation: 2017-02-07 08:29:42 -0600
After validation: 2017-02-07 08:29:42 -0600
Before save: 2017-02-07 08:29:42 -0600
Before update: 2017-02-07 08:29:42 -0600
After update: 2017-02-07 08:29:42 -0600
After save: 2017-02-07 08:29:42 -0600
Before commit: 2017-02-07 14:29:42 UTC
After commit: 2017-02-07 14:29:42 UTC

[metaskills]

I've merged in #559 and that will be in the v5.0.4 release. However, I can not reproduce your issue. I added new tests to the core datetimeoffset tests and everything is green.

Perhaps you should use Time.current vs Time.now which would give you an ActiveSupport::TimeWithZone object?