How to create an Upcoming Birthdays module in Rails?

Question

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

Passed age, trying to work out the logic .. also looked at every possible article on Google without any luck.

Any suggestions on how to do this in RoR?

Thanks!

Answer 1

Several answers suggested calculating / saving the day of the year and sorting them out, but this alone will not bring much benefit when you are approaching the end of the year, and you need to consider people with birthdays at the beginning of next year.

I would choose a solution in which you calculate the full date (year, month, day) of each person on the next birthday, and then sort by this question. You can do this in Ruby code or use a stored procedure in a database. The latter will be faster, but will make your application more difficult to switch to another db platform later.

It would be wise to update this list of upcoming birthdays once a day, which means that you can use some form of caching. So the speed of the requested request / code is not a problem, and something like this should work fine while 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 correct execution with leap years.

Answer 2

Thanks to this post in my rails 3 application, I use:

  u = User.where("strftime('%m%d', date_of_birth) = ?", Date.today.strftime('%m%d')) 

Update:

To use this with Postgresql:

 u = User.where("extract(month from date_of_birth) = ? AND extract(day from date_of_birth) = ?", Date.today.strftime('%m'), Date.today.strftime('%d')) 

Answer 3

It is better to use SQL to make this query:

 next_month = (Date.today + 1.month).month # or use any other integer users_having_birhtday_next_month = User.where("EXTRACT(MONTH FROM date_of_birth) = ?", next_month) 

Note: EXTRACT - PostgreSQL function

Answer 4

I would have a before_save callback that computes and stores the day of the year in the database along with the birthday.

Then you have a simple request to cancel the next 5 birthdays. Do not forget to process the boundary condition where you are at the end of the year (I would check if you do not get 5 results in RoR, and then run a new query for January 1 to get additional birthdays to make it up to 5).

You might want to cache the results so that you don’t repeat the request if it is on a shared page.

Answer 5

I also thought that the day of the year would be a way for you, but the fact that it differs for most of the year depending on whether it is a leap year or not makes it difficult.

It is better to store the month and day as a string: d.strftime ('% m% d'). Then you can use this as (possibly) two queries (assuming the new column is "monthday")

Firstly,

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

If you do not get 5 results, repeat the request, except for using "0101" instead of calculating the date and below the limit.

This gives you a list of monthday strings, which you then need to return to dates.

If you want users, delete the line :select .

Answer 6

If you are working in Oracle, you can do this without creating a new column. IMO it's a scent to create a column that contains data that you already have.

SQL is a little ugly - I'm sure there is a more elegant way to do this. Typically, in these cases, I would 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 that a repeating column is faster, but if you have enough user data that speeds up the problem, you should compare the duplicate column with a table without it, which has a functional index on TO_NUMBER(TO_CHAR(dob, 'MMDD')) .

Answer 7

Here's how I find birthdays today:

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

I run it once a day. It takes less than a second from about 100,000 lines. (He doesn’t handle people born February 29th.)

Answer 8

If your database is mysql, maybe faster:

 scope :birthday_next_week, (lambda { where('DayOfYear(date_of_birth) >= 7 And DayOfYear(date_of_birth) - DayOfYear(curdate()) Between 0 and 6) Or (MOD(YEAR(curDate()),4) = 0) And MOD(YEAR(curDate()),100) != 0 And (DayOfYear(date_of_birth) + 366 - DayOfYear(curdate())) % 366 < 7) Or (DayOfYear(date_of_birth) + 365 - DayOfYear(curdate())) % 365 < 7'). order('DATE_FORMAT( date_of_birth, "%m-%d" ) ASC') }) 

Edit: changed to make it work a week before the new year or years.