CODEWITHSUNDEEP
mongodbdate-formatmongodb-aggregation
Ayyappa A
Ayyappa A

Reputation: 667

Mongodb WeekofMonth?

I am stuck with achieving weekofMonth instead of WeekofYear. Can somebody guide me on how to get this right?

db.activity.aggregate([
    {
        $group:{
            _id: {
                week: { $week: "$createdAt" }, 
                month: { $month: "$createdAt" },      
                year: { $year: "$createdAt" } 
            },
            count: { $sum: 1 }
        }
     },
     { $match : { "_id.year" : 2016, "_id.month" : 5 } }
])

Output 

/* 1 */
{
    "_id" : {
        "week" : 19,
        "month" : 5,
        "year" : 2016
    },
    "count" : 133.0
}

/* 2 */
{
    "_id" : {
        "week" : 18,
        "month" : 5,
        "year" : 2016
    },
    "count" : 1.0
}

In the above shown data, it is actually not displaying weekofMonth. How can I get this given week 18 is the first week of Month?

Upvotes: 5

Views: 1863

Answers (1)

joao
joao

Reputation: 4117

The $week operator gives you the week of year as described in the docs.

The week of month can be calculated by getting the day of month and dividing by 7.

db.activity.aggregate([
    {$project: {
        "year": {$year: "$createdAt"},
        "month": {$month: "$createdAt"},
        "weekOfMonth": {$floor: {$divide: [{$dayOfMonth: "$createdAt"}, 7]}}
    }},
    {$group: {
        "_id": {"year": "$year", "month": "$month", "weekOfMonth": "$weekOfMonth"},
        count: { $sum: 1 }
    }},
    {$match : { "_id.year" : 2016, "_id.month" : 5}}
])

Note that the week of month here is 0 based. If you want it to start at 1 just $add 1. Also, the $floor operator is new in version 3.2.

Edit

You can simulate the floor using $mod (which exists in version 3.0)

"weekOfMonth": {$subtract: [{$divide: [{$dayOfMonth: "$createdAt"}, 7]}, {$mod: [{$divide: [{$dayOfMonth: "$createdAt"}, 7]}, 1]}]},

Upvotes: 5

Related Questions