Checking for existing row using a DateTime column fails

I'm trying to find a row using a DateTime value. If the row exists, return it; otherwise create a new row.

The problem I'm seeing is that the find sql uses a different datetime value to the insert. As there is a unique index on my datetime column I get a duplicate not allowed db error at the 2nd call.

It seems to be related to the summer time saving 1 hour difference but then I'm stumped. I've got my timezone set to 'London' in environment.rb, and the mysql db is storing all dates as utc.

Running the 'doit' method twice with the same parameter results in 2 rows in the database - it should only ever create one.

I've put together a test class to demonstrate what I'm trying to do. And also the development log so that you can see the sql being generated.

I'm sure I've missed some basic fact regarding the conversion of datetime to utc before / at hitting the database but I've researched and I can't pin down what I've done wrong.

Rails: 2.3.10 mysql: 5.1.49

Thanks

class TestDate < ActiveRecord::Base

  # create table test_dates (thedate datetime not null);

  def self.doit(for_date)
    r = find_by_thedate(for_date)
    if r.nil?
      r = TestDate.new(:thedate => for_date)
      r.save!
    end
    r
  end

end

>> TestDate.doit(DateTime.now.midnight)
=> #<TestDate thedate: "2011-10-02 23:00:00">

  TestDate Load (0.3ms)   SELECT * FROM `test_dates` WHERE (`test_dates`.`thedate` = '2011-10-03 00:00:00') LIMIT 1
  SQL (0.1ms)   BEGIN
  TestDa开发者_如何学Cte Create (0.2ms)   INSERT INTO `test_dates` (`thedate`) VALUES('2011-10-02 23:00:00')
  SQL (0.1ms)   COMMIT

You could try the following:

r.save(:validate => false)

Because r.save! will run your validations, I think it rolls back your query.