CODEWITHSUNDEEP
mongodbmongodb-queryazure-cosmosdb-mongoapi
ravi
ravi

Reputation: 1

MongoDb query with "2019-07-31T00:00:00" date format

I have date field in the MongoDB collection in the below format:

{
     "firstEffectiveDate" : "2019-09-31T00:00:00".
     "lastEffectiveDate" : "2019-11-30T00:00:00".
}

Now I am trying to get the records which are currently active, means if the current date is between the firstEffectivedate and the lastEffectivedate, it should return all the records.

I first tried to get any record grater than today:

db.collection.find({firstEffectiveDate: {"$gte": new Date()}})

but this itself is not returning any documents, could someone please help. Thank you.

How to get records, between dates with above date format?

Upvotes: 0

Views: 1480

Answers (1)

kevinadi
kevinadi

Reputation: 13775

Since your date is recorded as string, you cannot use new Date() as parameter because then you'll be comparing string to date.

Luckily, you use the ISO standard date format, which means that it can be sorted, even as string.

For example, let's say I have this collection:

> db.test.find()
{ "_id": 0, "dt": "2019-08-20T00:00:00" }
{ "_id": 1, "dt": "2019-08-21T00:00:00" }
{ "_id": 2, "dt": "2019-08-22T00:00:00" }
{ "_id": 3, "dt": "2019-08-23T00:00:00" }
{ "_id": 4, "dt": "2019-08-24T00:00:00" }
{ "_id": 5, "dt": "2019-08-25T00:00:00" }

You can construct a query using the ISO date format in string, like:

> db.test.find({dt:{$gte:'2019-08-23T00:00:00'}})
{ "_id": 3, "dt": "2019-08-23T00:00:00" }
{ "_id": 4, "dt": "2019-08-24T00:00:00" }
{ "_id": 5, "dt": "2019-08-25T00:00:00" }

Note that the query correctly selected all dates >= 2019-08-23T00:00:00.

This will also work if today's date is 2019-08-22:

> db.test.find({dt:{$gte:(new Date()).toISOString()}})
{ "_id": 3, "dt": "2019-08-23T00:00:00" }
{ "_id": 4, "dt": "2019-08-24T00:00:00" }
{ "_id": 5, "dt": "2019-08-25T00:00:00" }

The best solution, actually, is to refactor your data to use proper ISODate() datatype instead of string. This way, you can leverage advanced date calculations methods provided by the Date Expression Operators in aggregation.

Upvotes: 1

Related Questions