Based on the documents below, I need to count the records that the difference between the inicial_date and end_date is greater than and less than 5 minutes and the name = A.
[{
"_id": 1,
"name": "A",
"inicial_date": "2019-01-01 12:00:00",
"end_date": "2019-01-01 12:01:00"
},{
"_id": 2,
"name": "A",
"inicial_date": "2019-01-01 12:05:00",
"end_date": "2019-01-01 12:07:00"
},{
"_id": 3,
"name": "A",
"inicial_date": "2019-01-01 12:00:00",
"end_date": "2019-01-01 12:06:00"
},{
"_id": 4,
"name": "A",
"inicial_date": "2019-01-01 12:04:00",
"end_date": "2019-01-01 12:05:00"
},
"_id": 5,
"name": "A",
"inicial_date": "2019-01-01 12:10:00",
"end_date": "2019-01-01 12:20:00"
},{
"_id": 6,
"name": "A",
"inicial_date": "2019-01-01 12:00:00",
"end_date": "2019-01-01 12:08:00"
},{
"_id": 7,
"name": "A",
"inicial_date": "2019-01-01 13:00:00",
"end_date": "2019-01-01 13:01:00"
},{
"_id": 8,
"name": "B",
"inicial_date": "2019-01-01 14:00:00",
"end_date": "2019-01-01 14:09:00"
}]
The expected result:
{
"less_than_5": 4,
"greater_than_5": 3
}
Well, you can do this using an aggregate query,
db.collection.aggregate([
{
$match: {
name: "A"
}
},
{
// find time difference, the result is in milliseconds
$project: {
timeDiffInMilliseconds: {
$subtract: [
{
$toDate: "$end_date"
},
{
$toDate: "$inicial_date"
}
]
}
}
},
{
// convert the time difference to minutes
$project: {
timeDiffInMinutes: {
$divide: [
"$timeDiffInMilliseconds",
60000
]
}
}
},
{
// check if the number of minutes is greater than 5 mins or not
$project: {
timeDiffGreaterThan5Mins: {
$cond: [
{
$gt: [
"$timeDiffInMinutes",
5
]
},
1,
0
]
}
}
},
{
// group according to greater than 5 minutes or not
$group: {
_id: "null",
greater_than_5: {
$sum: {
$cond: [
{
$eq: [
"$timeDiffGreaterThan5Mins",
1
]
},
1,
0
]
}
},
less_than_5: {
$sum: {
$cond: [
{
$eq: [
"$timeDiffGreaterThan5Mins",
0
]
},
1,
0
]
}
}
}
}
])
Making it a bit more efficient,
db.collection.aggregate([
{
$match: {
name: "A"
}
},
{
$project: {
more_than_5_mins: {
$cond: [
{
$gt: [
{
$subtract: [
{
$toDate: "$end_date"
},
{
$toDate: "$inicial_date"
}
]
},
300000
]
},
1,
0
]
}
}
},
{
$group: {
_id: "",
less_than_5: {
$sum: {
$cond: [
{
$eq: [
"$more_than_5_mins",
0
]
},
1,
0
]
}
},
greater_than_5: {
$sum: {
$cond: [
{
$eq: [
"$more_than_5_mins",
1
]
},
1,
0
]
}
}
}
}
])