$group by 24 hour period form a given start date (as oppose to group by calendar day)?

Working with Data
1

Can anyone point me in the right direction for rolling up counts/sums into 24 hour periods?

e.g.

I have an IoT device that started collecting data at 1pm 9/1/2021 to 12:59pm on 9/7/2021.

I want to roll up and $sum the events not by calendar days, but by 24 hour periods starting at 1pm on 9/1/2021

Ending up with something like [{ day: ‘1’, value: 240 }, { day: ‘2’, value: 260 } , { day: ‘3’, value: 210 } ]

I know how to rollup data by the calendar day with $group, but haven’t approached it before from a calendar agnostic time interval with a given start date.

Any tips would be great.

2

Hi @Anthony_Comito ,

Yes you need to use the new $setWindowFields aggregation stage using 5.0 MongoDB servers:

See the example and change the unit to a day or use 24 hour.

Thanks
Pavel

3

That’s helpful @Pavel_Duchovny . I am getting a MongoServerError: unknown time unit value: 24hour

Is there an enum list I can see somewhere?

Are you part of the mongodb “flex consulting” offer? If not, could you put me in touch with somebody to setup an engagement? I’d like to make sure I get this correct and would like to speak to somebody who’s worked with $setWindowFields and IoT data

4

Hi @Anthony_Comito ,

I can contact to our consulting teams in the upcoming week.

You can also contact them via our consulting web page.

I think the unit can be only “hour” or “day” then in the boundaries you need to set -24 or -1 depending on the unit.

I suggest you read our newly posted timeSeries article :

It has an example of a rolling average with window fields.

Thanks
Pavel

5

You don’t need any fancy window functions here, you just need to group with a boundary that’s different than midnight. It turns out since date is stored as seconds since epoch you should be able to do this by creating appropriate boundaries yourself. Given your example to start on ISODate("2021-09-01T13:00:00Z") do this:

startDate = ISODate("2021-09-01T13:00:00Z");
db.rollup.aggregate(
    {$group:{_id:{$trunc:{$divide:[{ $subtract: ["$date", start] }, 86400000]}}, sum:{$sum:"$value"}}}
)

For each date it subtracts it from your given start date, and divides it by number of milliseconds in 24 hour period to get the “date” offset from your start. It uses that expression as the group key. Result might look like this:

[ { _id: 0, sum: 35 }, { _id: 1, sum: 15 } ]

Here 0 would be start date, 1 would be the next 24 hour period, etc. You can then use date arithmetic to convert it back to a timestamp (something like start + $_id * 86400000 convert $toDate).

Asya

4 Likes
6

How about I want to group it by one more field along with the time interval?