How to get records created in each month in rails

Question

Looking for a way to get all the records that are created in each month for a table

For example i need to know how to get a result like:

January: 6,
Feb: 9,
March: 10

Ideally i'm looking at using the created_at field in the database to compare against.

What you are looking for is the SQL GROUP BY clause, coupled with COUNT. There's a bunch of discussion and solutions to that type of problem here: https://stackoverflow.com/questions/31879150/group-by-and-count-using-activerecord — Casper, May 27 '22 at 11:25
This might be even better: https://stackoverflow.com/questions/902974/grouping-by-week-month-etc-activerecord — Casper, May 27 '22 at 11:45

Holger Just · Answer 1 · 2022-05-28T12:34:20.467

You can use GROUP BY and COUNT from within SQL to efficiently retrieve the data. Rails offers various options here to build an SQL query which performs aggregations and calculations with ActiveRecord::Calculations.

Assuming you have a model named Record for your records and you use MySQL / MariaDB for your database, this can be used to get the number of records per month:

records_per_month = Record.group('EXTRACT(YEAR_MONTH FROM created_at)').count

This will return a hash of Integers (corresponding to the year and month of the group so that e.g. records in May 2022 will groups under the key 202205) and the number of records within this month as values.

From your example, this would be

{
  202201 => 6,
  202202 => 9,
  202203 => 10
}

If desired, you can then further "format" the keys, e.g.

records_per_month.transform_keys! do |year_month|
  Date.strptime(year_month.to_s, '%Y%m').strftime('%B %Y')
end

Here, we parse year-month integer as a date with Date.strptime and format the date with Date#strftime to show the month name and year, e.g. "February 2022".

score -1 · Accepted Answer · answered May 27 '22 at 13:14

Imagine you have a Users table (my Rails application has one), like this:

id
name
.
.
.
created_at
updated_at

You could use this code, which would return a hash of months with the count:

users = User.all
users.group_by {|u| u.created_at.strftime("%B")}.transform_values {|v| v.count}

Returns something like:

{"September"=>33,
 "August"=>1,
 "October"=>1,
 "February"=>55,
 "January"=>185,
 "May"=>4,
 "December"=>145,
 "June"=>8,
 "November"=>19,
 "March"=>51,
 "April"=>27,
 "July"=>5}

Explanation

created_at.strftime("%B")

This converts the date to a Month, using strftime

users.group_by {|u| u.created_at.strftime("%B")}

Creates a hash that groups the user records by the Month name, using group_by

.transform_values {|v| v.count}

Instead of a collection of records, we just want the count. We leave the key alone in the hash, and use transform_values to count the values.

This is very inefficient since the grouping and counting all happens within Rails. Thus, Rails first has to load all records and create AR objects for that, just to throw them all away again after the aggregation. Depending on the number of objects, this can (1) take a lot of time and (2) use a lot of memory. A better solution would be to use `grorp by` in SQL, retrieving only the required data. — Holger Just, May 27 '22 at 13:56

How to get records created in each month in rails

2 Answers2