CODEWITHSUNDEEP
ruby-on-railspostgresqldate
Meltemi
Meltemi

Reputation: 38359

How to return Postgres records grouped by week in Rails

Rails query ninjas, I'm having a tough time getting records out of my Postgres DB grouped by the week they were created.

(before you block this question for being a duplicate see that I've tried what I've found elsewhere on SO but my results aren't useful. I'm still missing something.)

Given an ActiveRecord User object with a created_at timestamp I would like to quickly find out how many Users were created during each week of the year.

I've tried:

> User.select("DATE_TRUNC('week', created_at) as week").group("week")
  User Load (0.4ms)  SELECT DATE_TRUNC('week', created_at) as week FROM "users" GROUP BY week
[#<User:0x00007fb73892e978 id: nil>,
 #<User:0x00007fb73892e7e8 id: nil>,
 #<User:0x00007fb73892e5e0 id: nil>,
 #<User:0x00007fb73892e400 id: nil>,
 #<User:0x00007fb73892e270 id: nil>]

This seems to work as there are 7 records in this test data and 2 share same week but I want a numerical response for the total next to the week number:

Week     Count
   1         2
   2         1
   3         1
   4         5

Or a hash or anything usable.

Or plain ruby User.group(:created_at).count but I'm not sure how to work the week number into this query.

Thanks for any help!

Upvotes: 0

Views: 418

Answers (1)

max
max

Reputation: 102222

User.group("DATE_TRUNC('week', created_at)").count

This will give you a hash with dates as keys and counts as values:

=> #<User id: 2, email: "[email protected]", created_at: "2020-03-09 22:47:51", updated_at: "2020-03-23 22:47:51", display_name: "Test2">
irb(main):020:0> User.group("DATE_TRUNC('week', created_at)").count
   (0.4ms)  SELECT COUNT(*) AS count_all, DATE_TRUNC('week', created_at) AS date_trunc_week_created_at FROM "users" GROUP BY DATE_TRUNC('week', created_at)
=> {2020-03-23 00:00:00 UTC=>1, 2020-03-09 00:00:00 UTC=>1}

You can get the week numbers by:

 User.group("DATE_TRUNC('week', created_at)")
     .count
     .transform_keys {|d| d.strftime('%V') }

Upvotes: 4

Related Questions