How to calculate timestamp difference in hours with MongoDB?

To calculate timestamp difference in hours in MongoDB, use the aggregate framework with $subtract and $divide operators. The key is subtracting timestamps (which returns milliseconds) and dividing by 3600000 to convert to hours.

Syntax

db.collection.aggregate([
    {
        $project: {
            DifferenceInHours: {
                $divide: [
                    { $subtract: ["$endTime", "$startTime"] },
                    3600000
                ]
            }
        }
    }
]);

Sample Data

db.timestampDifferenceDemo.insertMany([
    {
        "MovieBeginningTime": new ISODate("2019-05-12 10:20:30"),
        "MovieEndingTime": new ISODate("2019-05-12 12:30:20")
    },
    {
        "MovieBeginningTime": new ISODate("2019-05-12 04:00:00"),
        "MovieEndingTime": new ISODate("2019-05-12 07:10:00")
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5cd7ba1f6d78f205348bc644"),
        ObjectId("5cd7ba3b6d78f205348bc645")
    ]
}

View Sample Data

db.timestampDifferenceDemo.find().pretty();

{
    "_id": ObjectId("5cd7ba1f6d78f205348bc644"),
    "MovieBeginningTime": ISODate("2019-05-12T10:20:30Z"),
    "MovieEndingTime": ISODate("2019-05-12T12:30:20Z")
}
{
    "_id": ObjectId("5cd7ba3b6d78f205348bc645"),
    "MovieBeginningTime": ISODate("2019-05-12T04:00:00Z"),
    "MovieEndingTime": ISODate("2019-05-12T07:10:00Z")
}

Calculate Timestamp Difference in Hours

db.timestampDifferenceDemo.aggregate([
    {
        $project: {
            DifferenceInHours: {
                $divide: [
                    { $subtract: ["$MovieEndingTime", "$MovieBeginningTime"] },
                    3600000
                ]
            }
        }
    }
]);

{ "_id": ObjectId("5cd7ba1f6d78f205348bc644"), "DifferenceInHours": 2.1638888888888888 }
{ "_id": ObjectId("5cd7ba3b6d78f205348bc645"), "DifferenceInHours": 3.1666666666666665 }

How It Works

• $subtract calculates the difference between two timestamps in milliseconds
• $divide converts milliseconds to hours by dividing by 3600000 (1000ms × 60sec × 60min)
• The result shows decimal hours (e.g., 2.16 hours = 2 hours 10 minutes)

Conclusion

Use MongoDB's aggregation pipeline with $subtract and $divide to calculate timestamp differences in hours. Subtracting ISODate objects returns milliseconds, which you divide by 3600000 for the hour conversion.