Rails & Postgresql: how to group requests by hours?

Question

How do I group by clock in Postgres and Rails? I have read some SO answers, but I am getting errors.

This works when grouping by date:

Model.group("date(updated_at)").count 

Then I tried the following for an hour, but they did not work:

 Model.group("hour(updated_at)").count Model.group("date_format(updated_at, '%H')").count Model.group("extract(hour from updated_at)").count 

As soon as I found a few hours, I need to update, how can I get models with this watch? I.e:

 Model.where("hour(updated_at) = ?", 5) 

Answer 1

You can try the following

 Model.group("DATE_PART('hour', updated_at)").count 

UPDATE:

How to find records

 Model.where("DATE_PART('hour', updated_at) = ?", 5) 

Answer 2

PostgreSQL offers two ways to extract a part of a date / time:

EXTRACT is a standard SQL function that exists in other SQL-based databases.

 Model.group("EXTRACT(hour FROM created_at)").count Model.where("EXTRACT(hour FROM created_at) = ?", 5) 

date_part Function

 Model.group("date_part('hour', updated_at)").count Model.where("date_part('hour', updated_at) = ?", 5) 

The official documentation .

Answer 3

If you want both date and time, you can use date_trunc:

Model.group("date_trunc('hour', created_at)").count

And if you want the answers to be in order:

Model.order("date_trunc('hour', created_at)").group("date_trunc('hour', created_at)").count

Yes, it is strange that you cannot just do order(:created_at) , but ok.