I have a "status" collection like this strcture -
我有一个像这个结构的“状态”集合 -
{ _id: ObjectId("545a0b63b03dbcd1238b4567"), status: 1004, comment: "Rem dolor ipsam placeat omnis non. Aspernatur nobis qui nisi similique.", created_at: ISODate("2014-11-05T11:34:59.804Z")},{ _id: ObjectId("545a0b66b03dbcd1238b4568"), status: 1001, comment: "Sint et eos vero ipsa voluptatem harum. Hic unde voluptatibus et blanditiis quod modi.", created_at: ISODate("2014-11-05T11:35:02.814Z")}........I need to get result grouped by 15 minutes interval from that collection.
我需要从该集合中获得15分钟间隔的结果。
4 个解决方案
#1
80
There are a couple of ways to do this.
有几种方法可以做到这一点。
The first is with Date Aggregation Operators, which allow you to dissect the "date" values in documents. Specifically for "grouping" as the primary intent:
第一个是使用日期聚合运算符,它允许您剖析文档中的“日期”值。特别是“分组”作为主要意图:
db.collection.aggregate([ { "$group": { "_id": { "year": { "$year": "$created_at" }, "dayOfYear": { "$dayOfYear": "$created_at" }, "hour": { "$hour": "$created_at" }, "interval": { "$subtract": [ { "$minute": "$created_at" }, { "$mod": [{ "$minute": "$created_at"}, 15] } ] } }}, "count": { "$sum": 1 } }}])The second way is by using a little trick of when a date object is subtracted (or other direct math operation) from another date object, then the result is a numeric value representing the epoch timestamp milliseconds between the two objects. So just using the epoch date you get the epoch milliseconds representation. Then use date math for the interval:
第二种方法是使用从另一个日期对象中减去日期对象(或其他直接数学运算)的小技巧,然后结果是表示两个对象之间的纪元时间戳毫秒的数值。因此,只需使用纪元日期即可获得纪元毫秒表示。然后使用日期数学作为间隔:
db.collection.aggregate([ { "$group": { "_id": { "$subtract": [ { "$subtract": [ "$created_at", new Date("1970-01-01") ] }, { "$mod": [ { "$subtract": [ "$created_at", new Date("1970-01-01") ] }, 1000 * 60 * 15 ]} ] }, "count": { "$sum": 1 } }}])So it depends on what kind of output format you want for the grouping interval. Both basically represent the same thing and have sufficient data to re-construct as a "date" object in your code.
因此,它取决于您希望分组间隔的输出格式类型。两者基本上代表相同的东西,并有足够的数据重新构建为代码中的“日期”对象。
You can put anything else you want in the "grouping operator" portion after the grouping _id. I'm just using the basic "count" example in lieu of any real statement from yourself as to what you really want to do.
在分组_id之后,您可以在“分组运算符”部分中放置您想要的任何其他内容。我只是使用基本的“计数”示例来代替您自己想要做的任何真实陈述。
MongoDB 4.x and Upwards
There were some additions to Date Aggregation Operators since the original writing, but from MongoDB 4.0 there will be actual "real casting of types" as opposed to the basic math tricks done here with BSON Date conversion.
自最初的写作以来,对日期聚合运算符进行了一些补充,但是从MongoDB 4.0开始,将会有实际的“实际类型转换”,而不是通过BSON日期转换完成的基本数学技巧。
For instance we can use $toLong and $toDate as new helpers here:
例如,我们可以在这里使用$ toLong和$ toDate作为新助手:
db.collection.aggregate([ { "$group": { "_id": { "$toDate": { "$subtract": [ { "$toLong": "$created_at" }, { "$mod": [ { "$toLong": "$created_at" }, 1000 * 60 * 15 ] } ] } }, "count": { "$sum": 1 } }}])That's a bit shorter and does not require defining an external BSON Date for the "epoch" value as a constant in defining the pipeline so it's pretty consistent for all language implementations.
这有点短,并且不需要将“epoch”值的外部BSON日期定义为定义管道的常量,因此它对于所有语言实现都非常一致。
Those are just two of the "helper" methods for type conversion which all tie back to the $convert method, which is a "longer" form of the implementation allowing for custom handling on null or error in conversion.
这些只是两种类型转换的“辅助”方法,它们全部绑定到$ convert方法,这是一种“更长”的实现形式,允许自定义处理null或转换错误。
It's even possible with such casting to get the Date information from the ObjectId of the primary key, as this would be a reliable source of "creation" date:
甚至可以通过这种转换从主键的ObjectId获取Date信息,因为这将是“创建”日期的可靠来源:
db.collection.aggregate([ { "$group": { "_id": { "$toDate": { "$subtract": [ { "$toLong": { "$toDate": "$_id" } }, { "$mod": [ { "$toLong": { "$toDate": "_id" } }, 1000 * 60 * 15 ] } ] } }, "count": { "$sum": 1 } }}])So "casting types" with this sort of conversion can be pretty powerful tool.
因此,使用这种转换的“投射类型”可以是非常强大的工具。
#2
14
I like the other answer here, and mostly for the use of date math instead of aggregation date operators which while helpful can also be a little obscure.
我喜欢这里的另一个答案,主要是使用日期数学而不是聚合日期操作符,虽然有用也可能有点模糊。
The only thing I want to add here is that you can also return a Date object from the aggregation framework by this approach as opposed to the "numeric" timestamp as the result. It's just a little extra math on the same principles, using $add:
我想在这里添加的唯一内容是,您还可以通过此方法从聚合框架返回Date对象,而不是作为结果的“数字”时间戳。使用$ add,它只是在相同原则上的一点额外数学:
db.collection.aggregate([ { "$group": { "_id": { "$add": [ { "$subtract": [ { "$subtract": [ "$current_date", new Date(0) ] }, { "$mod": [ { "$subtract": [ "$current_date", new Date(0) ] }, 1000 * 60 * 15 ]} ] }, new Date(0) ] }, "count": { "$sum": 1 } }}])The Date(0) contructs in JavaScript here represent the same "epoch" date in a shorter form, as 0 millisecond from epoch is epoch. But the main point is that when the "addition" to another BSON date object is done with a numeric identifier, then the inverse of the described condition is true and the end result is actually now a Date.
这里的JavaScript中的Date(0)构造以较短的形式表示相同的“epoch”日期,因为epoch的0毫秒是epoch。但重点是,当用数字标识符完成对另一个BSON日期对象的“添加”时,所描述条件的反转为真,最终结果实际上现在是日期。
All drivers will return the native Date type to their language by this approach.
所有驱动程序都会通过此方法将本机日期类型返回到其语言。
#3
7
A little more beautiful for mongo db.version() < 3.0
对于mongo db.version()<3.0来说更漂亮一点
db.collection.aggregate([ {$match: {created_at:{$exists:1}}}, {$group: { _id: {$add:[ {$dayOfYear: "$created_at" }, {$multiply: [{$year: "$created_at"}, 1000]} ]}, count: {$sum: 1 } }}, {$sort:{_id:-1}}])#4
3
Another useful way:
另一种有用的方法
db.collection.aggregate([ {$group: { _id: { overallTime: { $dateToString: { format: "%Y-%m-%dT%H", date: "$created_at" } }, interval: { $trunc: { $divide: [{ $minute: "$created_at" }, 15 ]}} }, }},])And more easier for min, hour, day intervals:
最小,小时,日间隔更容易:
var format = "%Y-%m-%dT%H:%M"; // 1 minvar format = "%Y-%m-%dT%H"; // 1 hourvar format = "%Y-%m-%d"; // 1 daydb.collection.aggregate([ {$group: { _id: { $dateToString: { format: format, date: "$created_at" } }, }},])#1
80
There are a couple of ways to do this.
有几种方法可以做到这一点。
The first is with Date Aggregation Operators, which allow you to dissect the "date" values in documents. Specifically for "grouping" as the primary intent:
第一个是使用日期聚合运算符,它允许您剖析文档中的“日期”值。特别是“分组”作为主要意图:
db.collection.aggregate([ { "$group": { "_id": { "year": { "$year": "$created_at" }, "dayOfYear": { "$dayOfYear": "$created_at" }, "hour": { "$hour": "$created_at" }, "interval": { "$subtract": [ { "$minute": "$created_at" }, { "$mod": [{ "$minute": "$created_at"}, 15] } ] } }}, "count": { "$sum": 1 } }}])The second way is by using a little trick of when a date object is subtracted (or other direct math operation) from another date object, then the result is a numeric value representing the epoch timestamp milliseconds between the two objects. So just using the epoch date you get the epoch milliseconds representation. Then use date math for the interval:
第二种方法是使用从另一个日期对象中减去日期对象(或其他直接数学运算)的小技巧,然后结果是表示两个对象之间的纪元时间戳毫秒的数值。因此,只需使用纪元日期即可获得纪元毫秒表示。然后使用日期数学作为间隔:
db.collection.aggregate([ { "$group": { "_id": { "$subtract": [ { "$subtract": [ "$created_at", new Date("1970-01-01") ] }, { "$mod": [ { "$subtract": [ "$created_at", new Date("1970-01-01") ] }, 1000 * 60 * 15 ]} ] }, "count": { "$sum": 1 } }}])So it depends on what kind of output format you want for the grouping interval. Both basically represent the same thing and have sufficient data to re-construct as a "date" object in your code.
因此,它取决于您希望分组间隔的输出格式类型。两者基本上代表相同的东西,并有足够的数据重新构建为代码中的“日期”对象。
You can put anything else you want in the "grouping operator" portion after the grouping _id. I'm just using the basic "count" example in lieu of any real statement from yourself as to what you really want to do.
在分组_id之后,您可以在“分组运算符”部分中放置您想要的任何其他内容。我只是使用基本的“计数”示例来代替您自己想要做的任何真实陈述。
MongoDB 4.x and Upwards
There were some additions to Date Aggregation Operators since the original writing, but from MongoDB 4.0 there will be actual "real casting of types" as opposed to the basic math tricks done here with BSON Date conversion.
自最初的写作以来,对日期聚合运算符进行了一些补充,但是从MongoDB 4.0开始,将会有实际的“实际类型转换”,而不是通过BSON日期转换完成的基本数学技巧。
For instance we can use $toLong and $toDate as new helpers here:
例如,我们可以在这里使用$ toLong和$ toDate作为新助手:
db.collection.aggregate([ { "$group": { "_id": { "$toDate": { "$subtract": [ { "$toLong": "$created_at" }, { "$mod": [ { "$toLong": "$created_at" }, 1000 * 60 * 15 ] } ] } }, "count": { "$sum": 1 } }}])That's a bit shorter and does not require defining an external BSON Date for the "epoch" value as a constant in defining the pipeline so it's pretty consistent for all language implementations.
这有点短,并且不需要将“epoch”值的外部BSON日期定义为定义管道的常量,因此它对于所有语言实现都非常一致。
Those are just two of the "helper" methods for type conversion which all tie back to the $convert method, which is a "longer" form of the implementation allowing for custom handling on null or error in conversion.
这些只是两种类型转换的“辅助”方法,它们全部绑定到$ convert方法,这是一种“更长”的实现形式,允许自定义处理null或转换错误。
It's even possible with such casting to get the Date information from the ObjectId of the primary key, as this would be a reliable source of "creation" date:
甚至可以通过这种转换从主键的ObjectId获取Date信息,因为这将是“创建”日期的可靠来源:
db.collection.aggregate([ { "$group": { "_id": { "$toDate": { "$subtract": [ { "$toLong": { "$toDate": "$_id" } }, { "$mod": [ { "$toLong": { "$toDate": "_id" } }, 1000 * 60 * 15 ] } ] } }, "count": { "$sum": 1 } }}])So "casting types" with this sort of conversion can be pretty powerful tool.
因此,使用这种转换的“投射类型”可以是非常强大的工具。
#2
14
I like the other answer here, and mostly for the use of date math instead of aggregation date operators which while helpful can also be a little obscure.
我喜欢这里的另一个答案,主要是使用日期数学而不是聚合日期操作符,虽然有用也可能有点模糊。
The only thing I want to add here is that you can also return a Date object from the aggregation framework by this approach as opposed to the "numeric" timestamp as the result. It's just a little extra math on the same principles, using $add:
我想在这里添加的唯一内容是,您还可以通过此方法从聚合框架返回Date对象,而不是作为结果的“数字”时间戳。使用$ add,它只是在相同原则上的一点额外数学:
db.collection.aggregate([ { "$group": { "_id": { "$add": [ { "$subtract": [ { "$subtract": [ "$current_date", new Date(0) ] }, { "$mod": [ { "$subtract": [ "$current_date", new Date(0) ] }, 1000 * 60 * 15 ]} ] }, new Date(0) ] }, "count": { "$sum": 1 } }}])The Date(0) contructs in JavaScript here represent the same "epoch" date in a shorter form, as 0 millisecond from epoch is epoch. But the main point is that when the "addition" to another BSON date object is done with a numeric identifier, then the inverse of the described condition is true and the end result is actually now a Date.
这里的JavaScript中的Date(0)构造以较短的形式表示相同的“epoch”日期,因为epoch的0毫秒是epoch。但重点是,当用数字标识符完成对另一个BSON日期对象的“添加”时,所描述条件的反转为真,最终结果实际上现在是日期。
All drivers will return the native Date type to their language by this approach.
所有驱动程序都会通过此方法将本机日期类型返回到其语言。
#3
7
A little more beautiful for mongo db.version() < 3.0
对于mongo db.version()<3.0来说更漂亮一点
db.collection.aggregate([ {$match: {created_at:{$exists:1}}}, {$group: { _id: {$add:[ {$dayOfYear: "$created_at" }, {$multiply: [{$year: "$created_at"}, 1000]} ]}, count: {$sum: 1 } }}, {$sort:{_id:-1}}])#4
3
Another useful way:
另一种有用的方法
db.collection.aggregate([ {$group: { _id: { overallTime: { $dateToString: { format: "%Y-%m-%dT%H", date: "$created_at" } }, interval: { $trunc: { $divide: [{ $minute: "$created_at" }, 15 ]}} }, }},])And more easier for min, hour, day intervals:
最小,小时,日间隔更容易:
var format = "%Y-%m-%dT%H:%M"; // 1 minvar format = "%Y-%m-%dT%H"; // 1 hourvar format = "%Y-%m-%d"; // 1 daydb.collection.aggregate([ {$group: { _id: { $dateToString: { format: format, date: "$created_at" } }, }},])