xiucai
xiucai

Reputation: 35

MongoDB query for time(%Y-%m-%d %H) less than the current time

  1. I need mongodb to get the current time
  2. I need to format the time (%Y-%m-%d %H)(String)

I am using the navicat for mongodb tool. But my sql can't get the result.

db.getCollection("test").find({
    hours:{
        $lte:{
            hourStr:{
                $dateToString: {
                    format: "%Y-%m-%d %H",
                    date: new ISODate()
                }
            }
        }
    }
})

null

Upvotes: 1

Views: 169

Answers (1)

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

The $dateToString is an expression. To evaluate any expression in find() clause, we have to use $expr.

The following query can get us the expected output:

db.test.find({
    $expr:{
        $lte:[
            "$hours",
            {
                $dateToString:{
                    "date":new ISODate(),
                    "format":"%Y-%m-%d %H"
                }
            }
        ]
    }
}).pretty()

Data set:

{ "_id" : ObjectId("5d67bf0e644d83f427bb4917"), "hours" : "2019-08-29 12" }
{ "_id" : ObjectId("5d67bfb1644d83f427bb4918"), "hours" : "2019-08-29 11" }
{ "_id" : ObjectId("5d67c0d1644d83f427bb4919"), "hours" : "2019-08-29 14" }

Current time: ISODate("2019-08-29T12:11:27.287Z")

Output:

{ "_id" : ObjectId("5d67bf0e644d83f427bb4917"), "hours" : "2019-08-29 12" }
{ "_id" : ObjectId("5d67bfb1644d83f427bb4918"), "hours" : "2019-08-29 11" }

Upvotes: 2

Related Questions