Question 116070 in Mongodb Question posted in
The official documentation can be found here.

How to identify start date and end date using MongoDB aggregation pipeline?

Below is array of documents with collection_date. For a given collection date, I wanted to identify start date and end date using MongoDB aggregation pipeline.

[
    {
        "collection_date": 2022-12-01,
        "created_timestamp": 1668177586955,
        "created_by": "SYSTEM",
        "updated_timestamp": 1668177586955,
        "updated_by": "SYSTEM"
    }
]

Let’s say in the above array collection of documents I have a collection date with 2022-12-01. I am expecting to calculate start date i.e. 2022-12-28 and end date 2022-12-04 for the given collection date using MongoDB aggregation pipeline.

Below is my aggregation pipeline script:

db.getCollection("price").aggregate(
    [
        {
            "$addFields" : {
                "convertedDate" : {
                    "$toString" : {
                        "$toDate" : "$collection_date"
                    }
                }
            }
        }, 
        {
            "$set" : {
                "convertedDate" : {
                    "$substr" : [
                        "$convertedDate",
                        0.0,
                        10.0
                    ]
                }
            }
        },
    ], 
    {
        "allowDiskUse" : false
    }
);

My expectation is for a given collection date, I want to identify the start date of that week and end date of that week. In this case, for year 2022, given collection date is 12-01-2022 for which it’s start date of the week is 11-28-2022 and end date of the week is 12-04-2022

Expected output:

[
        {
          "convertedDate" : "2022-12-01"        
          "startDate" : "2022-11-28",
          "endDate" : "2022-12-04"
        }
]
Tags:

2

Answers


  1. 0

    First result from a google search for "mongodb aggregation pipeline date range" is https://www.mongodb.com/community/forums/t/how-to-search-date-range-in-aggregate/153870

    Login or Signup to reply.
  2. 0

    Use $isoDateOfWeek to identify the day of week and use that as offset with $dateAdd and $dateSubtract to compute the start and end of the week.

    db.collection.aggregate([
  {
    "$addFields": {
      "convertedDate": {
        "$toDate": "$collection_date"
      }
    }
  },
  {
    "$addFields": {
      "t": {
        "$isoDayOfWeek": "$convertedDate"
      }
    }
  },
  {
    "$project": {
      "convertedDate": 1,
      "startDate": {
        "$dateSubtract": {
          "startDate": "$convertedDate",
          "unit": "day",
          "amount": "$t"
        }
      },
      "endDate": {
        "$dateAdd": {
          "startDate": "$convertedDate",
          "unit": "day",
          "amount": {
            $subtract: [
              6,
              "$t"
            ]
          }
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search