CODEWITHSUNDEEP
mongodbmongodb-query
Anil D
Anil D

Reputation: 2009

MongoDb search on date field

I have a Mongo collection with 'DateAdded' in ISODate format

{
"_id" : 4098199,
"DateAdded" : ISODate("2018-08-31T05:06:13.150Z")
}

so it is 1hour off then local datetime

LocalDatetime is 2018-08-31 06:06:13 +01:00
UTC is 2018-08-31T05:06:13.150Z

Using following query when i try to get records added after 2018-08-31 06:00:00 i m not getting this records as it is in UTC

 db.getCollection('User').find({"DateAdded":{$gte: new ISODate('2018-08-31T6:00:00Z')}})

How to convert UTC date while doing search on date field in MongoDB?

Upvotes: 1

Views: 2337

Answers (1)

Alex Blex
Alex Blex

Reputation: 37108

First of all ISODate('2018-08-31T6:00:00Z') has a typo - it should be 06:00:00Z. Without leading 0 ISODate helper returns '2018-08-31T00:00:00Z' 0am UTC and the query actually returns the document.

Secondly, there is nothing about local time. ISODate is in UTC, so ISODate("2018-08-31T05:06:13.150Z") is 5am UTC and ISODate("2018-08-31T06:06:13.150Z") is 6am UTC. 5 < 6, so the $gte condition doesn't meet and the document is not returned.

Lastly, if you want to use local time - use Date instead:

db.getCollection('User').find({"DateAdded":{$gte: new Date('2018-08-31T6:00:00')}})

Will return all documents created after 5am UTC, 6am local time. Note, there is no Z at the end, which specifies UTC timezone.

As a recommendation - don't mess up with local time. Convert it to UTC as soon as possible and always work with UTC.

Upvotes: 1

Related Questions