Reputation: 123
I am new to MongoDb and would appreciate some help with this query. I wrote the following aggregation pipeline. I wrote a query which returns last 24 hours records after that I am getting (00:01) hour to (05:00) hour records. With this how can I get records between (20:00) hour and (23:59) hours. Anyone, please suggest me.
db.collection.aggregate([
{$match: {createDate: {$gte: new Date(new Date(ISODate().getTime() - 1000*60*60*24))}}},
{
$project: {
hour: { $hour: "$createDate" },
minutes: { $minute: "$createDate" },
}
},
{
$match: {
$and: [{"hour": { "$gte" : 0}},{"minutes":{ "$gte" : 1}}],
$and: [{"hour": { "$lte" : 5}}],
}
},
])
Output:
{
"hour" : 0
"minutes" : 1
}
/* 2 */
{
"hour" : 4
"minutes" : 2
}
Expected output:
{
"hour" : 0
"minutes" : 1
}
/* 2 */
{
"hour" : 4
"minutes" : 2
},
{
"hour" : 4
"minutes" : 59
}
and
{
"hour" : 20
"minutes" : 1
}
/* 2 */
{
"hour" : 22
"minutes" : 2
},
{
"hour" : 23
"minutes" : 59
}
Upvotes: 0
Views: 101
Reputation: 61
Expanding on JohnnyHK's comment, this $match
should work for 20:00 - 23:59.
// 20:00 - 23:59
{
$match: {
hour: { $gte: 20, $lt: 24 }
}
}
If you want to get back both at the same time, you can use $or
to include all of the criteria.
{
$match: {
$or: [
< 0-5 hour criteria >,
< 20-24 hour criteria >
]
}
}
// 00:00 - 04:59
{
$match: {
hour: { $gte: 0, $lt: 5 }
}
}
// 00:01 - 04:59
{
$match: {
$or: [
{ hour: 0, minutes: { $gt: 0 }}, // 00:01 - 00:59
{ hour: { $gt: 0, $lt: 5 }} // 01:00 - 04:59
]
}
}
// 00:01 - 05:00
{
$match: {
$or: [
{ hour: 0, minutes: { $gt: 0}}, // 00:01 - 00:59
{ hour: { $gt: 0, $lt: 5 }}, // 01:00 - 04:59
{ hour: 5, minutes: 0} // 05:00
]
}
}
// 00:00 - 05:00
{
$match: {
$or: [
{ hour: { $gte: 0, $lt: 5 }}, // 00:00 - 04:59
{ hour: 5, minutes: 0 } // 05:00
]
}
}
// 00:01 - 04:59 AND 20:00 - 23:59
{
$match: {
$or: [
{ hour: 0, minutes: { $gt: 0}}, // 00:01 - 00:59
{ hour: { $gt: 0, $lt: 5 }}, // 01:00 - 04:59
{ hour: 5, minutes: 0}, // 05:00
{ hour: { $gte: 20, $lt: 24 }} // 20:00 - 23:59
]
}
}
Upvotes: 1