CODEWITHSUNDEEP
mongodb
Tony Mathew
Tony Mathew

Reputation: 910

Mongodb- Query documents between two timestamp

I have documents in the following structure: 

{
"escalation" : null,
"reclassified" : false,
"timestamp" : "1559164897437",
"endFlow" : "false",
"channel" : "web",
"intents" : null
},
{
"escalation" : null,
"reclassified" : false,
"timestamp" : "1565196671234",
"endFlow" : "true",
"channel" : "web",
"intents" : null
}

I would like to query documents like above between two timestamps, say "1559254897437" and "1563996671234".
Please note that I have the value of timestamp field stored as string, not integer.

What I have tried: 

db.getCollection('messagelogs').find({
    'timestamp':{
        $lte:1559164897437,
        $gte:1560351891811}
        })

Unfortunately, this is returning any documents.

What is the correct query to fetch documents between two timestamps?

Upvotes: 2

Views: 1105

Answers (2)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17935

Basically what you're trying there is something like AND operation on each document, saying you need a document having timestamp $lte a timestamp & at the same-time $gte a different timestamp, instead you need to do OR to get outer boundaries i.e; bring documents out if any one condition matches, Please do this :

db.getCollection('messagelogs').find({$or: [{'timestamp': {$lte:'1559164897437'}}, {'timestamp': {$gte:'1560351891811'}}] })

assuming you did a mistake, if you're in need of docs between particular times, you need to swap your timestamps :

db.getCollection('messagelogs').find({$and: [{'timestamp': {$lte:'1560351891811'}}, {'timestamp': {$gte:'1559164897437'}}] })

or simply :

db.getCollection('messagelogs').find({'timestamp': {$gte:'1559164897437', $lte: '1560351891811'}})

Upvotes: 2

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

Since the timestamp is present as String in DB, we need to query it in the string range and not numeric. The following query can get you the expected output:

db.getCollection('messagelogs').find(
{
    'timestamp':{
        $gte:'1559164897437',
        $lte:'1560351891811'
    }
})

Output:

{
    "_id" : ObjectId("5d4b1ccdcbfa696dcd9924a3"),
    "escalation" : null,
    "reclassified" : false,
    "timestamp" : "1559164897437",
    "endFlow" : "false",
    "channel" : "web",
    "intents" : null
}

Upvotes: 5

Related Questions