CODEWITHSUNDEEP
ruby-on-railsrubydate
Meltemi
Meltemi

Reputation: 38359

How to run Rails queries over multiple date ranges (weeks)

I'm trying to iterate over each week in the calendar year and run a query. 

range = Date.new(2020,3,16)..Date.new(2020,3,22)
u = User.where(created_at: range).count

But I'd like to do this for EACH week in another range (say since the beginning of this year).

Ruby's Date has a cweek function that gives you the week number but there doesn't seem to be a way to easily get from the week number to the date range.

Anyway, not sure how helpful cweek will be as I need week to run Sunday -> Saturday.

Thoughts?

Upvotes: 0

Views: 252

Answers (1)

max
max

Reputation: 101811

I'm assuming this is Postgres and the model name is User based on your previous question.

If this blog is to to believed you can shift a date one day to get sun-sat day week.

User.group("(date_trunc('week', created_at::date + 1)::date - 1)")
    .count

If you want the to select the actual week number while you are at it you can select raw data from the database instead of using ActiveRecord::Calculations#count which is pretty limited.

class User
  # @return [ActiveRecord::Result] 
  #  the raw query results with the columns count, year, week
  def self.count_by_biblical_week
    connection.select_all(
      select(
        "count(*) as count",
        "date_part('year', created_at)::integer as year",
        "(date_part('week', created_at::date + 1) - 1)::integer as week"
      ).group(:week, :year) 
    )
  end
end

Usage:

results = User.where(created_at: Date.new(2020,3,16)..Date.new(2020,3,22))
              .count_by_biblical_week
results.each do |row|
  puts  [row[:year], row[:week], row[:count]].join(' | ')
end

Adding the year to the group avoids ambiguity if the results span multiple years.

Upvotes: 1

Related Questions