CODEWITHSUNDEEP
ruby-on-railsoracle-databasedatetimeactiverecord
Mike Woodhouse
Mike Woodhouse

Reputation: 52316

How to return a Date (not a TimeWithZone) from a Oracle date column in ActiveRecord?

Environment: Rails 2.2.2, Oracle 10g

Most of the columns declared "date" in my ActiveRecord models are exactly that: dates: they don't care about time at all.

So with a model declared thus:#

class MyDateOnlyModel < ActiveRecord::Migration
  def self.up
    create_table :my_date_only_model do |t|
      t.date :effective_date
      t.timestamps
    end
  end
end

writing a test like this:

test_date = Date.new(2008,12,05)
MyDateOnlyModel.create!(:effective_date => test_date)
assert_equal test_date, MyDateOnlyModel.find(:first).effective_date

should pass, shouldn't it? (Assuming I didn't mess anything up transcribing the above, of course)

But it doesn't - not quite. I get this:

<Fri, 05 Dec 2008> expected but was
<Fri, 05 Dec 2008 00:00:00 UTC +00:00>.

So I put a date into the database and got ... well what did I get?

puts MyDateOnlyModel.find(:first).eff_date.class

tells me I actually got a ActiveSupport::TimeWithZone. Which wasn't what I wanted at all.

Is there a simple way to tell ActiveRecord that some (not all) columns are Dates and only Dates?

UPDATE: more complaining...

Yes, I could use to_date:

assert_equal test_date, MyDateOnlyModel.find(:first).effective_date.to_date

works fine. But that's what I'm trying to avoid. I asked AR to make me a date, I want a date back.

And I could add a method to my class, effective_date_as_date - that works too. But surely it's not impossible to just get a date, dagnabbit.

PRE-ACCEPTANCE UPDATE

Eventually I realised why this was a particular problem with Oracle: there is no distinction between DATE and DATETIME, so ActiveRecord can't figure out unaided whether a time of zero means midnight (possibly with time zone corrections) or just the date. Bah. Stupid Oracle. So I'm going to have either to go down the plugin route, change my database (tempting, so very tempting) or continue with the to_date/to_time mess I have at present.

Upvotes: 0

Views: 3497

Answers (2)

user52636
user52636

Reputation:

I have discovered a bug: you can't compare TimWithZone with a Date. Comparison fails, ruby tells me.

Upvotes: 1

mwilliams
mwilliams

Reputation: 9978

Have you tried casting your attribute to a Date type?

The API describes to_date as follows:

Converts self to a Ruby Date object; time portion is discarded

test_date = Date.new(2008,12,05)
MyDateOnlyModel.create!(:effective_date => test_date)
assert_equal test_date, MyDateOnlyModel.find(:first).effective_date.to_date

Update:

Enhanced Oracle Adapter

It looks like this adapter might have a fix for your problem...

Set the option below and as a result columns with DATE in their name will be emulated as Date (and not as Time which is default for DATE columns in database)

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true

Upvotes: 1

Related Questions