4

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

mu is too short
  • 426,620
  • 70
  • 833
  • 800
bjork24
  • 3,153
  • 7
  • 35
  • 46

4 Answers4

12

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)
Jake Dempsey
  • 6,264
  • 1
  • 30
  • 25
  • You could also maybe use DATE_FORMAT. Not sure which would be faster though. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format – Jake Dempsey Aug 18 '11 at 03:34
  • You could use EXTRACT(YEAR_MONTH FROM last_request_at) as well. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract – Jake Dempsey Aug 18 '11 at 03:37
12

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'))
mu is too short
  • 426,620
  • 70
  • 833
  • 800
2

Assuming that you are using mysql:

User.where(["DAY(created_at) = ? AND MONTH(created_at) = ?", date.day, date.month])
Arun Kumar Arjunan
  • 6,827
  • 32
  • 35
2

Assume RDBMS is MySQL and you have form with combobox to select month and/or date_of_months, may be you could make a named_scope, for example :

scope :by_date_or_month, lambda { |date, month| {:conditions => ["DAYOFMONTH(created_at) = ? or MONTH(created_at) = ?", date, month]}}

Test from IRB :

Model.by_date_or_month(31,8)
Model.by_date_or_month(nil,8)
Agung Prasetyo
  • 4,353
  • 5
  • 29
  • 37