Use active record to find a record by month and day, ignoring year and time

Question

I have a model (Entries) with five years worth of records (one record per day). I need a method that, when passed a date object such as 2011-12-25 00:00:00, will show me ALL the records that have happened on 12/25 (querying against the :created_at column), regardless of the year or time that's passed.

RoR 3.0.9 / Ruby 1.9.2p290

Answer 1

You can use the MONTH and DAY values of mysql. Maybe something like:

Model.where("MONTH(created_at) = ? and DAY(created_at) = ?", somedate.month, somedate.day)

Answer 2

A general solution that should work with most SQL databases (include MySQL and PostgreSQL):

Entry.where('extract(month from created_at) = ? AND extract(day from created_at) = ?', d.month, d.day)

SQLite doesn't understand extract though so you'd have to use:

Entry.where("strftime('%m/%d', created_at) = ?", d.strftime('%m/%d'))