Rails, ActiveRecord, and time ranges

On my military reading list site I wanted to be able to find records created in a particular month. That let me do URLs like /revisions/navy/jan-2009 and then look up the proper revision - e.g., the 2009 revision of the Navy reading list.

So, here are the functions, the first of which I got from some place on the internet which now eludes my Googling:

def days_in_month(d)
  (Date.new(d.year,12,31) << (12-d.month)).day
end

def beginning_to_end_of_month(date)
  from = date - ((date.day-1).days)
  to = from + (days_in_month(from)-1).days + 1.day
  from..to
end

And here's how to use them to look up revisions created in Januaary 2009:

>> Revision.find(:all, 
 :conditions => 
  {:created_at => 
  beginning_to_end_of_month(Date.parse("jan 2009"))})
=> [#<Revision id: 1, reading_list_id: 3, 
 created_at: "2009-01-10 21:25:17", 
 updated_at: "2009-01-10 21:25:17">, 
 #<Revision id: 2, reading_list_id: 4, 
 created_at: "2009-01-14 21:49:56", 
 updated_at: "2009-01-14 21:49:56">]

The coolest thing about this is that conditions takes a Range object, so it's easy to look up things within a date range. It gets translated to a SQL BETWEEN clause... very handy!

Comments

[Josh N]

Nice, didnt know u could use a range as a condition on a date column. I was always doing ["created_at BETWEEN % AND %", start_date, end_date]

btw, ActiveSupport provides beginning_of_month() and end_of_month() functions.

def beginning_to_end_of_month(date)
(date.beginning_of_month)..(date.end_of_month)
end

[Tony]

With my past SQL programming, I've got in the habit of aways doing something like

WHERE datefield >= '2009-01-01' AND datefield < '2009-02-01'

I haven't tried your code, but in your example, I suspect you may miss items posted after midnight on '2009-01-31' (pretty much the whole day of January 31st).

Using '2009-01-31 24:59:59' usually doesn't work either because of floating-point rounding errors.

[tomcopeland]

@josh, oh, cool, didn't know about that, thanks!

@tony, here's the SQL that ActiveRecord generates:

SELECT *
FROM "revisions"
WHERE ("revisions"."created_at"
BETWEEN '2009-01-01' AND '2009-01-31')

I should try that to see if it misses any of those edge dates.

[Tony]

@Tom -- Yeah, the databases that I have used will interrupt that as:

BETWEEN '2009-01-01 00:00:00' AND '2009-01-31 00:00:00'

And since it looks like you are storing times as well, that's a problem.

The next thing people try is to append "23:59:59" to the end_date, but like I said above, because times are stored as floating-point numbers, you may still miss some records.

[tomcopeland]

@tony, ah, cool, thanks for the note. Just added "+ 1.day" onto there, seems like that should do the trick then.