Get all objects for a week number

Question

I need to get a list of week ranges for all records in my MongoDB. When I click on a week range, it will display only the records for that week range. Clicking on the week range sends the ID of the week (lets say 42, ie the 42nd week out of year 2015), it should get those results.

Question: How can I query for a set of records given a week number and year? This should work, right?

SCHEMA:

var orderSchema = mongoose.Schema({
        date: Date, //ISO date
        request: {
            headers : {
            ...

First: Get all week IDs for all Objects:

    var query = Order.aggregate(
        [
            {
                $project:
                {
                    week:
                    {
                        $week: '$date'
                    }
                }
            },
            {
                $group:
                {
                    _id: null,
                    distinctDate:
                    {
                        $addToSet:
                        {
                            week: '$week'
                        }
                    }
                }
            }
        ]
    );

Result:

distinctDate: Array[35]
    0: Object
       week: 40
    1: Object
       week: 37
       ...

Convert to week ranges using MomentJS and display:

    data.forEach(function(v, k) {
        $scope.weekRanges.push(getWeekRange(v.week));
    });

    function getWeekRange(weekNum) {

        var monday = moment().day("Monday").isoWeek(weekNum).format('MM-DD-YYYY');
        var sunday = moment().day("Sunday").isoWeek(weekNum).format('MM-DD-YYYY');
        ...

Output:

Week 
10-12-2015 to 10-18-2015 //week ID 42
10-05-2015 to 10-11-2015 //week ID 41
09-28-2015 to 10-04-2015 ...
...

Second: Click on week range and get Objects Per Week ID:

var year = 2015;
var weekID = weekParamID; //42

    if (!Order) {
        Order = mongoose.model('Order', orderSchema());
    }

    var query = Order.aggregate(
        {
            $project:
            {
                cust_ID : '$request.headers.custID',
                cost : '$response.body.pricing.cost',
                year :
                {
                    $year:  '$date'
                },
                month :
                {
                    $month: '$date'
                },
                week:
                {
                    $week: '$date'
                },
                day:
                {
                    $dayOfMonth: '$date'
                }
            }
        },
        {
            $match:
            {
                year : year, //2015
                week : weekID //42
            }
        }
    );

And if I click on Week Range 10-12-2015 to 10-18-2015 (week ID 42), I get results with dates outside of the range (10-19-2015):

10-19-2015      Order info  
10-18-2015      Order info
10-19-2015      Order info

Using MongoDB command line:

db.mycollection.aggregate({ $project: { week: { $week: '$date' }, day: { $dayOfMonth: '$date' } }  }, { $match: { week: 42 } }

Results:

{ "_id" : "1bd482f6759b", "week" : 42, "day" : 19 } //shouldn't exceed week range
{ "_id" : "b3d38759", "week" : 42, "day" : 19 }

EDIT: Update

So there is a discrepancy with MongoDB ISO weeks (starts on Sunday) and Moment JS ISO (starts on Monday).

This SO post suggests subtracting the dates from the query so the Mongo date starts on Monday:

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

I implemented this with my query, but now it's not returning two fields that I need:

            cust_ID : '$request.headers.custID',
            cost : '$response.body.pricing.cost'

Query:

 db.mycollection.aggregate(
        {
            $project:
            {
                cust_ID : '$request.headers.custID',
                cost : '$response.body.pricing.cost',
                week:
                {
                    $week: ['$date']
                },
                dayOfWeek:
                {
                    $dayOfWeek: ['$date']
                }
            }
        },
        {
            $project:
            {
                week: {
                    $cond: [
                        {
                            $eq: [
                                "$dayOfWeek", 1
                            ]
                        },
                        {
                            $subtract: [
                                "$week", 1
                            ]
                        }, '$week'
                    ]
                }
            }
        },
        {
            $match:
            {
                week : 42
            }
        }
    );

Results:

{ "_id" : "387e2", "week" : 42 }
{ "_id" : "ef269f6341", "week" : 42 }
{ "_id" : "17482f6759b", "week" : 42 }
{ "_id" : "7123d38759", "week" : 42 }
{ "_id" : "ff89b1fb", "week" : 42 }

It's not returning the fieldsets I specified in $project

You need to include them again in second project. E.g. cost : '$cost' — sheilak, Oct 20 '15 at 06:13
@sheilak, thanks. So, the mongodb $cond fix doesn't seem to work. I am returning $dayOfMonth to show which day of the month the order obj was placed, but its still showing days (ie 10/19/2015 or even 10/20/2015) which are outside the range of 10/12/2015 - 10/18/2015 — user3871, Oct 20 '15 at 06:24
Updated my answer. The answer you linked to converts to a Monday-starting week, but not to the ISO standard week. The ISO standard week differs from MongoDB in how it defines week 1 at the start of the year. Sorry I don't have a solution but hopefully a couple of pointers to get closer — sheilak, Oct 20 '15 at 07:29

sheilak · Accepted Answer · 2015-10-20T07:55:25.150

The MongoDB $week operator considers weeks to begin on Sunday, see docs:

Weeks begin on Sundays, and week 1 begins with the first Sunday of the year... This behavior is the same as the “%U” operator to the strftime standard library function.

Moment.JS's isoWeekday() uses the ISO week which considers weeks to begin on Monday. It also differs in that it considers the week 1 to be the first week with a Thursday in it.

This discrepancy could explain the behaviour you are seeing.

E.g. if I save this doc in MongoDB, which is a Monday:

db.test.save({ "date" : new ISODate("2015-10-19T10:10:10Z")  })

then run your aggregation query above, I get week 42.

But then if I run the following:

console.log(moment().day("Monday").isoWeek(42))

I get the below date which is not the one I originally saved in MongoDB, even though it is Monday of the week MongoDB reported.

Mon Oct 12 2015

How to fix it I guess depends on which definition of week you need.

If you are happy with the MongoDB $week definition, it's probably easy to find/write an alternative implementation to convert the week number to the corresponding date. Here is one library that adds strftime support to Moment.js:

https://github.com/benjaminoakes/moment-strftime

If you want to use the ISO format, it's more complicated. As per your edit above you'll need to account for the week start difference. But you'll also need to account for the week number at start of year difference. This difference means that the strftime week number can have a week 0 while ISO always starts on week 1. For 2015 it looks like you need to add 1 week on to the strftime week to get the ISO week, as well as accounting for the week start day, but that won't be reliable in general.

I've tried implementing a change on the Mongo side. Please see above edit — user3871, Oct 19 '15 at 22:22

score 0 · Answer 2 · answered Feb 22 '17 at 16:16

Starting from MongoDB version 3.4 you can use the $isoWeek aggregation operator.

Returns the week number in ISO 8601 format, ranging from 1 to 53. Week numbers start at 1 with the week (Monday through Sunday) that contains the year’s first Thursday.

You can find more infos on this in the MongoDB docs.

Get all objects for a week number

2 Answers2