Using Rails 2.1 timezone support with non-UTC database

So the Rails 2.1 timezone support is great, but AFAIK requires that
the DB be stored in UTC. From a FAQ:

Tips Upgrading Existing Apps

1. the new time zone features assume that the database is storing
times in UTC, so if you've currently storing times in the database in
a zone other than UTC, you'll need to migrate existing data to UTC

The problem is we use a shared Oracle database which has its timezone
set to US/Pacific, and we don't have authority or the ability to
change this.

I would be willing to put together a patch that does one of the
following:

1) Obeys the config.active_record.default_timezone setting and adjusts
based on that

2) Adds a new setting, say config.source_time_zone that is just used
by ActiveSupport::TimeWithZone. This could then leverage the same code
in TimeWithZone to perform the calculation.

Is there a preference? And any pointers/caveats about TimeWithZone
would be appreciated.

Thanks,
Nate

The problem is we use a shared Oracle database which has its timezone
set to US/Pacific, and we don't have authority or the ability to
change this.

My (limited) understanding is that this may not actually be possible.
There are times which can't be converted from Pacific back to GMT due
to Daylight savings. Specifically when you're 'leaping back' there
are two 2:34 AMs, and when you spring forward there are none. So
taking a time in that TZ back to gmt (or to any other zone) isn't
possible.

I'm not sure if geoff is around to confirm my understanding.

I would be willing to put together a patch that does one of the
following:

1) Obeys the config.active_record.default_timezone setting and adjusts
based on that

Assuming it's possible to do this, I think that this is a fine way to
have it work.

Sorry for not seeing this thread before -- the RSS feed from this
group seems to be a bit flaky (anyone else noticing this?)

> The problem is we use a shared Oracle database which has its timezone
> set to US/Pacific, and we don't have authority or the ability to
> change this.

My (limited) understanding is that this may not actually be possible.
There are times which can't be converted from Pacific back to GMT due
to Daylight savings. Specifically when you're 'leaping back' there
are two 2:34 AMs, and when you spring forward there are none. So
taking a time in that TZ back to gmt (or to any other zone) isn't
possible.

To clarify the DST conversion issue: the issue arises when you store
timestamps without offest or DST info -- e.g., a MySQL datetime field,
which stores timestamps in the "%Y-%m-%d %H:%M:%S" format.

In zones that observe DST, during the "fall back" period there's one
hour that repeats itself -- first in daylight savings time, and then
in standard time. The database, however, is not given the information
to disambiguate between the two:

t1 = Time.local(2008,11,2,0,34) + 1.hour

=> Sun Nov 02 01:34:00 -0500 2008

t2 = Time.local(2008,11,2,0,34) + 2.hours

=> Sun Nov 02 01:34:00 -0600 2008

t1.to_s(:db)

=> "2008-11-02 01:34:00"

t2.to_s(:db)

=> "2008-11-02 01:34:00"

The upshot is, if you're storing local times from a DST-observing zone
in a standard datetime column, you can't store the full range of time
-- there would be one hour per year that you couldn't represent.

> I would be willing to put together a patch that does one of the
> following:

> 1) Obeys the config.active_record.default_timezone setting and adjusts
> based on that

Assuming it's possible to do this, I think that this is a fine way to
have it work.

Actually, we're very close to having this working -- we'd just need to
tweak TimeWithZone#to_s(:db) to report the time relative to the system
local zone if config.active_record.default_timezone == :local.
Granted, the datetime column caveat I explained above would be an
issue with this setup, but this has always been a limitation of
config.active_record.default_timezone == :local.

Nate, if you still want to pull together a patch and need a bit more
guidance, feel free to email me.

Geoff

I have a related problem, using a secondary database connection, set
up using
self.abstract_class = true
establish_connection YAML.load_file("config/database.yml")[e =
'secondary_db_'+ENV['RAILS_ENV']]
and using the sqlserver adapter.
I read records from the 2ary DB, and the datetimes are in localtime,
and when they get saved to the main DB, get converted to UTC.

I can't see where to tell the 2ary connection that this DB is also in
UTC
so there should be no conversion... any thoughts?

Also there needs to be a way to override the default typecasting for
sqlserver datetime fields; some columns are just dates some just
times but there seems to be no hook to specify that.

