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.
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
0