需要在MongoDB中使用时间戳匹配某一天

Question

使用示例文档

{ "_id": {
    "$oid": "527339384bb8d32905f000853"
}, "format": "flat1", "aggregation": "raw", "vm_id": "10101010", "hostname": "foo.example.com", "fooid": "100822", "ts": {
    "$date": "2013-11-01T05:00:23.000Z"
}, "cpu_nanoseconds": 1410576880000000, "disk_usage": 20069460, "interface_public_rx_packets": 35771474, "interface_public_rx_bytes": 6023191691, "interface_public_rx_errors": 0, "interface_public_rx_drop": 0, "interface_public_tx_packets": 26004483, "interface_public_tx_bytes": 37293536055, "interface_public_tx_errors": 0, "interface_public_tx_drop": 0, "interface_private_rx_packets": 846833, "interface_private_rx_bytes": 63898435, "interface_private_rx_errors": 0, "interface_private_rx_drop": 0, "interface_private_tx_packets": 39, "interface_private_tx_bytes": 1638, "interface_private_tx_errors": 0, "interface_private_tx_drop": 0, "disk_primary_read_requests": 3280869, "disk_primary_read_bytes": 39818978304, "disk_primary_write_requests": 40331710, "disk_primary_write_bytes": 685420728320, "disk_swap_read_requests": 32, "disk_swap_read_bytes": 823808, "disk_swap_write_requests": 16, "disk_swap_write_bytes": 253952, "vcpu_nanoseconds_0": 470437920000000, "vcpu_nanoseconds_1": 344849460000000, "vcpu_nanoseconds_2": 342793890000000, "max_vcpus": 3}

我有以下按天分组信息的查询：

[ { "$match" : { "vm_id" : "1111223"}},
  { "$group" : {
                 "_id" : { "$dayOfMonth" : "$ts"},
                 "month" : { "$first" : { "$month" : "$ts"}},
                 "year" : { "$first" : { "$year" : "$ts"}},
                 "vm_id" : { "$first" : "$vm_id"},
                 "max_public_tx" : { "$max" : "$interface_public_tx_bytes"},
                 "public_tx_total" : { "$sum" : "$interface_public_tx_bytes"},
                 "public_rx_total" : { "$sum" : "$interface_public_rx_bytes"},
                 "private_tx_total" : { "$sum" : "$interface_private_tx_bytes"},
                 "private_rx_total" : { "$sum" : "$interface_private_rx_bytes"},
                 "count" : { "$sum" : 1}}
               },
  { "$sort" : { "_id" : 1}}
]

所以现在我想把它减少到某一天的几个小时。我想我所需要的只是更改_id，并在每月的第一天向$match添加另一个项目，例如{"$dayOfMonth"："$ts"}:1}，但MongoDb非常讨厌它挂起了我的IDE。什么是合适的$match查询？

我目前正在处理的查询：

[ { "$match" : { "vm_id" : "1111223",{ "$dayOfMonth" : "$ts"}: 1 }},
  { "$group" : {
                 "_id" : { "$hour" : "$ts"},
                 "day" : {"$first" : {"$dayOfMonth" : "$ts"}},
                 "month" : { "$first" : { "$month" : "$ts"}},
                 "year" : { "$first" : { "$year" : "$ts"}},
                 "vm_id" : { "$first" : "$vm_id"},
                 "max_public_tx" : { "$max" : "$interface_public_tx_bytes"},
                 "public_tx_total" : { "$sum" : "$interface_public_tx_bytes"},
                 "public_rx_total" : { "$sum" : "$interface_public_rx_bytes"},
                 "private_tx_total" : { "$sum" : "$interface_private_tx_bytes"},
                 "private_rx_total" : { "$sum" : "$interface_private_rx_bytes"},
                 "count" : { "$sum" : 1}}
               },
  { "$sort" : { "_id" : 1}}
]

Answer 1

在匹配管道中使用$gte和$lt运算符，它将使用您的索引（如果ts在索引中），因此您的匹配管道将是：

{ "$match" : { "vm_id" : "1111223","ts" : {$gte:ISODate('2013-11-01T00:00:00.000Z'), $lt:ISODate('2013-11-02T00:00:00.000Z') }}},

完整查询：

[{ "$match" : { "vm_id" : "1111223","ts" : {$gte:ISODate('2013-11-01T00:00:00.000Z'), $lt:ISODate('2013-11-02T00:00:00.000Z') }}},  
{ "$group" : {
             "_id" : { "$hour" : "$ts"},
             "day" : {"$first" : {"$dayOfMonth" : "$ts"}},
             "month" : { "$first" : { "$month" : "$ts"}},
             "year" : { "$first" : { "$year" : "$ts"}},
             "vm_id" : { "$first" : "$vm_id"},
             "max_public_tx" : { "$max" : "$interface_public_tx_bytes"},
             "public_tx_total" : { "$sum" : "$interface_public_tx_bytes"},
             "public_rx_total" : { "$sum" : "$interface_public_rx_bytes"},
             "private_tx_total" : { "$sum" : "$interface_private_tx_bytes"},
             "private_rx_total" : { "$sum" : "$interface_private_rx_bytes"},
             "count" : { "$sum" : 1}}
           },
{ "$sort" : { "_id" : 1}}
]