Question
How can I filter an ISODate field based only the in the time in MongoDB
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.