Clifford Heath.

I have a related problem, using a secondary database connection, set
up using
self.abstract_class = true
establish_connection YAML.load_file("config/database.yml")[e =
'secondary_db_'+ENV['RAILS_ENV']]
and using the sqlserver adapter.
I read records from the 2ary DB, and the datetimes are in localtime,
and when they get saved to the main DB, get converted to UTC.

I can't see where to tell the 2ary connection that this DB is also in
UTC
so there should be no conversion... any thoughts?

I can't see how the current code would break this case, however you
could probably create a test case in the activerecord unit tests.
They connect to two databases and you could try roundtripping those
datetimes.

Also there needs to be a way to override the default typecasting for
sqlserver datetime fields; some columns are just dates some just
times but there seems to be no hook to specify that.

I believe that the sqlserver adapter has (had?) custom logic for this
kind of conversion. If the time is 0, return a date, etc.

> I have a related problem, using a secondary database connection, set
> up using self.abstract_class = true
> I read records from the 2ary DB, and the datetimes are in localtime,
> and when they get saved to the main DB, get converted to UTC.
I can't see how the current code would break this case,

I posted before enough exploration. The problem is that the sqlserver
adapter doesn't use Base.default_timezone so is incompatible with
Rails 2.1 - any date read from a record, assigned to another and saved
will have the timezone delta subtracted. It's not a 2-database
problem,
it's just an sqlserver adapter problem... The problem is in its
cast_to_datetime() method. Patch forthcoming :-).

I believe that the sqlserver adapter has (had?) custom logic for this
kind of conversion. If the time is 0, return a date, etc.

Yep, it's kinda arbitrary, limited and generally crappy. I'll see if I
can
come up with a better suggestion.

it's just an sqlserver adapter problem... The problem is in its
cast_to_datetime() method. Patch forthcoming :-).
Yep, it's kinda arbitrary, limited and generally crappy. I'll see if I
can
come up with a better suggestion.

Excellent. I've lost track of who the current maintainer is, but
hopefully they'll speak up here? If no one speaks up, you could
always create a project on github.

Tom Ward is the author nominated in the gem, but I don't know
where the project source lives. The gem also gives the homepage
as <http://wiki.rubyonrails.org/rails/pages/SQL+Server>, but that
page hasn't been updated in 18 months. I've emailed Tom.

A github project is the right idea, but someone has to maintain
the gem.

Tom Ward is the author nominated in the gem, but I don't know
where the project source lives. The gem also gives the homepage
as <http://wiki.rubyonrails.org/rails/pages/SQL+Server>, but that
page hasn't been updated in 18 months. I've emailed Tom.

I'm not sure tom even uses SQL Server anymore, but hopefully he can
point you in the right direction. If he can't, you may have just
volunteered :slight_smile:

A github project is the right idea, but someone has to maintain
the gem.

Github has their neat little gem server, but once we have an active
maintainer, we can give them permission to upload new versions to the
relevant rubyforge project.

