Find closest date in one query

Question

I'm currently trying to figure out a way to find the closest date of a entry in mongoDB to the on i'm looking for.

Currently i solved the problem by using 2 queries. One using $gte and limit(1) to look for the next larger date and then $lte - limit(1) to see if there is a closer on that might be lower.

I was wondering, if there might be a way to find the closest date in just one query, but was not able to find anything on that matter.

Hope you can help me with this, or at least tell me for sure that this is the only way to do so.

db.collection.find({"time":{$gte: isoDate}}).sort({"time":1}).limit(1)
db.collection.find({"time":{$lte: isoDate}}).sort({"time":-1}).limit(1)

But I am looking for a way to do this in one query so i dont have to subtract the results to find the closest one.

Answer 1

I solved a similar problem using an aggregation.

Sample data:

{ 
  "_id" : ObjectId("5e365a1655c3f0bea76632a0"), 
  "time" : ISODate("2020-02-01T00:00:00Z"), 
  "description" : "record 1" 
}
{ 
  "_id" : ObjectId("5e365a1655c3f0bea76632a1"), 
  "time" : ISODate("2020-02-01T00:05:00Z"), 
  "description" : "record 2"
}
{
  "_id" : ObjectId("5e365a1655c3f0bea76632a2"), 
  "time" : ISODate("2020-02-01T00:10:00Z"), 
  "description" : "record 3"
}
{ 
  "_id" : ObjectId("5e365a1655c3f0bea76632a3"), 
  "time" : ISODate("2020-02-01T00:15:00Z"), 
  "description" : "record 4"
}
{ 
  "_id" : ObjectId("5e365a1655c3f0bea76632a4"), 
  "time" : ISODate("2020-02-01T00:20:00Z"), 
  "description" : "record 5"
}
{ 
  "_id" : ObjectId("5e365a1655c3f0bea76632a5"), 
  "time" : ISODate("2020-02-01T00:25:00Z"), 
  "description" : "record 6"
}

And I'm looking for the record nearest to ISODate('2020-02-01T00:18:00.000Z').

db.test_collection.aggregate([
{
    $match: 
        {
            time: 
                {
                    $gte: ISODate('2020-02-01T00:13:00.000Z'), 
                    $lte: ISODate('2020-02-01T00:23:00.000Z')
                }
        }
},
{
    $project:
        {
            time: 1,
            description: 1,
            time_dist: {$abs: [{$subtract: ["$time", ISODate('2020-02-01T00:18:00.000Z')]}]}}
},
{
    $sort: {time_dist: 1}
},
{
    $limit: 1
}])

The $match stage sets up a "time window". I used 5 minutes for this example.

The $project stage adds a time distance field. This is the time in milliseconds each record is from the query time of ISODate('2020-02-01T00:18:00.000Z').

Then I sorted on the time_dist field and limit the results to 1 to return the record with time closest to ISODate('2020-02-01T00:18:00.000Z').

The result of the aggregation:

{ 
  "_id" : ObjectId("5e365a1655c3f0bea76632a4"), 
  "time" : ISODate("2020-02-01T00:20:00Z"), 
  "description" : "record 5", 
  "time_dist" : NumberLong(120000)
}

Answer 2

check this one

db.collection.find({"time":{$gte: isoDate,$lt: isoDate}}).sort({"time":1}).limit(1)

Please use the same format what mongodb support like following

ISODate("2015-10-26T00:00:00.000Z")