Rails & Postgresql: how to group queries by hour?

Question

How do I group by hour in Postgres & Rails? I've read through quite a few SO answers but I'm getting errors.

This works when grouping by date:

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

Then I tried the following for hour but they didn't work:

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

Once I've found certain hours I need to update, how would I then get the models with those hours? I.e:

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

score 16 · Accepted Answer · edited Jun 11 '15 at 06:46

16

You could try the following

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

UPDATE:

How to find records

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

edited Jun 11 '15 at 06:46

schmijos

answered Mar 03 '13 at 03:17

jvnill

Thanks, this works. Can you please have a look at my updated question - how do I get the second query to work? – mind.blank Mar 03 '13 at 03:29
If database datetime is in UTC, how can we add the desired timezone in this query. Thank you. – Bot May 17 '15 at 10:55
change `5` in the query above to something like `Time.zone.now.utc.hour`. – jvnill May 17 '15 at 11:50

score 1 · Answer 2 · answered Apr 02 '17 at 14:47

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

EXTRACT is SQL standard 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.

score 0 · Answer 3 · answered Jan 22 '18 at 22:42

If you want both the date and the hour, you can use date_trunc:

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

And if you want the answers in order:

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

Yeah it's weird that you can't just do order(:created_at) but oh well.

3 Answers3