CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
gangatharan
gangatharan

Reputation: 791

In Mongo, How to write search query to search document based on time, on Date object.?

We have Collection named Incident. In which we have one field StartTime(Date object type). Every day, whenever incident condition is met then new Document entry will be created and inserted into the collection. We have to get all the incident which, fall between 10PM to 6AM. (i.e from midnight to early morning).

But i face problem on how to write query for this use case. Since we have date object, I can able to write query to search document between two Dates. How to write search query for search based on time, on Date object. Sample Data:

"StartTime" : ISODate("2015-10-16T18:15:14.211Z")

Upvotes: 0

Views: 113

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151122

It's just not a good idea. But basically you apply the date aggregation operators:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$or": [
          { "$gte": [{ "$hour": "$StartTime" }, 22] },
          { "$lt": [{ "$hour": "$StartTime" }, 6 ] }
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Using $redact that will only return or $$KEEP the documents that meet both conditions for the $hour extracted from the Date, and $$PRUNE or "remove" from results those that do not.

A bit shorter with MongoDB 3.6 and onwards, but really no different:

db.collection.find({
  "$expr": {
    "$or": [
      { "$gte": [{ "$hour": "$StartTime" }, 22] },
      { "$lt": [{ "$hour": "$StartTime" }, 6 ] }
    ]
  }
})

Overall, not a good idea because the statement needs to scan the whole collection and calculate that logical condition.

A better way is to actually "store" the "time" as a separate field:

var ops = [];

db.collection.find().forEach(doc => {
  // Get milliseconds from start of day
  let timeMillis = doc.StartTime.valueOf() % (1000 * 60 * 60 * 24);

  ops.push({
    "updateOne": {
      "filter": { "_id": doc._id },
      "update": { "$set": { timeMillis } }
    }
  });

  if ( ops.length > 1000 )  {
    db.collection.bulkWrite(ops);
    ops = [];
  }
})

if ( ops.length > 0 )  {
  db.collection.bulkWrite(ops);
  ops = [];
}

Then you can simply query with something like:

var start = 22 * ( 1000 * 60 * 60 ),   // 10PM
    end = 6 * ( 1000 * 60 * 60 );      // 6AM

db.collection.find({ 
  "$or": [
    { "timeMillis": { "$gte": start } },
    { "timeMillis": { "$lt": end } }
  ]
);

And that field can actually be indexed and so quickly and efficiently return results.

Upvotes: 1

Related Questions