findOne query compare date in utc

Question

I am using UTC date format in my application and storing the data as shown below

{ 
"_id" : ObjectId("58ef69c221f24305c0c7123b"), 
"departmentId" : "58db361424f6bc2d3840f38b", 
"departmentOfficerId" : "58e21f0a7fa219021cd351ca", 
"departmentOfficerCurrentMonth" : NumberInt(1), 
"calenderYear": 2017 
"createdAt" : "Thu, 13 Apr 2017 12:06:26 GMT", 
"updatedAt" : "Thu, 13 Apr 2017 12:06:26 GMT", 
}

It should not give me any data but it gives me whole data.

and I am using the following mongodb query

db.departmentOfficerMonthlyScores.findOne(
{ 
 departmentOfficerId: "58e21f0a7fa219021cd351ca",
 departmentOfficerCurrentMonth: 1,
 calenderYear: 2017, 
 updatedAt: { "$gte" : "Fri, 14 Apr 2017 06:33:10 GMT" } 
}
)

but when I am using this query where in the date provided for comparing is on the same day

db.departmentOfficerMonthlyScores.findOne(
{ 
 departmentOfficerId: "58e21f0a7fa219021cd351ca",
 departmentOfficerCurrentMonth: 1,
 calenderYear: 2017, 
 updatedAt: { "$gte" : "Thu, 13 Apr 2017 13:16:20 GMT" } 
}
)

It is giving me correct result i.e null. How to handle this

score 0 · Answer 1 · edited May 23 '17 at 12:25

Recently started learning mongo, node and mongoose but it looks like your code is doing string comparison.

I would suggest using Date data for your date field (if it's not like that in your schema) which should make date comparison work.

Also when you compare with $gte in your filter, parse that string date as a date object like

new Date('December 17, 1995 03:24:00')

Also doing a search, I found this post https://stackoverflow.com/a/20911237/2928459 which shows how to convert your stored data type while searching. I am assuming, if you convert your string data to Date type and do a comparison with a JavaScript Date object, it should work out.

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

you can use date by fetching current date by

1> new Date()

OR

2> new Date

to get current Date object so that you can compare easily with date stored in MongoDB database .

Further if you want more modifications in date object then use moment Library.It does fantastic job for Date.

Thanks

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 14 '17 at 08:02

hardy

880
2
13
30

score 0 · Answer 3 · answered Apr 14 '17 at 10:11

When you query with date you should use date inside new Date()

Example:

var queryDate = new Date('2017-04-14'); //This can be any valid format so create you date you want to query and pass it inside.

db.departmentOfficerMonthlyScores.findOne({ 
   departmentOfficerId: "58e21f0a7fa219021cd351ca",
   departmentOfficerCurrentMonth: 1,
   calenderYear: 2017, 
   updatedAt: { "$gte" : queryDate } 
})

This way it will work properly.

score 0 · Answer 4 · answered Apr 14 '17 at 12:28

If you want to operate value as a date, you should store a date, but not a string that represents the date. Also in a query you should use a date object, but not a string that represents the date.

Look at example (mongodb shell):

db.test_date.insert({date: Date()})
WriteResult({ "nInserted" : 1 })

db.test_date.insert({date: Date()})
WriteResult({ "nInserted" : 1 })

db.test_date.insert({date: Date()})
WriteResult({ "nInserted" : 1 })

db.test_date.find()
{ "_id" : ObjectId("58f0bc95ad51c90cceb8e092"), "date" : "Fri Apr 14 2017 15:12:05 GMT+0300 (MSK)" }
{ "_id" : ObjectId("58f0bc97ad51c90cceb8e093"), "date" : "Fri Apr 14 2017 15:12:07 GMT+0300 (MSK)" }
{ "_id" : ObjectId("58f0bc97ad51c90cceb8e094"), "date" : "Fri Apr 14 2017 15:12:07 GMT+0300 (MSK)" }

typeof db.test_date.findOne().date
string

db.test_date.find({date: {$gt: "A"}})
{ "_id" : ObjectId("58f0bc95ad51c90cceb8e092"), "date" : "Fri Apr 14 2017 15:12:05 GMT+0300 (MSK)" }
{ "_id" : ObjectId("58f0bc97ad51c90cceb8e093"), "date" : "Fri Apr 14 2017 15:12:07 GMT+0300 (MSK)" }
{ "_id" : ObjectId("58f0bc97ad51c90cceb8e094"), "date" : "Fri Apr 14 2017 15:12:07 GMT+0300 (MSK)" }

db.test_date.find({date: {$lt: "A"}})
"there are no documents to this condition"

db.test_date.remove({})
WriteResult({ "nRemoved" : 3 })

db.test_date.insert({date: new Date()})
WriteResult({ "nInserted" : 1 })

db.test_date.insert({date: new Date()})
WriteResult({ "nInserted" : 1 })

db.test_date.find()
{ "_id" : ObjectId("58f0bd9cad51c90cceb8e095"), "date" : ISODate("2017-04-14T12:16:28.296Z") }
{ "_id" : ObjectId("58f0bd9dad51c90cceb8e096"), "date" : ISODate("2017-04-14T12:16:29.456Z") }

typeof db.test_date.findOne().date
object

db.test_date.find({date: {$gt: new Date('2017-01-01')}})
{ "_id" : ObjectId("58f0bd9cad51c90cceb8e095"), "date" : ISODate("2017-04-14T12:16:28.296Z") }
{ "_id" : ObjectId("58f0bd9dad51c90cceb8e096"), "date" : ISODate("2017-04-14T12:16:29.456Z") }

Date()
Fri Apr 14 2017 15:18:39 GMT+0300 (MSK)
typeof Date()
string
typeof new Date()
object

findOne query compare date in utc

4 Answers4