How to query objects with the longest time period?

user_11693096 0
Viewed 51

I'm looking for a query that would return the objects with the longest time period.

So the each entry in the collection has attributes of startDate and finishDate for example:

id: 1  startDate: 2019-01-22 23:23:47  finishDate: 2019-01-22 20:58:47 
id: 2  startDate: 2019-01-22 23:12:47  finishDate: 2019-01-22 11:58:47
id: 3  startDate: 2019-01-22 23:23:47  finishDate: 2019-01-22 13:58:47

How do I find 2 items with the longest time period difference between endDate and startDate?

mongodb date
1 Answers

Assuming your dates are Strings, and since they are quite nicely formatted, you can convert them to Date objects using Mongo 4.0's $toDate converter. (with earlier versions you can use $dateFromString)

Once you have dates, you can obtain their difference with $subtract which provides the number of milliseconds between the two dates.

We can thus add this field to each document with $addFields (or $set starting Mongo 4.2) and thus sort all documents by decreasing order on this new period field via the $sort operator.

Finally, to only keep the two documents with the longest periods, you can apply a $limit stage:

// { id: 1, startDate: "2019-01-22 20:58:47", finishDate: "2019-01-22 23:23:47" }
// { id: 2, startDate: "2019-01-22 11:58:47", finishDate: "2019-01-22 23:12:47" }
// { id: 3, startDate: "2019-01-22 13:58:47", finishDate: "2019-01-22 23:23:47" }
db.collection.aggregate([
  { $addFields: {
      period: { $subtract: [ { $toDate: "$finishDate" }, { $toDate: "$startDate" } ] }
  } },
  { $sort: { period: -1 } },
  { $limit: 2 }
])
// { id: 2, period: NumberLong(40440000), startDate: "2019-01-22 11:58:47", finishDate: "2019-01-22 23:12:47" }
// { id: 3, period: NumberLong(33900000), startDate: "2019-01-22 13:58:47", finishDate: "2019-01-22 23:23:47" }
user_92971440
Related