ActiveRecord: issue with 'find' method when querying dates.

Question

I'm struggling with the conditions for date querying in the 'find' method on my ActiveRecord models running over an SQLite3 database. My model is as follows:

Day:
  failure_day:   date
  failure_count: integer 

When I attempt to query Days, I get the following (pseudo code only):

Query:   Days.all
Result:  [{failure_day: 2010-04-14, failure_count: 1}]

Query:   Days.find(:first, :conditions=>'failure_day > 2010-02-01')
Result:  {failure_day: 2010-04-14, failure_count: 1}

Query:   Days.find(:first, :conditions=>'failure_day = 2010-04-14')
Result:  nil

Query:   Days.find(:first, :conditions=>'failure_day < 2010-05-05')
Result:  nil

What I can't understand is why the last two queries return 'nil'. The first query (Days.all) proves that I have a record in my database. The second one correctly matches 'failure_day' against a date that is less than 'failure_day', but when trying equal or less than it doesn't work.

Any ideas?

Answer 1

you should use

Days.find(:first, :conditions=>["DATE_FORMAT(failure_day, '%Y-%m-%d') > '2010-02-01'"])

Days.find(:first, :conditions=>["DATE_FORMAT(failure_day, '%Y-%m-%d') = '2010-04-14'"])

Days.find(:first, :conditions=>["DATE_FORMAT(failure_day, '%Y-%m-%d') < '2010-05-05'"])

Answer 2

You only provided pseudo-code, which makes it harder.

You're telling us that your failure_date column is of type DATE. Just let ActiveRecord handle the details for you:

Day.all(:conditions => {:failure_date => Date.today})
Day.all(:conditions => ["failure_date < ?", Date.today])
Day.all(:conditions => {:failure_date => Date.new(1900, 1, 1) .. 5.days.ago})

Answer 3

This is an answer to the comments by François Beausoleil: "What version of ActiveRecord, SQLite, Ruby are you using? Post a gist of a session where we can see the code you enter at the console and what's in development.log"

I'm putting it here because I can't get formatting in a comment and this post will be pretty much unreadable without formatting!

SQLite version:       3.6.12
ActiveRecord version: 2.3.5

Test code:

require 'test_helper'

class StatTest < ActiveSupport::TestCase
  test "Tmp" do
    # Prints: [#<Day id: 980190962, failure_day: "2010-04-14" ... >]
    p Day.all

    # Prints: []
    p Day.all(:conditions=>["failure_day < ?", '2010-05-14'])

    # Prints: []
    p Day.all(:conditions=>["failure_day < ?", Date.new(2010,5,4)])
  end
end

Test.log

Day Load (0.3ms)   SELECT * FROM "days" 
Day Load (0.1ms)   SELECT * FROM "days" WHERE (failure_day < '2010-05-14') 
Day Load (0.1ms)   SELECT * FROM "days" WHERE (failure_day < '2010-05-04') 

Running any of those commands directly from SQLite returns the expected rows, so the issue isn't with the SQLite command that ActiveRecord is generating. Bizarre.

Answer 4

Thanks to the probing questions of François Beausoleil I was able to figure out what was wrong. François reminded me that tests load data from the test fixtures (in my case test/fixtures/days.yml). I had already set this up but thought I'd better double check the format. In the days.yml file I discovered:

one:
  failure_day:   2010/04/14
  failure_count: 1

Noticing that the date wasn't defined as 2010-04-14 as in the tests so I changed it to:

one:
  failure_day:   2010-04-14
  failure_count: 1

All queries now work!