MongoDB Group by Date
I have a collection with datetime and count field :
{
_id: null,
datetime: new Date(),
count: 1234
}
I want to get counts for 24 hours, 7 days and 30 days. The result should look like this:
{"sum": 100, "interval": "day"},
{"sum": 700, "interval": "week"},
{"sum": 3000, "interval": "month"}
EDIT:
More abstract: I want group results on multiple conditions (in this case, multiple time intervals)
MySQL example:
SELECT
IF (time>CURRENT_TIMESTAMP() - INTERVAL 24 HOUR, 1, 0) last_day,
IF (time>CURRENT_TIMESTAMP() - INTERVAL 168 HOUR, 1, 0) last_week,
IF (time>CURRENT_TIMESTAMP() - INTERVAL 720 HOUR, 1, 0) last_month,
SUM(count) count
FROM table
GROUP BY last_day,
last_week,
last_month
source to share
There are two different ways to do this. One is to issue a separate count()
query for each of the ranges. It's pretty simple and if the datetime field is indexed it will be fast.
The second way is to combine them all into one query using a similar method like your SQL example. To do this, you need to use the method aggregate()
by creating a pipeline $project
to create 0 or 1 values ββfor the new "last_day", "last_week" and "last_month" fields, and then use them $group
to execute the sums.
source to share
date aggregation agents are available for MongoDB aggregation framework. For example, the operator is used to get this value from the date of use when grouping: $dayOfYear
db.collection.aggregate([
{ "$group": {
"_id": { "$dayOfYear": "$datetime" },
"total": { "$sum": "$count" }
}}
])
Or, you can use a mathematical approach to date instead. By applying an epoch date, you are converting a date object to a number in which math can be applied:
db.collection.aggregate([
{ "$group": {
"_id": {
"$subtract": [
{ "$subtract": [ "$datetime", new Date("1970-01-01") ] },
{ "$mod": [
{ "$subtract": [ "$datetime", new Date("1970-01-01") ] },
1000 * 60 * 60 * 24
]}
]
},
"total": { "$sum": "$count" }
}}
])
If what you are after is an interval from the current point in time, then what you want is basically a mathematical approach to date and works in some conditional expressions with : $cond
db.collection.aggregate([
{ "$match": {
"datetime": {
"$gte": new Date(new Date().valueOf() - ( 1000 * 60 * 60 * 24 * 365 ))
}
}},
{ "$group": {
"_id": null,
"24hours": {
"$sum": {
"$cond": [
{ "$gt": [
{ "$subtract": [ "$datetime", new Date("1970-01-01") ] },
new Date().valueOf() - ( 1000 * 60 * 60 * 24 )
]},
"$count",
0
]
}
},
"30days": {
"$sum": {
"$cond": [
{ "$gt": [
{ "$subtract": [ "$datetime", new Date("1970-01-01") ] },
new Date().valueOf() - ( 1000 * 60 * 60 * 24 * 30 )
]},
"$count",
0
]
}
},
"OneYear": {
"$sum": {
"$cond": [
{ "$gt": [
{ "$subtract": [ "$datetime", new Date("1970-01-01") ] },
new Date().valueOf() - ( 1000 * 60 * 60 * 24 * 365 )
]},
"$count",
0
]
}
}
}}
])
This is essentially the same approach as the SQL example, where the query conditionally evaluates if the date value is within the required range and decides whether to add the value to the sum.
Another addition is an additional step to restrict the request to actions to only those items that can be within the maximum annual range that you are asking for. This makes it slightly better than the SQL presented, since the index can be used to filter these values, and you don't have to "force" through the mismatched data in the collection. $match
It is always recommended to limit input when using the aggregation pipeline. $match
source to share