How to group documents for the last 12 months, month wise when you have unix time stored as timeStam-CodePudding

I have a collection where data is updated daily based on unix epoch time. For example:

{ 
    "uID" : "12345678", 
    "midNightTimeStamp" : NumberInt(1645381800), // 21st Feb 2022 midnight IST
    "energyConsumed" : NumberInt(53)
},
{ 
    "uID" : "12345678", 
    "midNightTimeStamp" : NumberInt(1645641000), // 24th Feb 2022 midnight IST
    "energyConsumed" : NumberInt(30)
}

Now if you wanted to query last 12 months data month wise, what would have been your approach to solve this? All I know is that I could group my data in aggregation query something like this:

 $project: {
            energyConsumed: 1.0,
            year: {
              $year: // How would you convert this from epoch
            },
            month: {
              $month: // How would you convert this from epoch
            }
          },

Now if I wanted to group my data from last 12 months as something like this

  $group: {
            _id: '$month',
            energyConsumed: {
              $sum: '$energyConsumed'
            }
          }

And my desired output will be:

{
  id: 04 // (something like 04 or just April but data should be sorted month and year wise April 2021),
  energyConsumed: 4179772
},
{
  id: 05 // (something like 05 or just May but data should be sorted month and year wise),
  energyConsumed: 6179772
},
...
...
{
  id: 03 // (something like 03 or just March (March 2022),
  energyConsumed: 5643772
}

CodePudding user response：

Maybe this:

 db.collection.aggregate([
 {
   $group: {
    _id: {
      $dateToString: {
       format: "%Y-%m",
        date: {
          $toDate: {
          "$multiply": [
            "$midNightTimeStamp",
            1000
          ]
        }
      }
     }
     },
     monthlyConsumption: {
       $sum: "$energyConsumed"
     }
    }
   }
 ])

Explained:

$group based on Year-Month ("YYYY-mm" ) extracted by the midNightTimeStamp field and $sum the monthly consumption

playground

CodePudding user response：

To catch just the last 12 months from right now, throw this in front of the $group stage above:

// Back up one year from right now:                                                                           
var sdate = new ISODate();
sdate.setYear((sdate.getYear()-1)   1900);

db.foo.aggregate([
    {$match: {$expr: {$gt:[{$toDate: {$multiply:['$midNightTimeStamp',1000]}},sdate]} } },
    {$group: { ... // as above

CodePudding user response：

If you run already MongoDB version 5.0 then you can use $dateTrunc:

db.collection.aggregate([
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: { $toDate: { $toLong: { $multiply: ["$midNightTimeStamp", 1000] } } },
          unit: "month",
          timezone: "Europe/Zurich"          
        }
      },
      energyConsumed: { $sum: "$energyConsumed" }
    }
  }
])

It might be more precise than $dateToString: { format: "%Y-%m" ... due to timezone option.