9

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?

Edward
  • 3,429
  • 2
  • 27
  • 43

2 Answers2

12

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.

Shadwell
  • 34,314
  • 14
  • 94
  • 99
  • Brilliant for q 1. For the second part, I can't have made myself clear. I would like 14=>0 to be in the hash if no items are created in that hour. – Edward May 24 '12 at 16:04
  • Ah sorry, okay, I understand now. To be honest you're probably best off adding in the empty groups afterwards using ruby rather than asking the persistence layer to provide them. – Shadwell May 24 '12 at 16:07
  • When I try this I get the following error: "PG::UndefinedFunction: ERROR: function date_format(timestamp without time zone, unknown) does not exist". Im using Rails 5.2.2 – Bernardo OS Feb 28 '19 at 06:23
  • 2
    For postgres the function is `date_part` as per this answer : https://stackoverflow.com/a/20637136/120518 – Shadwell Feb 28 '19 at 10:52
1

For PostgreSQL you can use date_part

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

Community
  • 1
  • 1
swapab
  • 2,402
  • 1
  • 27
  • 44