How to make a query in Postgres to group records by month they were created? (:created_at datetime column)
activerecord, postgresql, ruby-on-rails, sql, squeel
Solution
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)
Problem
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.