How to get the sum of values ​​by group from a table?

Question

PostgreSQL

Hey! I have online records in my database

id | user_id | count | created_at
1  | 1       | 12    | сегодня
2  | 4       | 26    | вчера

And I display a graph to users, where I show when and what was online.
And now I need the admins to display the general schedule. To do this, you need to group by date (all users) and get the amount online.
However, everyone's date is different. It should be rounded down to the nearest hour.
Help, I'm really confused.

Answer 1

Revised the architecture of the application. Now there is no such issue. Thanks to all.

Answer 2

How about .group('FLOOR(UNIX_TIMESTAMP(created_at) / 3600)')

Answer 3

@grouped_entities = Entity.order('created_at DESC').group_by { |group| group.created_at.to_date }

Entity is our model model (table).
.order('created_at DESC') – sort in descending order.
.group_by { |group| group.created_at.to_date } – group by date. Note that you have to turn created_at into a date (to_date). Since we are grouping by date, not by seconds.
Now in the View you can do the following:

<% @grouped_entities.each do |date, record_group| %>
  <h1>date</h1>
  <% record_group.each do |record| %>
    <%= record.some_method_to_display %>
  <% end %>
<% end %>