rails - group by day as well as hour

Question

I want to create an array of the number of items created each hour, each day.

I'm tracking how people are feeling, so my model is called TrackMood It just has a column called mood and the timestamps.

If I do

TrackMood.where(mood: "good").group("hour(created_at)").count

I get something like {11=>4, 12=>2, 13=>2, 15=>1}

I've got 2 issues here

1 How do I add the day into this so it doesn't just add the items created yesterday at 11 o'clock to the items added today at 11 o'clock?

2 How do I make sure it says 0 for hours when nothing is created?

Answer 1

1) Instead of grouping on just the hours part of the date you'll need to group part of the date that is relevant i.e. the date up to the hours and not including anything more specific than that. E.g.

TrackMood.where(mood: "good").group("date_format(created_at, '%Y%m%d %H')").count

2) You're always going to get a hash back from this call even if it doesn't find any groups. If you want to check how many groups there are you can call .size or .count on it.

Answer 2

For PostgreSQL you can use date_part

SO-post - Rails & Postgresql: how to group queries by hour?