How to subtract two date time in mongodb

Question

I have used aggregate function.

db.checkins.aggregate([
       {$match: {checkinType: "Beacon",
               "associationIds.organizationId":"af39bc69-1938-4149",
               "checkinData.time": {"$gte": new Date("2018-01-18T18:30:00.000Z"), 
                                   "$lt": new Date("2018-01-19T18:30:00.000Z")}
                }
        },
       {"$sort":{"checkinData.time":-1}},
       {$group: {"_id":
                    {"orgId":"$asst.organizationId", "userId":"$asst.userId"},
                    "lastSeen":{"$first":"$checkinData.time"},
                   "firstSeen":{"$last":"$checkinData.time"},
               }
       }, 
      {"$project":{"_id":1,"lastSeen":1, "firstSeen":1, 
                  totalHourSpent:{$subtract: ["$lastSeen","$firstSeen"]}}}, 
  ])

When I performed this Query mongo return totalHourSpent in milisecond which is given below.

{
  "_id" : {
        "orgId" : "af39bc69-1938-4149-b9f7-f101fd9baf73",
        "userId" : "34adb4a0-0012-11e7-bf32-cf79d6b423e9"
  },
 "lastSeen" : ISODate("2018-01-19T18:49:52.242+05:30"),
 "firstSeen" : ISODate("2018-01-19T10:08:21.026+05:30"),
 "totalHourSpent" : NumberLong("31291216")
},
{
  "_id" : {
       "orgId" : "af39bc69-1938-4149-b9f7-f101fd9baf73",
       "userId" : "679416b0-3f88-11e7-8d27-77235eb1ba9b"
   },
   "lastSeen" : ISODate("2018-01-19T20:51:30.946+05:30"),
   "firstSeen" : ISODate("2018-01-19T11:07:44.256+05:30"),
   "totalHourSpent" : NumberLong("35026690")
 },

How to calculate totalHourSpent in hour. Thanks in advance.

Answer 1

group, count, avg

subtract value return in milliseconds

  const avgTime = await Post.aggregate<{
    count?: string;
    avg_time: string;
  }>([
    {
      $match: {
        finishedAt: {
          $exists: true,
        },
      },
    },

    {
      $group: {
        _id: null,
        count: {
          $sum: 1,
        },
        avg_time: {
          $avg: {
            // $subtract: [
            //   {
            //     $ifNull: ['$finishedAt', 0],
            //   },
            //   {
            //     $ifNull: ['$createdAt', 0],
            //   },
            // ],
            $subtract: ['$finishedAt', '$createdAt'],
            //$subtract: [{ $toDate: '$finishedAt' }, { $toDate: '$createdAt' }],
          },
        },
      },
    },
  ]);

for more info

https://database.guide/mongodb-subtract/#:~:text=In%20MongoDB%2C%20you%20can%20use,and%20return%20the%20resulting%20date

Answer 2

$subtract gives you the duration in millisecond. So we need to divide the duration with 3600000 for hour format.

The returned mills can be converted to hour by dividing by 3600000 (number of milliseconds in 1 hour):

totalHourSpent:{$divide : [{$subtract: ["$lastSeen","$firstSeen"]}, 3600000]}

ie

35026690÷3600000=9.72963611111111 hours