Rails - a group during the day, as well as an hour

Question

I want to create an array of elements created every hour, every day.

I track how people feel, so my model is called TrackMood It only has a column named mood and 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 have 2 problems here

1 How to add a day to this so that it doesn’t just add elements created yesterday at 11 o’clock to elements added today at 11 o’clock?

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

Answer 1

1) Instead of grouping only part of the hours in a date, you need to group the part of the date that matters, that is, the date before the clock and not include something more specific. For instance.

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

2) You will always receive a hash from this call, even if it does not 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 and Postgresql: how to group requests by hours?