Retrieve records based on Oracle like criteria "sysdate between PublishFrom and PublishTo" in mongodb

102 Views Asked by At

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?

0

There are 0 best solutions below