Mongo query to find record between specific time-CodePudding

I have collections with DateTime field which have datetime value saved in UTC, with time.

I like to find all record between time 8 AM to 6 PM regardless date. I have tried following query:

{ $expr: { $gte: [ { $hour: "$DateTime" }, 8 ]} }

This query is working fine to get record after 8 AM, regardless date. I am unable to find query with $lte in same match. This query is not working:

{ $expr: { $gte: [ { $hour: "$DateTime" }, 8 ], $lte: [ { $hour: "$DateTime" }, 18 ]} }

Error: An object representing an expression must have exactly one field: { $gte: [ { $hour: "$DateTime" }, 8 ], $lte: [ { $hour: "$DateTime" }, 18 ] }

Any help would be appreciated. If this can be done in Mongo dotnet core then that would be great using lamda expression.

CodePudding user response：

One of the simple ways to perform time-only comparison will be convert to string using $dateToString and perform string comparison.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $gte: [
              {
                "$dateToString": {
                  "date": "$DateTime",
                  "format": "%H:%M:%S"
                }
              },
              "08:30:00"
            ]
          },
          {
            $lte: [
              {
                "$dateToString": {
                  "date": "$DateTime",
                  "format": "%H:%M:%S"
                }
              },
              "18:30:00"
            ]
          }
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.