pymongo按日期时间分组 [英] pymongo group by datetime

问题描述

我正在尝试搜索集合，并按日期字段(即日期时间)对记录进行分组. 我知道pymongo会在后台将其转换为正确的类型(ISODate或类似的东西).

Im trying to search through a collection and group records by date field which is a datetime. I know pymongo converts those to the proper type on the background (ISODate or something like that).

问题是，由于datetime对象具有日期，时间，时区..我如何告诉组运算符仅使用日期部分?因为否则我将无法获得所需的分组，因为时间阻止了将具有相同日期，月份，年份的记录分组在一起.

Question is, since datetime objects have date, time, timezone.. how can i tell the group operator to use only the date portion? Because otherwise i dont get the desired grouping since time is preventing the records with same day, month, year to be grouped together.

db.test.aggregate([
        {"$group": {
             "_id": "$date", 
             "count": {"$sum": 1}
        }},
        {"$limit": 10}])

结果:

{u'ok': 1.0,
 u'result': [
  {u'_id': datetime.datetime(2014, 2, 15, 18, 49, 9, tzinfo=<bson.tz_util.FixedOffset object at 0x318f210>),
   u'count': 1},
  {u'_id': datetime.datetime(2014, 2, 15, 18, 36, 38, tzinfo=<bson.tz_util.FixedOffset object at 0x318f210>),
   u'count': 1},
  {u'_id': datetime.datetime(2014, 2, 15, 18, 23, 56, tzinfo=<bson.tz_util.FixedOffset object at 0x318f210>),
   u'count': 1}]}

最好控制用于分组的日期时间信息

It would be nice to control the datetime information used to group,

• 仅按日期分组
• 按日期和小时分组
• 按日期，小时和分钟分组
• 等

有没有类似的东西:(或某种告诉使用日期的方式)

Is there something like: (or some way of telling to use date only)

db.test.aggregate([
          {"$group": {
              "_id": "$date.date()",
              "count": {"$sum": 1}
          }},
          {"$sort": "_id"}
])

或者也许有另一种处理方式，有什么想法吗? 谢谢.

Or maybe there’s another way of dealing with this, any ideas? Thanks.

推荐答案

是.您可以将日期运算符与

Yes. You can use the Date Operators with $substr and $concat to tie it all together.

db.test.aggregate([
    {"$group": {
        "_id" : { "$concat": [
            {"$substr": [{"$year": "$date"}, 0, 4 ]},
            "-",
            {"$substr": [{"$month": "$date"}, 0, 2 ]},
            "-",
            {"$substr": [{"$dayOfMonth": "$date"}, 0, 2 ]},
        ]},
        "count": {"$sum": 1 }
     }},
     {"$sort": { "_id": 1 }}
])

您可以仅使用日期运算符并按照以下步骤制作文档:

You could use just the date operators and make a document as in:

"day": { 
    "year": {"$year": "$date" },
   "month": {"$month": "$date"}, 
   "day": {"$dayOfYear": "$date"}
}

效果也一样.但这为您提供了一个不错的字符串.这利用了$substr将从整数转换为字符串的事实.如果有的话会添加到文档中.

That works just as well. But this gives you a nice string. This makes use of the fact that $substr will cast from integer to string. If that ever gets added to the documentation.

查看日期运算符文档，以用于其他文档可以用于日期的时分.

Look at the Date Operators documentation for usage on the other time divisions that can be used on dates.

更好的方法是，使用日期数学返回BSON日期:

Better yet, use date math to return a BSON Date:

import datetime

db.test.aggregate([
    { "$group": {
        "_id": {
            "$add": [
               { "$subtract": [
                   { "$subtract": [ "$date", datetime.datetime.utcfromtimestamp(0) ] },
                   { "$mod": [
                       { "$subtract": [ "$date", datetime.datetime.utcfromtimestamp(0) ] },
                       1000 * 60 * 60 * 24
                   ]}
               ]},
               datetime.datetime.utcfromtimestamp(0)
           ]
        },
        "count": { "$sum": 1 }
    }},
    { "$sort": { "_id": 1 } }
])

datetime.datetime.utcfromtimestamp(0) 管道作为代表时代"的BSON日期.当您 $subtract 一个BSON日期与另一个BSON日期之间的差异以毫秒为单位返回.这样，您可以通过再次减去 $mod 结果可得出与一天相比剩余的毫秒数.

Here datetime.datetime.utcfromtimestamp(0) will be fed into the pipeline as a BSON Date representing "epoch". When you $subtract one BSON Date from another the difference in milliseconds is returned. This allows you to "round" the date to the current day by again subtracting the $mod result to get the remainder of milliseconds difference from a day.

$add 也是一样， 将BSON日期转换为数值将导致BSON日期.

The same is true of $add where "adding" a BSON Date to a numeric value will result in a BSON Date.

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