Given a collection of events, aggregate their duration weekly. Fill gaps to contain every week since the earliest event.
Data Model
Users have meetings.
class Meeting
include Mongoid::Document
include Mongoid::Timestamps
include ActionView::Helpers::DateHelper
field :started_at, type: DateTime
field :finished_at, type: DateTime
belongs_to :user
end
Duration Aggregated Weekly
The following aggregation matches any documents that belong to a user, groups and sorts them by year and week number, then counts and sums their durations.
user.meetings.collection.aggregate([
{ '$match' => { user_id: user.id } },
{
'$group' => {
_id: {
year: { '$year' => '$started_at' },
week: { '$isoWeek' => '$started_at' }
},
count: { '$sum' => 1 },
duration: { '$sum' => { '$subtract' => ['$finished_at', '$started_at'] } }
}
},
{ '$sort' => { _id: -1 } }
])
Filling Gaps
The code above will miss any week without data. It’s easier to fill those in Ruby by iterating over weeks, starting with the current week and finishing with the week of the oldest available data.
# current week start
week_start = Time.now.utc.beginning_of_week.to_date
# use the week_start from the last record or the current week
last_week_start = data.empty? ? week_start : data.values[-1].week_start
while week_start >= last_week_start
results << ... # either from data or a blank entry
week_start -= 1.week
end
Complete Code
See weekly_meetings.rb for a complete implementation.