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

I have an ActiveRecord model that has a date attribute. Is it possible to utilize that date attribute to find by Year, Day and Month:

Model.find_by_year(2012)
Model.find_by_month(12)
Model.find_by_day(1)

or is it simply possible to find_by_date(2012-12-1).

I was hoping I could avoid creating Year, Month and Day attributes.

67133 次浏览
小开

I think the easiest way to do this is a scope:

scope :date, lambda {|date| where('date_field > ? AND date_field < ?', DateTime.parse(date).beginning_of_day, DateTime.parse(date).end_of_day}

Use it like this:

Model.date('2012-12-1').all

That will return all models with a date_field between the beginning and end of day for the date you send.

小开
最佳答案

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.
小开

For MySQL try this out 

Model.where("MONTH(date_column) = 12")
Model.where("YEAR(date_column) = 2012")
Model.where("DAY(date_column) = 24")
小开

Database independent version ...

def self.by_year(year)
dt = DateTime.new(year)
boy = dt.beginning_of_year
eoy = dt.end_of_year
where("published_at >= ? and published_at <= ?", boy, eoy)
end
小开

In your Model:

scope :by_year, lambda { |year| where('extract(year from created_at) = ?', year) }

In your Controller:

@courses = Course.by_year(params[:year])
小开

I like BSB's answer but as a scope

scope :by_year, (lambda do |year|
dt = DateTime.new(year.to_i, 1, 1)
boy = dt.beginning_of_year
eoy = dt.end_of_year
where("quoted_on >= ? and quoted_on <= ?", boy, eoy)
end)
小开

This would be another one:

def self.by_year(year)
where("published_at >= ? and published_at <= ?", "#{year}-01-01", "#{year}-12-31")
end

Works pretty well for me in SQLite.

小开

Try this:

Model.where("MONTH(date_column) = ? and DAY(date_column) = ?", DateTime.now.month, DateTime.now.day)
小开

Simple Implementation for Just The Year

app/models/your_model.rb

scope :created_in, ->( year ) { where( "YEAR( created_at ) = ?", year ) }

Usage

Model.created_in( 1984 )
小开

No need for extract method, this works like a charm in postgresql:

text_value = "1997" # or text_value = '1997-05-19' or '1997-05' or '05', etc
sql_string = "TO_CHAR(date_of_birth::timestamp, 'YYYY-MM-DD') ILIKE '%#{text_value.strip}%'"
YourModel.where(sql_string)
小开

Another short scope as below:

  class Leave
scope :by_year, -> (year) {
where(date:
Date.new(year).beginning_of_year..Date.new(year).end_of_year)
}
end

Call the scope: 

Leave.by_year(2020)
小开

In Postgresql v 11+(i've tested): below way works

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

I usually use:

Model.where('extract(month from birthday_column) = ? and extract(day from birthday_column) = ?', Time.now.month, Time.now.day)
小开

If you're looking for just the date, simply do:

Model.where(date: Date.today.beginning_of_day..Date.today.end_of_day)
小开

To look for all the records matching a specific day / month / year you could do:

Model.where(date: Date.today.all_day)
Model.where(date: Date.today.all_month)
Model.where(date: Date.today.all_year)

.all_X returns a date range:

 Time.current.all_year
=> Sat, 01 Jan 2022 00:00:00.000000000 UTC +00:00..Sat, 31 Dec 2022 23:59:59.999999999 UTC +00:00

提交答案