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
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 S. 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
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 <
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]