date comparisons in Rails

Question

Hi there, I'm having trouble with a date comparison in a named scope. I'm trying to determine if an event is current based on its start and end date. Here's the named scope I'm using which kind of works, though not for events that have the same start and end date.

named_scope :date_current, :conditions => ["Date(start_date) <= ? AND Date(end_date) >=  ?", Time.now, Time.now]

This returns the following record, though it should return two records, not one...

>> Event.date_current
=> [#<Event id: 2161, start_date: "2010-02-15 00:00:00", end_date: "2010-02-21 00:00:00", ...]

What it's not returning is this as well

>> Event.find(:last)
=> #<Event id: 2671, start_date: "2010-02-16 00:00:00", end_date: "2010-02-16 00:00:00", ...>

The server time seems to be in UTC and I presume that the entries are being stored in the DB in UTC. Any ideas as to what I'm doing wrong or what to try?

Thanks!

Answer 1

Easiest way to debug is to look in your rails log to see exactly what SQL statement is being generated by Rails.

Then post the sql if still having problems.

In the meantime, my guess is that something is not set to UTC. Where something is the set {operating system, rails environment, dbms}

Added: Also, why are you comparing "Date" (in the dbms) with "Time" values (from your statement)? Better to have the type classes match explicitly. I use the standard of a new day has time component 00:00:00. That way you can compare with the db without needing the date function in your SQL.

Answer 2

I struggled with the same issue. The records are stored in UTC, but the utc value is not inserted into the query. I solved it, like you, by converting it to utc manually, like this time.utc.

def find_production_since(start_time)
    find(:all, :conditions => ["time > ?", start_time.utc])
end

Answer 3

Rather than using Time.now (and then laboriously converting to a date as per your second comment) you could just use Date.today, which will insert '2010-04-20' into your SQL, and should compare against Date(start_date) without worrying about times.