0

In mongoDB aggregator, when I try to '$group' the data by '$week' to show weekly comparisons and statistics, I run into the problem that $week aggregator starts on a Sunday instead of Monday so all the data gets skewed by one day.

The specific date field that I am trying to group by is called 'dateWorked' in this case.

When I try to fix this, by subtracting one day from 'dateWorked', the dates get adjusted but then the rest of my data is not grouped properly. All the '$sum' operators return zero when they have numbers they should be adding. Below is the query I am using and sample json data.

I also tried implementing this solution here, but it has the same problem. None of my other data is "$sum"-ed properly. Any ideas on how to resolve this problem?

Timesheet.aggregate([
    {$match: {
            'userInfo.sub': req.params.id
        }
    },
    {$project: {
        dateWorked: {$subtract: [ "$dateWorked", 24 * 60 * 60 * 1000]}
    }
    },
    {"$sort": {dateworked:1}},
    {$group: {
            _id: {$week: '$dateWorked'},
            weekOf: {$push: '$dateWorked'},
            docCount: {$sum: 1},
            dev: {$sum: '$dev'},
            qa: {$sum: '$qa'},
            admin: {$sum: '$admin'},
            other: {$sum: '$other'},
            rd: {$sum: '$rd'},
            total: {$sum: {$add: ['$rd', '$other', '$admin', '$qa', '$dev']}}
        }
   }
], function(err, data) {
    if (err) return next(err);
    res.json(data);
});

Sample of Data I am trying to aggregate:

  {
      "_id": "5812542902e5d2ab017fe918",
      "dev": 2,
      "qa": 1,
      "rd": 2,
      "other": 4,
      "admin": 0,
      "dateWorked": "2016-10-04T04:00:00.000Z",
      "userInfo": {
          "name": "test1"
      }
    },
    {
      "_id": "5812542902e5d2ab017fe919",
      "dev": 2,
      "qa": 1,
      "rd": 2,
      "other": 4,
      "admin": 0,
      "dateWorked": "2016-10-03T04:00:00.000Z",
      "userInfo": {
        "name": "test2"
      }
  },
Community
  • 1
  • 1
andre
  • 1,660
  • 3
  • 19
  • 31
  • Is you dateWorked value a string instead of a [mongo date](https://docs.mongodb.com/v3.2/reference/bson-types/#date)? – joao Oct 29 '16 at 22:25
  • According to MongoDB, weeks starts from Sunday, so you are getting correct behavior. See https://docs.mongodb.com/v3.2/reference/operator/aggregation/week/ – Saleem Oct 29 '16 at 23:45
  • Also your post title is not in sync with what you mentioned in body. – Saleem Oct 29 '16 at 23:47

0 Answers0