$week function and first day of week in aggregation

Question

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?

Khat

Would you add some sample data so we can try it out our responses before posting. Thanks. – Coparcener

Answer 1

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/

Sr

Note to most - as of 4/24/2017, MongoDB v3.4 is still widely not used on cloud hosted platforms like MongoHQ... They have beta versions available that you might be invited to (I was)... Keep this in mind in case you use something like Heroku, or a cloud hoster separately, you won't be able to use 3.4 features for maybe a few months. – Fictional

Answer 2

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.

Halinahalite

thx, it's a smart way and it seems to work. will check the corner cases, if any – Khat

@VolodymyrMetlyakov - hey there, just wondering if you checked this works for all corner cases? – Marqueritemarques

@VolodymyrMetlyakov - also, as written, these projection stages will filter out all the other fields, as other fields must be included explicitly to pass through these stages. – Marqueritemarques

yes it worked fine for all our cases. however, due to huge amount of data to be processed in aggregation (up to billion records), we removed "$projection" stage at all, as it turned out to be slowing down overall performance (not particularly this calculation, but the presence of projection stage in general for some reason is counter-productive in our case) – Khat

Answer 3

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.

Sharolynsharon

this is smart, if there are no pitfalls :) – Khat

Answer 4

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).

Abirritant

Answer 5

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.

Guienne

the application level is always an option. as for me, it is last resort in case if no appropriate solution on db level is found. especially i would like to avoid fiddling around with date stuff, if possible – Khat

Your POV is correct, it is a last resort. I just provide it as an alternative. It might be useful if your data are not that many and you want to avoid the extra db complexity. – Guienne

Answer 6

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)

Lauranlaurance