I want to retrieve records from MongoDB collection based on Oracle like criteria such as "sysdate between publishFrom and publishTo" but I am not able to do it. Appreciate your help.
The data looks like below:
{
"_id" : ObjectId("5580fcdd9aaebd3e6591e65d"),
"category" : "green",
"noticeText" : "vacation notice",
"publishFrom" : "06/17/2015 10:21 AM",
"publishTo" : "08/19/2015 10:21 AM"
}
{
"_id" : ObjectId("558106a79aaebd55af91e66a"),
"category" : "red",
"noticeText" : "notice 1",
"publishFrom" : "06/22/2017 11:02 AM",
"publishTo" : "06/22/2017 11:02 AM"
}
The following query returns correct result:
db.notices.find({publishTo: {$gte: "08/19/2015 10:21 AM"}}).pretty()
{
"_id" : ObjectId("5580fcdd9aaebd3e6591e65d"),
"category" : "green",
"noticeText" : "vacation notice",
"publishFrom" : "06/17/2015 10:21 AM",
"publishTo" : "08/19/2015 10:21 AM"
}
but the following query returns all the records which is wrong
db.notices.find({publishFrom: {$gte: "06/17/2015 10:21 AM"}}).pretty()
I am struggling to write a Oracle like query to find notices where current time is between PublishFrom and publishTo.
Also, I tried using date variable like:
> var dt = new Date()
> dt
ISODate("2015-06-17T05:43:23.634Z")
But the following query does not return any result
db.notices.find({publishFrom: {$gte: dt}}).pretty()
Is it because difference in date storage formats?