CODEWITHSUNDEEP
ruby-on-railsruby
Jack Dawson
Jack Dawson

Reputation: 3

How to get records created in each month in rails

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.

Upvotes: 0

Views: 1480

Answers (2)

Holger Just
Holger Just

Reputation: 55718

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

Upvotes: 1

port5432
port5432

Reputation: 6371

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.

Upvotes: -1

Related Questions