Calculate the value of a session per day across bigger intervals

Alexandru_Ionescu · 2024-06-04T15:59:46.349Z

Hello all.

I have a time-series collection pertaining to some electric vehicles. I am trying to use a MongoDB Aggregate to correctly calculate the total energy charged per day, however I am stuck because of sessions that span across multiple days.

If a sessions spans multiple days, I need the total for each day. Each consecutive day should take into account the day before, so if the device is charging 10 units per hours from 10 PM to 2 AM, I am getting the values: 10, 20, 30, 40 and what I need is something like “day1: 20”, “day2: 20”. If on day 2 I have another session, the total should be added to the one from the first session on day 2.

Here are some example documents:

{
  "timestamp": {
    "$date": "2024-05-18T23:59:46.271Z"
  },
  "value": "charging_active",
  "charged": 8.3
},
{
  "timestamp": {
    "$date": "2024-05-19T00:00:11.939Z"
  },
  "charged": 8.3
},
{
  "timestamp": {
    "$date": "2024-05-19T00:00:16.270Z"
  },
  "value": "charging_active",
  "charged": 8.400001
}

Based on these documents, ignoring any preceding events, I should have the result “day1: 8.3” and “day2: 0.1” since that session is spanning across the day.

Thank you in advance,
Alex

Alexandru_Ionescu · 2024-06-14T15:59:57.274Z

Hello again. Does anybody have a clue on how this could be accomplished, what direction to follow? We have been working with MongoDB aggregates for more than a month and we continue to learn so many new things, but the sheer number of operators is quite overwhelming.

Any clues or ideas, @Peter_Hubbard ?

Thanks,
Alex