ActiveRecord Find By Year, Day or Month on a Date field
Solution 1
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.
Solution 2
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
Solution 3
In your Model:
scope :by_year, lambda { |year| where('extract(year from created_at) = ?', year) }
In your Controller:
@courses = Course.by_year(params[:year])
Solution 4
For MySQL try this out
Model.where("MONTH(date_column) = 12")
Model.where("YEAR(date_column) = 2012")
Model.where("DAY(date_column) = 24")
Solution 5
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 )
Mutuelinvestor
Ruby jruby Ruby on Rails Celerity Mongodb Angularjs Phantomjs
Updated on July 05, 2022Comments
-
Mutuelinvestor almost 2 years
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.
-
mu is too short about 12 yearsA day is not an open interval, it is a half-open interval.
-
mu is too short about 12 years
'd >= ? and d < ?', dt.beginning_of_day, dt.tomorrow.beginning_of_day
. Using<
and>
misses the boundary between days. -
Mutuelinvestor about 12 yearsThanks for a great answer. It seems to do this you have to rely upon database specfic function which defeats one of the benefits of activerecord. Perhaps that's why you see dates broken out by month, day and year so often. Thanks a again.
-
apneadiving about 12 yearsalways impressed by the quality of your answers
-
ITmeze about 12 yearsi am not quite sure this is something that Mutuelinvestor was asking for as it does not cover case when user is asking for all posts from April, neglecting 'year' part
-
d_rail over 10 yearsThere's also
beginning_of_day
/end_of_day
andbeginning_of_month
/end_of_month
for the other implementations. -
Sixty4Bit over 9 yearsThis is a far superior answer since it will use an index if available. Mu's will do a table scan.
-
Jay El-Kaake about 8 yearsThis was really helpful, although for SQLite I had to
CAST
the date number to an integer instead of adding 0 (adding 0 gave me the wrong number for some reason). My revised filters are then: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)
-
mu is too short about 8 years@JayEl-Kaake: I think an explicit
cast(... as int)
is better than trickery in any case, I'm not sure why I didn't usecast
in the first place. -
Heriberto Magaña almost 7 yearsNo need for extract method in pg you can use
TO_CHAR
withILIKE
in only one line, I posted the full example at the end of this thread -
monteirobrena over 5 years@NarenP I don't think so. PostgreSQL haven't the
MONTH
function. Try extract or date_part. wiki.postgresql.org/wiki/MONTH()_equivalent postgresqltutorial.com/postgresql-date_part -
Giridharan over 5 yearsbut iam using sqlite3 how to do this
-
mu is too short over 5 years@giridharan There's a "However, if you're using SQLite, you'd have to mess around with
strftime
since it doesn't know whatextract
is" section already there. -
Whatcould almost 3 years
dt = DateTime.new(year)
is already the beginning of the year; you don't need the extra datetime. Eg,boy = DateTime.new(year); eoy = boy.end_of_year