Using Rails 2.1 timezone support with non-UTC database

rubyonrails-core
1

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

2

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.

3

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

4

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.

5

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.

6

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

7

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.

8

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&gt;, 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.

9

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&gt;, 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.

10

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

11

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&gt;\.

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.

12

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.

13

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

14

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

15

@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.

16

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

17

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.