Ruby-on-rails ActiveRecord 在日期字段中按年、日或月查找

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

提问by Mutuelinvestor

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:

我有一个具有日期属性的 ActiveRecord 模型。是否可以利用该日期属性按年、日和月查找：

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).

或者是否可以简单地找到_by_date(2012-12-1)。

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

我希望我可以避免创建年、月和日属性。

回答by mu is too short

Assuming that your "date attribute" is a date (rather than a full timestamp) then a simple wherewill give you your "find by date":

假设您的“日期属性”是一个日期（而不是完整的时间戳），那么一个简单的方法where将为您提供“按日期查找”：

Model.where(:date_column => date)

You don't want find_by_date_columnas that will give at most one result.

你不想要，find_by_date_column因为这最多只能给出一个结果。

For the year, month, and day queries you'd want to use the extractSQL function:

对于年、月和日查询，您希望使用extractSQL 函数：

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 strftimesince it doesn't know what extractis:

但是，如果您使用的是 SQLite，则必须搞砸，strftime因为它不知道是什么extract：

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 %mand %dformat 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.

在%m与%d格式说明会在某些情况下，添加前导零，并能迷惑平等的测试，因此cast(... as int)迫使格式化字符串的数字。

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).

ActiveRecord 不会保护您免受数据库之间的所有差异的影响，因此一旦您做任何重要的事情，您要么必须构建自己的可移植层（丑陋但有时是必要的），将自己绑定到一组有限的数据库（现实，除非你正在发布一些必须在任何数据库上运行的东西），或者在 Ruby 中完成你的所有逻辑（对于任何非平凡的数据量来说都是疯狂的）。

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.

年、月和月日查询在大表上会很慢。一些数据库允许你在函数结果上添加索引，但 ActiveRecord 太愚蠢而无法理解它们，所以如果你尝试使用它们会造成很大的混乱；因此，如果您发现这些查询太慢，那么您将不得不添加您试图避免的三个额外列。

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 argumentis 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.

回答by BSB

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

回答by Lane

In your Model:

在您的模型中：

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

In your Controller:

在您的控制器中：

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

回答by Beena Shetty

For MySQL try this out

对于 MySQL，试试这个

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

回答by Veraticus

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.

这将返回在您发送的日期的开始和结束之间具有 date_field 的所有模型。

回答by Joshua Pinter

Simple Implementation for Just The Year

一年的简单实施

app/models/your_model.rb

app/models/your_model.rb

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

Usage

用法

Model.created_in( 1984 )

回答by monteirobrena

Try this:

尝试这个：

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

回答by Shiko

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)

回答by idrinkpabst

I like BSB's answer but as a scope

我喜欢 BSB 的回答，但作为一个范围

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)

回答by Tintin81

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.

在 SQLite 中对我来说效果很好。