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

Question

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.

YuTing · Accepted Answer · 2022-04-02T09:43:42.313

1

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

edited Apr 02 '22 at 09:43

answered Mar 31 '22 at 05:24

YuTing

6,555
2
6
16

It's only printing the start and end date not even the difference. And my question was to calculate the difference of some of the object based on a specific field value. – unknown Mar 31 '22 at 08:02
@unknown It would be great if you can provide some valid testing data and expected result to make this question more clarify. – YuTing Mar 31 '22 at 08:05
I have updated the question please have a look – unknown Apr 01 '22 at 11:36
@unknown my second answer is what you need – YuTing Apr 02 '22 at 09:43
It's not working in the Studio 3T (previously Robo 3T) saying unrecognized expression '$datediff', any recommendations? – unknown Apr 03 '22 at 06:17
@unknown `$datediff` is New in version 5.0. Maybe you can only use subtract like [this](https://stackoverflow.com/questions/48369419/how-to-subtract-two-date-time-in-mongodb) – YuTing Apr 03 '22 at 14:16
Thanks it worked. Just a question when I am converting milliseconds to date using $toDate it's giving me year as 1970. => 1970-01-01 00:38:08.466Z. Any solution to this? – unknown Apr 03 '22 at 15:25

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

1 Answers1