12

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?

Xavier Guihot
  • 54,987
  • 21
  • 291
  • 190
Volodymyr Metlyakov
  • 490
  • 7
  • 12

6 Answers6

10

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/

Michiel De Mey
  • 198
  • 2
  • 6
  • 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. – Augie Gardner Apr 25 '17 at 02:37
7

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.

Sarath Nair
  • 2,828
  • 2
  • 21
  • 36
  • thx, it's a smart way and it seems to work. will check the corner cases, if any – Volodymyr Metlyakov Feb 03 '14 at 10:52
  • @VolodymyrMetlyakov - hey there, just wondering if you checked this works for all corner cases? – Ghopper21 Aug 21 '14 at 02:09
  • @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. – Ghopper21 Aug 21 '14 at 02:51
  • 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) – Volodymyr Metlyakov Aug 21 '14 at 07:53
1

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.

Oleg Matei
  • 866
  • 13
  • 9
1

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

Xavier Guihot
  • 54,987
  • 21
  • 291
  • 190
0

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.

xlembouras
  • 8,215
  • 4
  • 33
  • 42
  • 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 – Volodymyr Metlyakov Feb 03 '14 at 10:50
  • 1
    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. – xlembouras Feb 03 '14 at 11:22
0

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)

Tom Soukup
  • 83
  • 6