Rails & Sqlite question - comparing dates to db timestamps

I'm trying to do this: map the total sales on a day to an array of dates for highcharts (yes my project is effectively exactly the same as the railscast example).

I'm unfortunately just ending up with a lot of 0s; I believe the piece in my model:

def self.total_revenue_on(date)
  where("date(created_at) = ?", date).sum(:amt)
end

is failing to match the date to the datetime written in my database, e.g. "2011-07-21 09:22:28.388944+0000". Pretty sure that's where it's failing because if I remove the timezone piece manually from my database (get rid of "+0000" and leave just "2011-07-21 09:22:28.388944") it works just fine.

I think this is really a rails/sqlite question: am I storing the timestamp improperly, or compari开发者_StackOverflow中文版ng improperly? Any help is greatly appreciated!

The best practice is to use to_s(:db) for referencing datetimes in a database in Rails. Try:

 def self.total_revenue_on(date)
   where("date(created_at) = ?", date.to_s(:db)).sum(:amt)
 end

OK, I managed to solve this by using a different lookup method:

def self.total_revenue_on(date)
   where("datetime >= ? and datetime < ?", date, date + 1.day).sum(:amt)
end

Still completely perplexed by the problem with the original, but this seems to be working.