MongoDb汇总按日期分组 [英] MongoDb aggregation Group by Date

问题描述

我正在尝试按时间戳将名为"foo" {_id，TimeStamp}的集合进行分组

I'm trying to group by timestamp for the collection named "foo" { _id, TimeStamp }

db.foos.aggregate(
[
   {$group : { _id : new Date (Date.UTC({ $year : '$TimeStamp' },{ $month : '$TimeStamp' },{$dayOfMonth : '$TimeStamp'}))       }}
])

预计有多个日期，但结果只是一个日期.我使用的数据是正确的(除1970外，有许多foo和不同的日期).日期解析中存在一些问题，但我仍无法解决.

Expecting many dates but the result is just one date. The data i'm using is correct (has many foo and different dates except 1970). There's some problem in the date parsing but i can not solve yet.

{
    "result" : [ 
        {
            "_id" : ISODate("1970-01-01T00:00:00.000Z")
        }
    ],
    "ok" : 1
}

尝试过一次:

db.foos.aggregate(
[
   {$group : { _id : { year : { $year : '$TimeStamp' }, month : { $month : '$TimeStamp' }, day : {$dayOfMonth : '$TimeStamp'} }, count : { $sum : 1 }       }},
   {$project : { parsedDate : new Date('$_id.year', '$_id.month', '$_id.day') , count : 1, _id : 0} }
])

结果:

uncaught exception: aggregate failed: {
    "errmsg" : "exception: disallowed field type Date in object expression (at 'parsedDate')",
    "code" : 15992,
    "ok" : 0
}

那一个:

db.foos.aggregate(
[
   {$group : { _id : { year : { $year : '$TimeStamp' }, month : { $month : '$TimeStamp' }, day : {$dayOfMonth : '$TimeStamp'} }, count : { $sum : 1 }       }},
   {$project : { parsedDate : Date.UTC('$_id.year', '$_id.month', '$_id.day') , count : 1, _id : 0} }
])

在结果中看不到日期

{
    "result" : [ 
        {
            "count" : 412
        }, 
        {
            "count" : 1702
        }, 
        {
            "count" : 422
        }
    ],
    "ok" : 1
}

推荐答案

这取决于您是否要在最终输出中将日期作为ISODate类型.如果是这样，那么您可以执行以下两项操作之一:

It depends on whether you want to have the date as ISODate type in the final output. If so, then you can do one of two things:

1. 从时间戳中提取$year，$month，$dayOfMonth，然后从它们中重构一个新日期(您已经在尝试这样做，但是您使用的语法无效)在聚合框架中).

1. Extract $year, $month, $dayOfMonth from your timestamp and then reconstruct a new date out of them (you are already trying to do that, but you're using syntax that doesn't work in aggregation framework).

如果原始时间戳为ISODate()类型，则可以进行日期算术以从时间戳中减去小时，分钟，秒和毫秒，以获取新的日期，该日期将四舍五入为一天. p>

If the original Timestamp is of type ISODate() then you can do date arithmetic to subtract the hours, minutes, seconds and milliseconds from your timestamp to get a new date that's "rounded" to the day.

有一个示例的2个在这里.

这里是您的处理方法1.我假设您所有的日期都是今年，但是您可以轻松地调整数学以适应最早的日期.

Here is how you would do 1. I'm making an assumption that all your dates are this year, but you can easily adjust the math to accommodate your oldest date.

project1={$project:{_id:0, 
                   y:{$subtract:[{$year:"$TimeStamp"}, 2013]},
                   d:{$subtract:[{$dayOfYear:"$TimeStamp"},1]}, 
                   TimeStamp:1, 
                   jan1:{$literal:new ISODate("2013-01-01T00:00:00")}
         } };
project2={$project:{tsDate:{$add:[
                       "$jan1",
                       {$multiply:["$y", 365*24*60*60*1000]},
                       {$multiply:["$d", 24*60*60*1000]}
         ] } } };

样本数据:

db.foos.find({},{_id:0,TimeStamp:1})
{ "TimeStamp" : ISODate("2013-11-13T19:15:05.600Z") }
{ "TimeStamp" : ISODate("2014-02-01T10:00:00Z") }

汇总结果:

> db.foos.aggregate(project1, project2)
{ "tsDate" : ISODate("2013-11-13T00:00:00Z") }
{ "tsDate" : ISODate("2014-02-01T00:00:00Z") }

这篇关于MongoDb汇总按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！