Monday, May 4, 2009

More on Saving Dates to Oracle

I did some digging into rails after my last post (but before I saw the comment from Raimonds, so I still need to check that out -- thanks Raimonds!), and found that the setter method generated by rails is doing the timezone adjustment, turning the Date into a TimeWithZone (see the define_write_method_for_time_zone_conversion method in active_record/attribute_methods.rb).

However, in the process of determining which setter method to use, it looks at the skip_time_zone_conversion_for_attributes list, which controls whether to do the timezone conversion or just generate the standard setter method for the attribute. You can just add a line to your active record class, like this:

skip_time_zone_conversion_for_attributes << :my_date_attribute_name

With that line added, the date remains a date, and everything in the persistence layer is happy.

As I mentioned, I haven't tried it yet, but the new oracle enhanced adapter looks like the way to go. I thought I'd post this anyway, in case it's of use to anyone else -- there doesn't seem to be much written about "skip time zone conversion", and it might be useful in other situations...

Friday, May 1, 2009

Saving Dates to Oracle with JRuby / Rails

When you create date or datetime attributes in rails, both types get mapped to date columns in Oracle. The problem I was running into was trying to save date values (without time values).

Date attributes that were set in a view worked fine, but those that were set programmatically were getting saved with the time -- the time being offset from midnight based upon my timezone. I messed with the timezone setting ("config.timezone =" in environment.rb), but everything seemed to be working fine except this. It works if you set the attribute to a string value (i.e., '2009-05-01'), like the view / controller code does, but that's a pain.

So, digging through the activerecord extensions in jdbc_oracle.rb, I found that it tries to guess the type based upon whether there are values for hours, minutes, or seconds in the ActiveSupport::TimeWithZone objects that get sent through. The ones coming from the view had no hours, minutes, nor seconds, but the ones that had been set in the code (originally set to Date.today, for example) were getting converted into TimeWithZone objects that were midnight on the specified date, then adjusted for the timezone, turning the date part into the prior date. (I didn't dig through the rails code to find out where/how/why that conversion is taking place, but that might yield a better solution -- if any body knows, please share!) Since rails uses TimeWithZone objects, that seemed like a reasonable place to look.

Looking at the doc for TimeWithZone, it says that you shouldn't ever create instances directly, you should use TimeZone methods instead, via the Time.zone instance. The TimeZone methods include "today", "local", "parse" etc -- nice! So I tried each of those:
  • a = Time.zone.today
  • b = Time.zone.local(2009, 5, 1)
  • c = Time.zone.parse('2009-05-01')
Well, "b" and "c" worked -- they got saved to the database as dates -- but "a" acted the same as Date.today. It turns out that's exactly what Time.zone.today returns: a Date instance!?! I'm not sure why it works that way, but...

So, until I have time to dig through rails to see if there's a way to avoid the time zone conversion (besides setting the attribute to a string), I'll start using the TimeZone methods, with this little patch stuck in the config/initializers/ directory:

class ActiveSupport::TimeZone
def today
today = Date.today
self.local(today.year, today.month, today.day)
end
end

Again, if anyone has another suggestion, please share. Also, I'm not sure if this is JRuby-specific, but I don't have time to test that right now either...

(Note: running rails 2.2.2 w/ jruby 1.1.5 and activerecord-jdbc-adapter 0.9)