How to query objects with the longest time period in MongoDB?

To query objects with the longest time period in MongoDB, use the aggregation pipeline with $addFields to calculate the duration, $sort to order by longest period, and $limit to get the top result.

Syntax

db.collection.aggregate([
    { $addFields: {
        duration: { $subtract: [ { $toDate: "$endDate" }, { $toDate: "$startDate" } ] }
    } },
    { $sort: { duration: -1 } },
    { $limit: 1 }
]);

Create Sample Data

db.demo344.insertMany([
    { "startDate": "2020-02-24 10:50:00", "endDate": "2020-02-24 11:50:00" },
    { "startDate": "2020-02-24 08:00:00", "endDate": "2020-02-24 11:50:50" }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e53f52cf8647eb59e5620aa"),
        ObjectId("5e53f53df8647eb59e5620ab")
    ]
}

Display All Documents

db.demo344.find();

{ "_id": ObjectId("5e53f52cf8647eb59e5620aa"), "startDate": "2020-02-24 10:50:00", "endDate": "2020-02-24 11:50:00" }
{ "_id": ObjectId("5e53f53df8647eb59e5620ab"), "startDate": "2020-02-24 08:00:00", "endDate": "2020-02-24 11:50:50" }

Example: Query Object with Longest Time Period

db.demo344.aggregate([
    { $addFields: {
        longestTime: { $subtract: [ { $toDate: "$endDate" }, { $toDate: "$startDate" } ] }
    } },
    { $sort: { longestTime: -1 } },
    { $limit: 1 }
]);

{ "_id": ObjectId("5e53f53df8647eb59e5620ab"), "startDate": "2020-02-24 08:00:00", "endDate": "2020-02-24 11:50:50", "longestTime": NumberLong(13850000) }

How It Works

• $addFields calculates duration in milliseconds using $subtract
• $toDate converts string dates to Date objects for calculation
• $sort: { longestTime: -1 } orders by duration descending
• $limit: 1 returns only the document with the longest period

Conclusion

Use MongoDB's aggregation pipeline to calculate time differences and find documents with the longest duration. The $subtract operator with $toDate provides millisecond precision for accurate time period comparison.