Take the 2-minute tour ×

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!

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

Sounds like you're looking for EXTRACT:

    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:

    select * from ms where array[? ,?] <= array[extract(month from bday)::int, extract(day from bday)::int]
    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:

    "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.

share|improve this answer
Great. I was only testing with a couple of records and the previous query seemed to work, but now I can see where that one would give incorrect results. Thank you so much! I'm no SQL guru, so I always need help on this sort of thing. –  Andrew Dec 8 '11 at 15:59
@Andrew: I originally made the same mistake you probably did and I'm supposed to be good at this stuff :) Inferring a query based on a limited data set is fraught with danger and that's why we check each other's work. –  mu is too short Dec 8 '11 at 18:29
Will this work across months (e.g., 10/31-11/4)? For example, when I run the first part of the query for 10/31, it only picks up birthdays happening on or after the 31st (i.e., only months with 31 days and not 11/1, 11/2, etc.). So my intersection keeps turning up '0' even though I have birthdays in between that time range. Also, I get a 'wrong number of arguments (5 for 2)' from the Rails console when I load the query as written in this answer. –  JHo Oct 31 '12 at 14:28
@JHo: There were some missing brackets in the find_by_sql call, sorry about that. I think you might be right about the edge case, I'll get my head around this again and get back to you tonight. –  mu is too short Nov 1 '12 at 2:21
@JHo: You were right, thanks for pointing out the brain damage. I've updated with some better options with, hopefully, less brain damage. –  mu is too short Nov 1 '12 at 2:57
show 2 more comments

There are various date-functions in sql. Some of which just pull out the day/month (independent of the year). I'm pretty sure there's one that just returns the "day of the year" (ie 1 to 365) (and if there isn't you could write one) - in which case you could use that and grab all rows that have their day-of-year within a small range of the one you've got in your hand right now.

share|improve this answer
You could run into leap year issues with extract(doy from bday) though, probably safest to consider the day-of-month and month separately. –  mu is too short Dec 6 '11 at 20:04
Awesome. I ended up using extract and considering month and day of month separately, and it's working like a charm! –  Andrew Dec 6 '11 at 20:24
@Andrew: Are you sure it is working like a charm? You should only look at the day-of-month when then month number is equal to the upper or lower month (I admit that I made that mistake in my original attempt). –  mu is too short Dec 6 '11 at 20:31
add comment

Your Answer


