Rails 2.3 and TimeZone

rubyonrails-talk
1

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

2

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:

3

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 chris@johnsonch.com

-Chris

4

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.