Tom uploaded the most recent sqlserver adapter to his github account,
and I've been doing some work on my own fork of that
(http://github.com/h-lame/rails-sqlserver-adapter/), although work's
been hectic so there's been nothing for a month or so. There was also
some activity on the rails-sqlserver-adapter mailing list, so I'd jump
in there if I was you.

In our own project I've recently come across this same date problem
and I took the hacky way out of defining a has_dates for AR::Base that
made sure to a) coerce values from the DB into Date objects and b)
turn off time-zone awareness for those fields. It's probably not the
right thing to do (it's more work to specify has_dates) but it does
achieve the correct result. You're welcome to it if you want.

I know that in the past Tom mooted having migrations that specified
dates adding a known constraint to the column so that the adapter
could look for it and then know that columns with that constraint are
to be treated as Dates not full Date + Time objects. I'm not sure if
he ever implemented it though.

Anyway, if you (or anyone else) has any time + love for the sql server
adapter it would be awesome if you could join the
rails-sqlserver-adapter mailing list
(http://groups.google.com/group/rails-sqlserver-adapter) and we'll
thrash it out there.

Cheers,

Muz

Murray,

Tom uploaded the most recent sqlserver adapter to his github account,
and I've been doing some work on my own fork of that

Tom answered my email saying that neoryan's github fork had been
receiving the most work recently:
<http://github.com/neoryan/rails-sqlserver-adapter>.

I pulled jranafie's fork and merged my own before I was aware of the
other versions. I'll look at merging the others, but I'm not really
sure
how we should decide which patches go into an official version. Also
I'm a bit of a newb with git still.

In our own project I've recently come across this same date problem
and I took the hacky way out

I think I have code that matches the abstract adapter, FWIW.

Anyway, if you (or anyone else) has any time + love for the sql server

Well, I only work with sqlserver sporadically, but have managed
significant schemas with it in the past so have some experience.
I'm not sure I'm ready to be a maintainer, but I can certainly
contribute.

it would be awesome if you could join the rails-sqlserver-adapter mailing list

Done, I'll post there, probably in the morning, I'm too tired now.
This is CC'd there.

Clifford Heath.

Folk,

http://github.com/cjheath/rails-sqlserver-adapter/ contains my
merge of all six forks of tomafro's original repo. I haven't yet
merged in jrafanie's independent work because I think some of
it won't work on SQLSVR 2K, only on 2K5.

Not thoroughly tested yet (though it's running our app ok), but
have a play with it and see how you go. Comments, patches,
and success reports are welcome.

Clifford Heath, Data Constellation.
Agile Information Management and Design.

Actually, we're very close to having this working -- we'd just need to
tweak TimeWithZone#to_s(:db) to report the time relative to the system
local zone if config.active_record.default_timezone == :local.
Granted, the datetime column caveat I explained above would be an
issue with this setup, but this has always been a limitation of
config.active_record.default_timezone == :local.

Nate, if you still want to pull together a patch and need a bit more
guidance, feel free to email me.

Sorry for letting this thread linger - other fish to fry.

We're still quite interested in this functionality. Just to confirm,
nobody else has patched this in, correct? (I'm not on Edge for my
current project)

Assuming not, I can still take the initiative on a patch; however,
given the above info, it may end up in a wildly different place than I
imagined. Can Geoff or somebody give me a pointer on where they think
these changes should go?

Thanks alot for all the hard work.

-Nate

Unless I am missing something here...

Wouldn't the time you store in the database be the timezone you set in
the config file?

If I set config.active_record_default_timezone == :local, then all the
records in the database should be saved in my local time.

Makes little difference with a timestamp with timezone, but all the
difference in the world on a record that is timestamp without,
especially when you are having to play good database citizen and share
the DB with other applications in other time zones that already save
everything in LA USA time because it is a 15 year old application /
convention :slight_smile:

anyway, the setting in the config file should over ride any default
value that the adapter thinks IMHO. This would be expected behaviour.
Not convert on the fly and _always_ store UTC in the DB regardless of
any config setting. This would cause serious problems for legacy DB
people I think.

Mikel

@Mikel -- if you *don't* set config.time_zone,
ActiveRecord.default_timezone will work as expected -- you'll send
values to the db in system local time, and times returned from the db
will be returned as Time.local instances. When you do set
config.time_zone, it sets ActiveRecord.default_timezone to :utc behind
the scenes; but if you explicitly set AR.default_timezone to :local in
combination with config.time_zone, things won't work correctly. This
is the combination we're looking to fix, so that legacy apps can take
advantage of the new time zone features.

@Nate -- thanks for following up on this -- correct, this
functionality has yet to be added. I'm now thinking the way to go with
this is, instead of putting logic into TimeWithZone#to_s(:db), we
should modify
ActiveRecord::AttributeMethods.define_write_method_for_time_zone_conversion
so that, if AR.default_timezone is :local, it coerces the time object
with #getlocal instead of #in_time_zone. With this setup,
default_timezone will be respected on a per-model basis, as opposed to
just a global setting.

Let me know if you need help pulling this together, or have different
ideas on how this could be implemented.

Thanks for the pointer, sounds like a good way to go. Looks like a 3-
line patch in one place. Am I missing anything?

I don't think so. We just need to confirm (via tests) that AR
time_zone_aware attributes are sending times to the db relative to the
system local zone, and returning them relative to config.time_zone.
Everything else should work as expected.