15. Date-Based Analytics and Time Bucketing
Date analytics often group records by day, week, month, quarter, or year. MongoDB provides $year, $month, $dayOfMonth, $hour, $dateTrunc, and $dateToString.
When grouping across several years, do not group by month number alone because January in different years would be combined. $dateTrunc is useful for calendar buckets. Choose the reporting timezone explicitly; UTC midnight may not equal local midnight. Store timestamps as BSON Date values and use half-open intervals for source filters.
Timezone is part of the report definition
Use consistent timezone settings for both source date boundaries and calendar buckets.
Code Example
db.orders.aggregate([
{ $match: { status: "delivered" } },
{ $set: { monthStart: { $dateTrunc: { date: "$createdAt", unit: "month", timezone: "UTC" } } } },
{ $group: { _id: "$monthStart", orderCount: { $sum: 1 } } },
{ $sort: { _id: 1 } },
{ $project: { _id: 0, monthStart: "$_id", orderCount: 1 } }
]);Key Takeaways
- Date expressions support calendar reporting.
- Include year when grouping by month across years.
- $dateTrunc creates time buckets.
- Choose the reporting timezone explicitly.
Knowledge Check
Why is grouping by $month alone incorrect for multi-year reports?