CODEWITHSUNDEEP
mongodb
Clutch
Clutch

Reputation: 7600

Need to match for a certain day using a timestamp in MongoDB

Using example doc

{ "_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}

I have the following query that groups information by day:

[ { "$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}}
]

So now I want to reduce it to hours for a particular day. I thought all I needed was to change the _id and add another item to $match such as {{"$dayOfMonth" : "$ts"} :1} for the first day of the month but MongoDb hated that some much it hung my IDE. What would be the proper $match query?

The query I'm working on currently:

[ { "$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}}
]

Upvotes: 3

Views: 3459

Answers (1)

ChrisCool
ChrisCool

Reputation: 134

Use $gte and $lt operator in your match pipeline, it will use your index (if ts is in an index), so your match pipeline will be :

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

Complete query : 

[{ "$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}}
]

Upvotes: 3

Related Questions