CODEWITHSUNDEEP
mongodbdatemongoosemongodb-query
RagAnt
RagAnt

Reputation: 1014

Why MongoDB Date range query returning emtpy result when querying for same date and one day before?

I am having a mongodb collection which stores payments info of users who bought videos

This is the content :

{
    "_id" : ObjectId("5ea17279ca73ec19f84ac39a"),
    "createdAt" : ISODate("2020-04-23T10:48:25.920Z"), // 23 Apr 2020
    "updatedAt" : ISODate("2020-04-23T10:48:25.920Z"),
    "accountId" : "0000",
    "postId" : ObjectId("5e8da8533efaa66ce53bd3bb"),
    "paidBy" : ObjectId("5e92e9ffda29d826d46f7562"),
    "paidGet" : ObjectId("5e465194366a412b7107a1c8"),
    "chargeId" : "0520114401000000538",
    "status" : "Charging",
    "isDeleted" : false,
    "chargeAmount" : "12",
    "transferAmount" : "0",
    "postPrice" : "1.00",
    "__v" : 0
}
{
    "_id" : ObjectId("5ea173a9ca73ec19f84ac39c"),
    "createdAt" : ISODate("2020-04-23T10:53:29.957Z"), //23 Apr 2020
    "updatedAt" : ISODate("2020-04-23T10:53:29.957Z"),
    "accountId" : "0000",
    "postId" : ObjectId("5e8da8533efaa66ce53bd3bb"),
    "paidBy" : ObjectId("5ea1673bca73ec19f84ac392"),
    "paidGet" : ObjectId("5e465194366a412b7107a1c8"),
    "chargeId" : "0520114401000000552",
    "status" : "Charging",
    "isDeleted" : false,
    "chargeAmount" : "12",
    "transferAmount" : "0",
    "postPrice" : "1.00",
    "__v" : 0
}

My Issue :

I need to query the collection based on date range against the field createdAt :

I am using $gte and $lte for querying

Now I need to find payments happened on 23 Apr 2020

My Queries are : 

db.payment.find({"createdAt" : {"$gte" : ISODate("2020-04-22") , "$lte" : ISODate("2020-04-23")}}).pretty()

db.payment.find({"createdAt" : {"$gte" : ISODate("2020-04-23") , "$lte" : ISODate("2020-04-23")}}).pretty()

But this query not returning anything : That is , Date ranges 22 Apr 20 - 23 Apr 20 && 23 Apr 20 - 23 Apr 20 , returning empty result

But this query working that is if I add extra one day that is apr 24 [22 Apr 20 - 24 Apr 20]

db.payment.find({"createdAt" : {"$gte" : ISODate("2020-04-22") , "$lte" : ISODate("2020-04-24")}}).pretty()

And also this : [23 Apr 20 - 24 Apr 20]

db.payment.find({"createdAt" : {"$gte" : ISODate("2020-04-23") , "$lte" : ISODate("2020-04-24")}}).pretty()

Date D => date to be find = 23 Apr 2020:

So my issue is , if I gave a date range that starts one day before of that Date D and ends on same date D [22 Apr 20 - 23 Apr 20] ... No result :

And If I gave same date D in start and end ,[23 Apr 20 - 23 Apr 20]... no result returned

But if I gave one day extra in end date , result coming [22 Apr 20 - 24 Apr 20]

And also if the start date is Date D and end date is one day extra , result is coming : [23 Apr 20 - 24 Apr 20]

Why this weird matching ? Please help me

Upvotes: 1

Views: 195

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

If you observe it carefully ISODate("2020-04-23T10:48:25.920Z"), you would notice that it got created on 2020-04-23 at 10:48:25.920. So when you do ISODate("2020-04-23") it would result in ISODate("2020-04-23T00:00:00.000Z") that's the reason why you're not getting any results back. So your initial two queries which are failing has to be like this :

db.payment.find({"createdAt" : {"$gte" : ISODate("2020-04-22") , "$lt" : ISODate("2020-04-24")}}).pretty()

db.payment.find({"createdAt" : {"$gte" : ISODate("2020-04-23") , "$lt" : ISODate("2020-04-24")}}).pretty()

So when you do "$lt" : ISODate("2020-04-24") it would ensure that none of the documents from "2020-04-24" will be pulled out in result cause we're checking for createdAt < ISODate("2020-04-24T00:00:00.000Z").

Upvotes: 1

Related Questions