How to filter/find records in MongoDB based on Date

Question

I have a collection in MongoDB database that has some data and would like to filter and find data based on date (ignoring time).

Sample Data

{
  "_id" : ObjectId("563a38173c2ab9248c02d89e"),
  "jobId" : "oAEKMcCIJRIAAAFQbrAR6NDd",
  "jobName" : "CheckSessions",
  "jobDesc" : "Checks and deletes expired session data",
  "jobType" : "Job",
  "startTime" : "2015-11-04 00:00:01",
  "endTime" : "2015-11-04 00:00:01",
  "domainName" : "root",
  "recurrencePeriod" : "60",
  "recurrencePeriodDesc" : "HOURS(1)"
}
{
  "_id" : ObjectId("563a38173c2ab9248c02d89f"),
  "jobId" : "C6wKMcCIJXoAAAFQm78R6NCm",
  "jobName" : "CheckSessions",
  "jobDesc" : "Checks and deletes expired session data",
  "jobType" : "Job",
  "startTime" : "2015-11-03 23:00:00",
  "endTime" : "2015-11-03 23:00:01",
  "domainName" : "root",
  "recurrencePeriod" : "60",
  "recurrencePeriodDesc" : "HOURS(1)"
}

I used the following command to fetch data greater than a specific date but it did not give me any results. Both startTime and endTime are in EST format. Not sure what am I missing. Please guide.

db.jobs.find({"startTime" : { $gt : new Date("2015-11-03")}});

Answer 1

Answer: You can query simply by string comparison.

First, ensure index on startTime column

db.test.ensureIndex({startTime:1})

Following command finds you second object in the example with _id:ObjectId("563a38173c2ab9248c02d89f")

db.test.find({
    startTime: {
        $gte: '2015-11-03 00:00:00',
        $lt:  '2015-11-04 00:00:00'
    }
}).pretty()

By running explain() on our query, we can see the index is in fact in use:

db.test.find({startTime:{$gte:'2015-11-03 00:00:00', $lt:'2015-11-04 00:00:00'}}).explain()

{
    "cursor" : "BtreeCursor startTime_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "startTime" : [
            [
                "2015-11-03 00:00:00",
                "2015-11-04 00:00:00"
            ]
        ]
    },
    "server" : "Jaans-MBP.home:27017"
}

My recommendation still is to follow best practices and to store your date fields in proper date format, e.g.

"startTime" : ISODate("2016-05-02T00:00:02Z")

in MongoDB shell you'd simply pass

> db.test.insert({startTime:new Date()})
> db.test.find()
// results
{ "_id" : ObjectId("563a49d63b1f1b7df0ebc4f5"), "startTime" : ISODate("2015-11-04T18:09:26.613Z") }