how to group record counts by hour of day

Question

Here are my models:

Interaction.rb

class Interaction < ActiveRecord::Base

  belongs_to :report
  belongs_to :post
  belongs_to :fb_user
  has_one :fb_page

Report.rb

class Report < ActiveRecord::Base

  belongs_to :user

  has_many :page_stats,
    dependent: :destroy
  has_many :interactions,
    dependent: :destroy
  has_many :fb_users,
    through: :interactions
  has_one :fb_page,
    foreign_key: :fb_id,
    primary_key: :fb_page_id
  has_many :posts,
    dependent: :destroy

FbUser.rb

class FbUser < ActiveRecord::Base

  self.primary_key = "id"
  has_many :interactions

What I would like to do is group Interactions by the hour of day that they occurred. I'm looking for a hash of 1-24 (hours of the day, UTC) and group the Interaction counts by when they occurred.

Here's an example return value:

{{1 => 23}, {2 => 7}, {n => x}}

Basically, for hour 1, there were 23 interactions, for hour 2, there were 7. I want to do this for each 1 through 24 hours of the day.

Interaction has a column called "interaction_time" which is when the interaction occurred. thanks

Answer 1

For postgres,

Interaction
.select('EXTRACT(HOUR from interaction_time)')
.group('EXTRACT(HOUR FROM interaction_time)')
.order('EXTRACT(HOUR from interaction_time)')
.count