databasemongodbmongodb-queryaggregation-frameworkrobo3t

How do I find date difference based on a field value in MongoDB?


I want to find the date difference in a collection but for the objects which matches a given condition. Any clue on how to achieve this.

Currently, I am using a query:

db.sales.aggregate([ {
    $project: {
       item: 1,
       dateDifference: {
          $subtract: [ "$enddate", "$startdate" ]
       }
    }
} ])

but this will return the date difference for all of my objects in the collection.

I want to have something like say I have a items field and there are multiple items say Car, Bike, Cycle etc. Now i only want the date difference based upon the item value.

say we have 4 sessions in a collection with properties like:

[
  {
    _id: "112233",
    item: "CAR",
    startdate: ISODate("2022-03-16T07:38:08.466Z"),
    enddate: ISODate("2022-03-16T08:38:08.466Z")
  },
  {
    _id: "11222333",
    item: "BIKE",
    startdate: ISODate("2022-02-16T07:38:08.466Z"),
    enddate: ISODate("2022-02-14T08:38:08.466Z")
  },
  {
    _id: "1122333243",
    item: "CAR",
    startdate: ISODate("2022-01-16T07:38:08.466Z"),
    enddate: ISODate("2022-02-16T01:38:08.466Z")
  },
  {
    _id: "12312233",
    item: "BUS",
    startdate: ISODate("2021-03-16T07:38:08.466Z"),
    enddate: ISODate("2021-03-16T08:38:08.466Z")
  }
]

Now i want to find the difference of startdate and enddate say for CAR only.


Solution

  • Use $expr and $dateDiff

    db.collection.aggregate([
      {
        $match: {
          $expr: {
            $gt: [
              {
                $dateDiff: {
                  startDate: "$startdate",
                  endDate: "$enddate",
                  unit: "minute"
                }
              },
              20
            ]
          }
        }
      }
    ])
    

    mongoplayground


    db.collection.aggregate([
      {
        $match: {
          item: "CAR"
        }
      },
      {
        $set: {
          diff: {
            $dateDiff: {
              startDate: "$startdate",
              endDate: "$enddate",
              unit: "day"
            }
          }
        }
      }
    ])
    

    mongoplayground