Finding annual dates with Rails 3.1 ActiveRecord Queries

Question

I am building a Rails 3.1 project in which one of my models contains a birthday attribute. I have been trying to find a query that essentially finds all users with a birthday within a certain range. The problem is that this part of the application doesn't really care about the year of birth, just the month and day. Unfortunately, Google has not been of much help on this one.

Does anyone know how to query for all records whose birthday attribute "has an anniversary" within a certain date range? I hope that makes sense!

Answer 1

Sounds like you're looking for EXTRACT:

M.where(%q{
    array[extract(month from d)::int, extract(day from d)::int] between array[?, ?] and array[?, ?]
}, lower_month, lower_day, upper_month, upper_day)

where M is your model, bday is the birthday column, and the upper and lower variables hold the month and day boundaries. You could also use INTERSECT:

M.find_by_sql([%q{
    select * from ms where array[? ,?] <= array[extract(month from bday)::int, extract(day from bday)::int]
    intersect
    select * from ms where array[?, ?] >= array[extract(month from bday)::int, extract(day from bday)::int]
}, lower_month, lower_day, upper_month, upper_day])

Yet another version would use to_char:

M.where(
    "to_char(bday, 'MM') || to_char(bday, 'DD') between ? and ?",
    '%2.2d%2.2d' % [lower_month, lower_day],
    '%2.2d%2.2d' % [upper_month, upper_day]
)

You could get the same results with a big ugly pile of CASE statements too (so that you'd only consider the day-of-month at the month boundaries) but the the intersection is (IMO) cleaner and easier to read. Using the (PostgreSQL) arrays allows you to compare the month/day pairs with each other as single units rather than separately.