ActiveRecord Find By Year, Day or Month on a Date field

by

Assuming that your “date attribute” is a date (rather than a full timestamp) then a simple where will give you your “find by date”:

Model.where(:date_column => date)

You don’t want find_by_date_column as that will give at most one result.

For the year, month, and day queries you’d want to use the extract SQL function:

Model.where('extract(year  from date_column) = ?', desired_year)
Model.where('extract(month from date_column) = ?', desired_month)
Model.where('extract(day   from date_column) = ?', desired_day_of_month)

However, if you’re using SQLite, you’d have to mess around with strftime since it doesn’t know what extract is:

Model.where("cast(strftime('%Y', date_column) as int) = ?", desired_year)
Model.where("cast(strftime('%m', date_column) as int) = ?", desired_month)
Model.where("cast(strftime('%d', date_column) as int) = ?", desired_day_of_month)

The %m and %d format specifiers will add leading zeroes in some case and that can confuse the equality tests, hence the cast(... as int) to force the formatted strings to numbers.

ActiveRecord won’t protect you from all the differences between databases so as soon as you do anything non-trivial, you either have to build your own portability layer (ugly but sometimes necessary), tie yourself to a limited set of databases (realistic unless you’re releasing something that has to run on any database), or do all your logic in Ruby (insane for any non-trivial amount of data).

The year, month, and day-of-month queries will be pretty slow on large tables. Some databases let you add indexes on function results but ActiveRecord is too stupid to understand them so it will make a big mess if you try to use them; so, if you find that these queries are too slow then you’ll have to add the three extra columns that you’re trying to avoid.

If you’re going to be using these queries a lot then you could add scopes for them but the recommended way to add a scope with an argument is just to add a class method:

Using a class method is the preferred way to accept arguments for scopes. These methods will still be accessible on the association objects…

So you’d have class methods that look like this:

def self.by_year(year)
    where('extract(year from date_column) = ?', year)
end
# etc.

More Related Contents:

  1. How to chain scope queries with OR instead of AND?
  2. Validate uniqueness of multiple columns
  3. ActiveRecord OR query
  4. ActiveRecord: size vs count
  5. Connecting Rails 3.1 with Multiple Databases
  6. Eager load polymorphic
  7. ActiveRecord OR query Hash notation
  8. Rails order by results count of has_many association
  9. When to use a “has_many :through” relation in Rails?
  10. Rails 3: Get Random Record
  11. Rails 5: ActiveRecord OR query
  12. Setting session timeout in Rails 3
  13. Alias for column names in Rails
  14. Clean way to find ActiveRecord objects by id in the order specified
  15. Rails 3 devise, current_user is not accessible in a Model ?
  16. Rails 3 SQLite3 Boolean false
  17. Rails 3, has_one / has_many with lambda condition
  18. Rails update_attributes without save?
  19. Rails 3 – select with Include?
  20. query , can not select column count
  21. belongs_to through associations
  22. Check if record exists from controller in Rails
  23. Creating a PostgreSQL sequence to a field (which is not the ID of the record)
  24. Rails – Validate Presence Of Association?
  25. How to implement bulk insert in Rails 3
  26. PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
  27. Specifying column name in a “references” migration
  28. Rails: Order with nulls last
  29. Multiple databases in Rails
  30. Rails: How do I create a default value for attributes in Rails activerecord’s model? [duplicate]

Leave a Comment