How can I filter an ISODate field based only the in the time in MongoDB

Question

I have a collection with the following structure in MongoDB:

    {
      "Name": "Test",
      "StartDatetime": ISODate('2024-05-15T15:00:41.180+00:00'),
      "EndDatetime": ISODate('2024-05-15T15:30:12.120+00:00')
    }

I have many records in this collection with different datetimes, and I want to filter them based solely on the time, ignoring the date. For example, I want to retrieve all documents where the time is between 02:32:05 and 05:23:12.

I wrote this query:

db.collection.find({
  $expr: {
    $and: [
      {
        $or: [
          {
            $gte: [
              {
                $concat: [
                  { $substr: [{ $hour: "$StartDateTime" }, 0, 2] },
                  ":",
                  { $substr: [{ $minute: "$StartDateTime" }, 0, 2] },
                  ":",
                  { $substr: [{ $second: "$StartDateTime" }, 0, 2] }
                ]
              },
              "02:32:05"
            ]
          },
          {
            $and: [
              { $eq: [{ $hour: "$StartDateTime" }, 2] },
              { $gte: [{ $concat: [{ $substr: [{ $minute: "$StartDateTime" }, 0, 2] }, ":", { $substr: [{ $second: "$StartDateTime" }, 0, 2] }] }, "32:05"] }
            ]
          }
        ]
      },
      {
        $or: [
          {
            $lte: [
              {
                $concat: [
                  { $substr: [{ $hour: "$EndDateTime" }, 0, 2] },
                  ":",
                  { $substr: [{ $minute: "$EndDateTime" }, 0, 2] },
                  ":",
                  { $substr: [{ $second: "$EndDateTime" }, 0, 2] }
                ]
              },
              "05:23:12"
            ]
          },
          {
            $and: [
              { $eq: [{ $hour: "$EndDateTime" }, 5] },
              { $lte: [{ $concat: [{ $substr: [{ $minute: "$EndDateTime" }, 0, 2] }, ":", { $substr: [{ $second: "$EndDateTime" }, 0, 2] }] }, "23:12"] }
            ]
          }
        ]
      }
    ]
  }
})

However it is not working and I also believe there might be a better way to write this query. I have searched online, but I only found answers about how to create a query based solely on the date.

Answer 1

Currently, it is unclear about your date comparing logic as provided in your example. However, the idea is to convert the date fields into strings using $dateToString that match your input date format (i.e. %H:%m:%S). Then, perform strings comparison for the strings.

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              {
                "$dateToString": {
                  "date": "$StartDatetime",
                  "format": "%H:%m:%S"
                }
              },
              "02:32:05"// your start date input here
              
            ]
          },
          {
            "$lt": [
              {
                "$dateToString": {
                  "date": "$EndDatetime",
                  "format": "%H:%m:%S"
                }
              },
              "05:23:12"// your end date input here
              
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground