mongoDB timestamp aggregation nodejs mongoose-CodePudding

I have searched for a while now and not really finding a clear enough answer to understand how to do it. I have chart data saved in MongoDB on a minute time-base (Unix timestamp). Now I understand that if I want to visualize the chart in hourly or daily based time I need to aggregate the data, but I am not fully understanding what needs to be done.

I want to create groups of 1 hour and 1 day, those need to contain the last record of its timeframe. and then does the aggregation need to be done once and it saves to DB? or every time I query it. and how do I setup the query in mongoose?

Here a example of my collection chart data

collection:[
{
 time: 1649083392,
 volume: '20384.28',
 open: 444.42,
 close: 444.56,
 high: 444.76,
 low: 444.36
},
{
 time: 1649083448,
 volume: '20384.28',
 open: 444.42,
 close: 444.56,
 high: 444.76,
 low: 444.36
},
{
 time: 1649083501,
 volume: '20384.28',
 open: 444.42,
 close: 444.56,
 high: 444.76,
 low: 444.36
}
]

lets say I need a chart per day timeframe. there are 1440 minutes in a day.

I first need to make a group per day to add up volume per day and then project the last item for each day. it should project the volume per day and the last item of the day for each day.

Hope someone can explain me a bit how this works. Thanks.

CodePudding user response：

db.collection.aggregate([
  {
    $sort: { time: -1 }
  },
  {
    $set: {
      d: { $toDate: { $multiply: [ "$time", 1000 ] } },
      volume: { $toDecimal: "$volume" }
    }
  },
  {
    $facet: {
      day: [
        {
          $group: {
            _id: {
              $dateTrunc: {
                date: "$d",
                unit: "day"
              }
            },
            volume: { $sum: "$volume" },
            lastItem: { $first: "$$ROOT" }
          }
        }
      ],
      hour: [
        {
          $group: {
            _id: {
              year: { $year: "$d" },
              dayOfYear: { $dayOfYear: "$d" },
              hour: { $hour: "$d" }
            },
            volume: { $sum: "$volume" },
            lastItem: { $first: "$$ROOT" }
          }
        }
      ],
      15min: [
        {
          $group: {
            _id: {
              year: { $year: "$d" },
              dayOfYear: { $dayOfYear: "$d" },
              hour: { $hour: "$d" },
              interval: {
                $subtract: [
                  { $minute: "$d" },
                  { $mod: [ { $minute: "$d" }, 15 ] }
                ]
              }
            },
            volume: { $sum: "$volume" },
            lastItem: { $first: "$$ROOT" }
          }
        }
      ]
    }
  }
])

mongoplayground

CodePudding user response：

I Know That this has already been answered but here is my take by flooring the timestamp to the interval and then grouping the data based on the timestampBoundary

db.data.aggregate([
    {
        $addFields: {
            // Your Group Interval In Seconds eg.
            // - 86400 For Per Day
            // - 3600 Per Hour
            // - 900 Per 15 minute
            timestampBoundary: {
                $subtract: ["$time", {$mod: ["$time", 3600]}]
            },
        }
    },
    {
        $sort: {
            time: -1
        }
    },
    {
        $group: {
            _id: "$timestampBoundary",
            lastItem: {$first: "$$ROOT"},
            totalVolume: {
                $sum: {
                    $toDecimal: "$volume"
                }
            },
        }
    }
])