CODEWITHSUNDEEP
mongodbgroupingdayofweek
Volodymyr Metlyakov
Volodymyr Metlyakov

Reputation: 499

$week function and first day of week in aggregation

I use $project operator to extract week part from Date, then do grouping by weeks in aggregation pipeline:

{ $project: { 
    year: { $year: [ "$datetime" ] }, 
    week: { $week: [ "$datetime" ] }, 
    ....
    }
},
....
{ $group: { 
    _id: { 
        year: "$year", 
        week: "$week", 
        },
        .....
    }
}

But the $week operator I use, always counts Sunday as a first day of week, and we use Monday as first day of week in our location. Therefore, the results grouped by week are always incorrect for me.

The existing request in mongo tracking system seems to be unresolved for more than a year (is it really so rarely needed option?).

Any possible options are welcome. Maybe there is possibility to create some custom function in javascript and add/replace it somewhere?

Upvotes: 13

Views: 9814

Answers (6)

Xavier Guihot
Xavier Guihot

Reputation: 61686

Starting in Mongo 5, it's a perfect use case for the new $dateTrunc aggregation operator:

// { date: ISODate("2021-12-02") } // Thursday
// { date: ISODate("2021-12-05") } // Sunday
// { date: ISODate("2021-12-05") } // Sunday
// { date: ISODate("2021-12-06") } // Monday
// { date: ISODate("2022-12-06") } // Following year
db.collection.aggregate([
  { $group: {
    _id: { $dateTrunc: { date: "$date", unit: "week", startOfWeek: "monday" } },
    total: { $count: {} }
  }}
])
// { _id: ISODate("2021-12-06"), total: 1 }
// { _id: ISODate("2021-11-29"), total: 3 }
// { _id: ISODate("2022-12-05"), total: 1 }

$dateTrunc truncates your dates at the beginning of their week (the truncation unit). It's kind of a modulo on dates per week.

And you can specify what day is considered the start of the week using the $startOfWeek parameter which here again would otherwise default to Sunday.

Weeks in the output will be defined by their first day (mondays' date).

Upvotes: 1

Tom Soukup
Tom Soukup

Reputation: 83

Commenting on Oleg's solution:

There is a pitfall. When the year starts with Sunday, moving back one day would put you into the previous year's week num (53), but the year would still show current year. I assume.

Also, if you are using $isoWeek, you can use a similar trick, by adding one day (add one day when you want a week to start with Sunday, otherwise add nothing)

Upvotes: 0

Oleg Matei
Oleg Matei

Reputation: 906

Trick: just change one line by subtracting one day from datetime

$project: { 
    year: { $year: [ "$datetime" ] }, 
    week: { $week: {$subtract: ["$datetime", 86400000] } }, // <--- minus 1 day (in ms).
    ....
    }

This works on new and old mongo versions.

Upvotes: 1

Michiel De Mey
Michiel De Mey

Reputation: 198

MongoDB has finally added $isoWeek and $isoWeekYear in 3.4 which will start the week on a Monday.

More information: https://docs.mongodb.com/manual/reference/operator/aggregation/isoWeek/

Upvotes: 10

Sarath Nair
Sarath Nair

Reputation: 2868

You can use the below pipeline to modify the $week operation as per your requirement

[{ 
  $project: 
  {
    week: { $week: [ "$datetime" ] },
    dayOfWeek:{$dayOfWeek:["$datetime"]}
  }
},
{
  $project:
    {
      week:{$cond:[{$eq:["$dayOfWeek",1]},{$subtract:["$week",1]},'$week']}
    }
}]

What it does is that in the first stage it projects the weekOfYear and dayOfWeek. In the second stage it checks whether the dayOfWeek is sunday, if thats the case then it modifies the week to week -1. This will then function as if the week is starting on monday.

Upvotes: 7

xlembouras
xlembouras

Reputation: 8295

I think you better handle that in application space.

It would be better to keep the query simple and create a wrapper method to format the results to your desired representation.

Upvotes: 0

Related Questions