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.

推荐答案

是的.您可以将日期运算符与$substr 和 $concat 将它们联系在一起.

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 日期从另一个返回毫秒差异.这允许您通过再次减去 $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屋！