Get number of days between two entries with mongodb

Question

I would like to get number of days between my two last entries with mongoDb grouped by number of days. Here is my table :

-------------------------------------------------
| mac address                | date             |
-------------------------------------------------
| aa:bb:cc:dd:ee:ff          | 2016-11-15       |
| aa:bb:cc:dd:ee:ff          | 2016-11-19       |
| aa:bb:cc:dd:ee:ff          | 2016-11-20       |
| ff:ee:dd:cc:bb:aa          | 2016-11-19       |
| ff:ee:dd:cc:bb:aa          | 2016-11-28       |
| aa:aa:aa:aa:aa:aa          | 2016-11-21       |
| bb:bb:bb:bb:bb:bb          | 2016-11-22       |
| bb:bb:bb:bb:bb:bb          | 2016-11-25       |
| cc:cc:cc:cc:cc:cc          | 2016-11-20       |
| cc:cc:cc:cc:cc:cc          | 2016-11-23       |
-------------------------------------------------

And here what I want to get :

-------------------------------------------------
| Number of days             | count            |
-------------------------------------------------
| 1 day                      | 1                |
| 2-7 days                   | 2                |
| 8-30 days                  | 1                |
| > 30 days                  | 0                |
-------------------------------------------------

Is there any possibility to do it in one request using mongodb ?

Thanks a lot.

Answer 1

you can do it in a single query with something like this

   db.date.aggregate([
   {
      $sort:{
         date:-1
      }
   },
   {
      $group:{
         _id:"$mac",
         date:{
            $push:"$date"
         }
      }
   },
   {
      $project:{
         _id:"$mac",
         laps:{
            $subtract:[
               {
                  $arrayElemAt:[
                     "$date",
                     0
                  ]
               },
               {
                  $arrayElemAt:[
                     "$date",
                     1
                  ]
               }
            ]
         }
      }
   },
   {
      "$group":{
         "_id":null,
         "1_day":{
            "$sum":{
               "$cond":[
                  {
                     "$lte":[
                        "$laps",
                        (1000 *60 * 60 * 24)
                     ]
                  },
                  1,
                  0
               ]
            }
         },
         "2_7days":{
            "$sum":{
               "$cond":[
                  {
                     $and:[
                        {
                           "$gt":[
                              "$laps",
                              (1000 *60 * 60 * 24*2)
                           ]
                        },
                        {
                           "$lte":[
                              "$laps",
                              (1000 *60 * 60 * 24*7)
                           ]
                        },

                     ]
                  },
                  1,
                  0
               ]
            }
         },
         "8_30days":{
            "$sum":{
               "$cond":[
                  {
                     $and:[
                        {
                           "$gt":[
                              "$laps",
                              (1000 *60 * 60 * 24*8)
                           ]
                        },
                        {
                           "$lte":[
                              "$laps",
                              (1000 *60 * 60 * 24*30)
                           ]
                        },

                     ]
                  },
                  1,
                  0
               ]
            }
         },
         "30+days":{
            "$sum":{
               "$cond":[
                  {
                     "$gte":[
                        "$laps",
                        (1000 *60 * 60 * 24*30)
                     ]
                  },
                  1,
                  0
               ]
            }
         }
      }
   }
])

it will return somthing like this:

{
   "_id":null,
   "1_day":1,
   "2_7days":3,
   "8_30days":0,
   "30+days":0
}

you may need to adjust bounds to better fit your needs

try it online: mongoplayground.net/p/JqolUAp2lfk