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.

Street

refer here to view all date functions. – Sharika

Possible duplicate of Rounding to 2 decimal places using MongoDB aggregation framework – Mulhouse

Answer 1

$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

Catalyze

How to get value after two decimal number. Thanks! – Street

If you care about precision, you might need to use extended bson types for example $numberDecimal – Catalyze

I wanna get like 9.729 hours – Street

From Mongdb 4.2 onwards can use $trunc operator, syntax { $trunc : [ <number>, <place> ] } with aggregation, see the link #47038944 – Monogenetic

Answer 2

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

Penance