How to create "Upcoming birthdays" module in Rails?

Question

Hi,

I have a table "users" with a column "date_of_birth" (DATE format with day, month, year). In frontend I need to list 5 upcoming birthdays.

Spent ages trying to work out the logic.. also browsed every possible article in Google with no luck..

Any suggestions how to do this in RoR?

Thanks!

Answer 1

I'd have a before_save callback that calculates and stores to the day of the year in the database alongside the birthday.

You then have a simple query to pull back the next 5 birthdays. Make sure to handle the boundary condition where you are at the end of the year (I'd check if you don't get 5 results in RoR and then run a new query for the 1st Jan to get some extra birthdays to make it up to 5).

You will probably want to cache the results so you don't keep rerunning the query if it is on a common page.

Answer 2

I too thought that day of year would be the way to go, but the fact that it is different for most of the year depending on whether it is a leap year or not makes it tricky.

Better is to store the month and day as a string: d.strftime('%m%d'). You can then use that as (possibly) two queries (assuming new column is 'monthday')

First,

User.find(:all,
          :condition => [:monthday > Date.now.strftime('%m%d')],
          :select => "DISTINCT monthday",
          :limit => 5)

If you don't get 5 results, do the query again, except use "0101" instead of the date calculation and lower the limit.

This gets you a list of monthday strings that you then have to turn back into dates.

If you want users, remove the :select line.

Answer 3

If you're on Oracle, you can do it without creating a new column. IMO it's a smell to create a column that contains data you already have.

The SQL's a bit ugly - I'm sure there's a more elegant way to do it. Generally in these cases I'd ask my DBA friends for advice.

User.find(:all,
  :conditions => 
    "TO_NUMBER(TO_CHAR(dob, 'MMDD')) >= TO_NUMBER(TO_CHAR(SYSDATE, 'MMDD'))",
  :order => "TO_NUMBER(TO_CHAR(dob, 'MMDD'))",
  :limit => 5)

Some people think a duplicate column is faster, but if you have enough user data that speed's an issue, you should benchmark the duplicate column against a table without it that has a functional index on TO_NUMBER(TO_CHAR(dob, 'MMDD')).

Answer 4

Several answers have suggested calculating/storing day of year and sorting on that, but this alone won't do much good when you're close to the end of the year and need to consider people with birthdays in the beginning of the next year.

I'd go for a solution where you calculate the full date (year, month, day) of each person's next birthday, then sort on that. You can do this in Ruby code, or using a stored procedure in the database. The latter will be faster, but will make your app harder to migrate to a different db platform later.

It would be reasonable to update this list of upcoming birthdays once per day only, which means you can use some form of caching. Thus the speed of the query/code needed is less of an issue, and something like this should work fine as long as you cache the result:

class User
  def next_birthday
    year = Date.today.year
    mmdd = date_of_birth.strftime('%m%d')
    year += 1 if mmdd < Date.today.strftime('%m%d')
    mmdd = '0301' if mmdd == '0229' && !Date.parse("#{year}0101").leap?
    return Date.parse("#{year}#{mmdd}")
  end
end

users = User.find(:all, :select => 'id, date_of_birth').sort_by(&:next_birthday).first(5)

Edit: Fixed to work correctly with leap years.

Answer 5

Here's how I find today's birthdays:

User.find_by_sql("select * from users where date_format(date_of_birth, '%m%d') = date_format(now(), '%m%d')")

I run this once a day. It takes less than a second from about 100,000 rows. (It doesn't properly handle people born on Feb 29th.)