Rails 3 DateTime Comparison w/ Date In An ActiveRecord Query

Question

I'm trying to search a model for any dates equal to a specific date while omitting the timestamp. In Rails I could simply execute this as DateTime.to_date == somedate, however I don't think it's quite as easy to formulate in SQL where I wouldn't be able to apply the to_date method to an entire column like created_at:

Foo.where("created_at == some_day_without_time_stamp").count

Initially I thought that because I was using a postgresql database I could simply use psql syntax, but I much much rather leave it to ActiveRecord to decide what sql is most applicable and keep my code agnostic of database vendors. Is this possible without any additional plugins or gems?

Solution 1

Try created_at >= some_day_with_00:00:00 timestamp and create_at < some_day_plus_one_with_00:00:00 timestamp

Solution 2

I would do something like...

someday = Date.today
Foo.where( :created_at => (someday)..(someday + 1.day) )

This would capture all created_at dates between midnight on someday and someday + 1. This is inclusive (so it would include a Foo created bang on midnight on the +1 day) but may be 'just good enough' for your needs without messing with timestamps.

For niceness, I would wrap it up as a scope

scope :on_day, ( lambda do |someday|
  where( :created_at => (someday)..(someday + 1.day) )
end )

So

Foo.on_day( Date.yesterday ).count

is nicely readable.

Solution 3

DateTime class have two usefull methods to do this: beginning_of_day and end_of_day.

For this case, in which you have a Date object, you might do:

Foo.where('created_at >= #{Date.today.to_time.beginning_of_day} AND 
           created_at <= #{Date.today.to_time.end_of_day}')

Notice that you have to transform the Date object to a DateTime object

Comments

[Kasper Grubbe]

You have an off by one, which could lead to you counting a day twice. This was my fix: where( :created_at => (someday)..(someday + 23.hours + 59.minutes + 59.seconds) )