How to make a query in Postgres to group records by month they were created? (:created_at datetime column)

Question

I have this Ruby code:

def visits_chart_data(domain)
  last_12_months.collect do |month|
    { created_at: month, count: domain.visits.created_on_month(month).count }
  end
end

def last_12_months
  (0..11).to_a.reverse.collect{ |month_offset| month_offset.months.ago.beginning_of_month }
end

CreatedOnMonth is just a scope:

scope :created_on_month, -> (month = Time.now) { where{ (created_at >= month.beginning_of_month) & (created_at <= month.end_of_month) } } 

created_at is just a standard datetime timestamp.

How can I optimize it to make one query instead of 12?

I saw some people use GROUP_BY, but I'm not that good with PostgreSQL to be able to build such query myself. The query should group records by month of the year and return count. Maybe someone could help me out. Thanks.

Answer 1

Use the date_trunc() function in your GROUP BY clause, if you want to group by each month of each year:

GROUP BY date_trunc('month', created_at)

Use EXTRACT() function in your GROUP BY clause, if you want to group by each month in every year:

GROUP BY EXTRACT(MONTH FROM created_at)