Grouping by week/month/etc & ActiveRecord?

Question

I'm doing some statics calculation in my product. A user has performed a number of operations, let's say posted comments. I want to be able to show them how many comments they've posted per week for the past month, or per month for the past year.

Is there any way with activerecord to group this way? Is my best best to simply do this manually - to iterate over the records summing based on my own criteria?

class User < ActiveRecord::Base
  has_many :comments
end

class Comments < ActiveRecord::Base
  belongs_to :user
end

@user.comments(:all).map {|c| ...do my calculations here...}

or is there some better way?

thanks! Oren

Answer 1

In Postgres you can do:

@user.comments.group("DATE_TRUNC('month', created_at)").count

to get:

{"2012-08-01 00:00:00"=>152, "2012-07-01 00:00:00"=>57, "2012-09-01 00:00:00"=>132}

It accepts values from "microseconds" to "millennium" for grouping: http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC