I encountered this problem today with Rails 2.3 and SQL Server. It turns out the problem is a bug in the activerecord-sqlserver-adapter gem; it uses Time.mktime to parse the datetime entries coming from the database, but Time.mktime is affected the the TZ environment variable of the Rails application. Unless ENV['TZ'] is set to "GMT" or "UTC" the local timezone will be applied to the UTC time value being read from the database. I monkey patched the gem by modifying lib/ active_record/connection_adapters/sqlserver_adapter.rb to change all invocations of Time.mktime to Time.gm instead, which does the same thing except ignores the current timezone and creates the Time object in UTC. I've submitted a patch to the people that I think might be maintaining the gem, so hopefully this will be a non-issue in future versions of activerecord-sqlserver-adapter.
If anyone has a better solution to suggest, then please do let me know. I do have a warning to anyone using a non-standard config.active_record.default_timezone value as a workaround: setting this value causes Rails to store your time data in non-UTC values, which is bad. It is an even worse solution if your timezone observes daylight savings because then you cannot even recover the correct time from your database for 1 hour each year. -- Michael H Buselli <[email protected]> On Apr 6, 8:48 am, Chris Johnson <[email protected]> wrote: > We also had issues with this. One thing that seemed to work (and might not > in your situation) was to convert to localtime then convert back to UTC, > don't ask me why. We had the issue when posting a UTC time as a hidden > field to (because the localtime was shown to the user) and then needed to > compare with the database time to get it to work. > > date_time = > (params[:audit][:date_time].to_time).localtime.strftime('%Y-%m-%d > %H:%M:%S').to_time > > @date_time = date_time.utc.strftime('%Y-%m-%d %H:%M:%S') > > if you have more questions hit me up on gTalk [email protected] > > -Chris > > On Mon, Apr 6, 2009 at 7:03 AM, Paul Mcconnon < > > > > [email protected]> wrote: > > > Hi there, > > > has anyone come up with a way around this problem? > > > It seems to be related to SQL server only. I only seem to have the > > problem since upgrading from a Rails 2.0 app to 2.3 > > > I have set both > > > config.time_zone = 'UTC' > > config.active_record.default_timezone = 'UTC' > > > I have tried changing > > > My dates are being adjusted (having an hour removed) when written to the > > database (as I am in Uk daylight savings time) but they don't get > > readjusted on the way back (As I suppose there are no offsets stored in > > SQL server datetime columns) > > > Is there any way to switch time-zone support off altogether and stop any > > adjustment happening at all? > > > Regards, > > > Paul > > > Rick Schumeyer wrote: > > > My rails 2.3 app is interpreting times retrieved from the database as > > > local (in my case, Eastern) instead of UTC. I created a test table in > > > a SQL Server database with one row. The table is called events and > > > contains an "edate" column. The value is "2009-03-30 12:00", and is a > > > UTC time. > > > > environment.rb contains > > > config.time_zone = 'UTC' > > > > The output from script/console is below. (results are identical to > > > using a browser) > > > > It looks like rails is assuming the database value is really a local > > > time, and it adds four hours to get UTC. But that is wrong, since the > > > time is already UTC. > > > > What else do I need to do to convince rails to leave the time value > > > alone, since it *is* a UTC value? > > > > I can get the correct value by extracting the "Time" with no zone, and > > > then recreating a new TimeWithZone. But that can't be the best way! > > > > Loading development environment (Rails 2.3.2) > > >>> Time.zone > > > => #<ActiveSupport::TimeZone:0xb795cbc8 @tzinfo=nil, @utc_offset=0, > > > @name="UTC"> > > >>> event=Event.find(1) > > > => #<Event id: 1, edate: "2009-03-30 12:00:00"> > > >>> event.edate > > > => Mon, 30 Mar 2009 16:00:00 UTC +00:00 > > >>> zutc= ActiveSupport::TimeZone.new('UTC') > > > => #<ActiveSupport::TimeZone:0xb795cbc8 > > > @tzinfo=#<TZInfo::DataTimezone: Etc/UTC>, @utc_offset=0, @name="UTC"> > > >>> correct_time = > > ActiveSupport::TimeWithZone.new(event.edate.localtime,zutc) > > > => Mon, 30 Mar 2009 12:00:00 UTC +00:00 > > > -- > > Posted viahttp://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
