CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
DanglingPointer
DanglingPointer

Reputation: 91

Getting first day of week from week number in mongodb

I have collection containing date field. I'm Grouping records by week and other related fields.

This is my aggregation query:

db.raw.aggregate([
    { "$match" : {
        "Timestamp":{
            "$gte": new Date("2012-05-30"), 
            "$lt": new Date("2014-07-31")
        }
    }},
    { "$group" : {
        "_id":{ 
            "ApplicationId": "$ApplicationId",
            "Country": "$Country",
            "week":{ "$week": "$Timestamp" }
        },
        "Date":{ "$first": "$Timestamp" },
        "Visits": { "$sum": 1 }
    }}
])

I want to Project : Visits and Start Date of week from week number.

Upvotes: 4

Views: 5147

Answers (5)

medihack
medihack

Reputation: 16627

For MongoDB >= v5.0 there is an even easier option now with the $dateTrunc operator, e.g.

$project: {
  weekStart: {
    $dateTrunc: {
      date: "$Timestamp",
      unit: "week",
      startOfWeek: "Monday",
    }
  },
}

Upvotes: 0

Hoan Ha
Hoan Ha

Reputation: 1

From mongo 3.6 https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromParts/

db.raw.aggregate([
    {
        "$match": {
            "Timestamp": {
                "$gte": new Date("2012-05-30"),
                "$lt": new Date("2014-07-31")
            }
        }
    },
    {
        "$group": {
            "_id": {
                "ApplicationId": "$ApplicationId",
                "Country": "$Country",
                "week": {
                    "$isoWeek": "$Timestamp"
                },
                                "year": {
                                    "$year": "$Timestamp"
                                }
            },
            "Visits": {
                "$sum": 1
            }
        }
    },
    {
        "$addFields": {
            "Date": {
                $dateFromParts: {
                    isoWeekYear: '$_id.year',
                    isoWeek: '$_id.week',
                    isoDayOfWeek: 1
                }
            }
        }
    }
])

Upvotes: 0

WatersLake
WatersLake

Reputation: 1282

With MongoDB 3.6

{ 
  '$project' : {
    'firstDateOfWeek': {
        '$dateFromString': {
            'dateString': {
                '$concat': [
                    {
                        '$toString': '$_id.year'
                    }, 
                    '-', 
                    {
                        '$toString': '$_id.week'
                    }
                ]
             },
             'format': "%G-%V"
         }
     }
  }
}

Upvotes: 3

Oleg Matei
Oleg Matei

Reputation: 906

For mongo >= v3.4, look at weekStart. The idea is to substruct milliseconds from given Timestamp

db.raw.aggregate([
         // stage 1
        { "$match" : {
            "Timestamp":{
                "$gte": ISODate("2012-05-30"), 
                "$lt": ISODate("2014-07-31")
            }
        }},

         // stage 2
        { "$project" : {

           ApplicationId: 1,
           Country: 1,
           week: {$isoWeek: "$Timestamp"},

           // [TRICK IS HERE] Timestamp - dayOfWeek * msInOneDay
           weekStart: { $dateToString: { format: "%Y-%m-%d", date: { // convert date
             $subtract: ["$Timestamp", {$multiply: [ {$subtract:[{$isoDayOfWeek: "$Timestamp"},1]}, 86400000]}] 
           }}},

         // stage 3
        { "$group" : {
        "_id":{ 
            "ApplicationId": "$ApplicationId",
            "Country": "$Country",
            "week": "$week"
        },
        "Date":{ "$first": "$weekStart" },
        "Visits": { "$sum": 1 }
        }}        
 ])

Upvotes: 7

Neil Lunn
Neil Lunn

Reputation: 151132

You seem to want a "date value" representing the date at the start of the week. Your best approach is "date math" with a little help from the aggregation operator $dayOfWeek:

db.raw.aggregate([
  { "$match" : {
    "Timestamp":{
      "$gte": new Date("2012-05-30"), 
      "$lt": new Date("2014-07-31")
    }
  }},
  { "$group" : {
    "_id":{ 
      "ApplicationId": "$ApplicationId",
      "Country": "$Country",
      "weekStart":{ 
        "$subtract": [
          { "$subtract": [
            { "$subtract": [ "$Timestamp", new Date("1970-01-01") ] },
            { "$cond": [
              { "$eq": [{ "$dayOfWeek": "$Timestamp" }, 1 ] },
              0,
              { "$multiply": [
                1000 * 60 * 60 * 24,
                { "$subtract": [{ "$dayOfWeek": "$Timestamp" }, 1 ] }
              ]}
            ]}
          ]},
          { "$mod": [
            { "$subtract": [
              { "$subtract": [ "$Timestamp", new Date("1970-01-01") ] },
              { "$cond": [
                { "$eq": [{ "$dayOfWeek": "$Timestamp" }, 1 ] },
                0,
                { "$multiply": [
                  1000 * 60 * 60 * 24,
                  { "$subtract": [{ "$dayOfWeek": "$Timestamp" }, 1 ] }
                ]}
              ]}
            ]},
            1000 * 60 * 60 * 24
          ]}
        ]
      }
    },
    "Date":{ "$first": "$Timestamp" },
    "Visits": { "$sum": 1 }
  }}
])

Or a little cleaner with $let from MongoDB 2.6 and upwards:

db.raw.aggregate([
  { "$match" : {
    "Timestamp":{
      "$gte": new Date("2012-05-30"), 
      "$lt": new Date("2014-07-31")
    }
  }},
  { "$group" : {
    "_id":{ 
      "ApplicationId": "$ApplicationId",
      "Country": "$Country",
      "weekStart":{ 
        "$let": {
          "vars": {
            "dayMillis": 1000 * 60 * 60 * 24,
            "beginWeek": {
              "$subtract": [
                { "$subtract": [ "$Timestamp", new Date("1970-01-01") ] },
                { "$cond": [
                  { "$eq": [{ "$dayOfWeek": "$Timestamp" }, 1 ] },
                  0,
                  { "$multiply": [
                    1000 * 60 * 60 * 24,
                    { "$subtract": [{ "$dayOfWeek": "$Timestamp" }, 1 ] }
                  ]}
                ]} 
              ]
            }
          },
          "in": {
            "$subtract": [
              "$$beginWeek",
              { "$mod": [ "$$beginWeek", "$$dayMillis" ]}
            ]
          }
        }
      }
    },
    "Date":{ "$first": "$Timestamp" },
    "Visits": { "$sum": 1 }
  }}
])

The resulting value in the "grouping" is the epoch milliseconds that represents the start of the day at the start of the week. The "start of the week" is generally considered to be "Sunday", so if you intend another day then you would need to adjust by the appropriate amount. The $add operator with the $dayMillis variable value can be used here to apply "Monday" for example.

It's not a date object, but something that you can easily feed to another method to construct a date object in post processing.

Also note that other things you are using such as $first usually require that the documents are sorted in a particular order, or generally by your "Timestamp" values. If those documents are not already ordered then you either $sort first or use an operator such as $min to get the first actual timestamp in the range.

Upvotes: 4

Related Questions