How to count overlapping time slot usage ("10:00-11:00")? With $group or $bucket?

Lets say I want to have on a given day slots like:

[ { from: '09:00', till: '10:00' }, { from: '11:00', till: '12:00' } ]

There are existing appointments in DB, some may not be aligned to the official slots, but overlapping:

[ { room: 'A', from: '09:00', till: '10:00' }, { room: 'B': from: '09:15', till: '10:15' }, { room: 'C', from: '11:30', till: '12:30' } ]

Is there a way to get aggregated data for the desired slots like this?

[ { from: '09:00', till: '10:00', booked: 2 },  { from: '11:00', till: '12:00', booked: 1} ]

Note: we use from and till with real dates, just wanted to make it easier to read.

Is there a way with $group or $bucket to realize this with overlapping timestamps?

I have linked your other post here since I feel they are somewhat related.

The difficulty for your use-case is the way you store your availability and reservation schedule. Yes it is very nice to define time schedules with from/till but storing it likewise make it hard. In those circumstances, I do not use this time of model. I first determine the granularity of the available resources. In your case it looks like it is time slots of 15 minutes as seen here:

I give the UI the possibility to define availability like you do,

but what I store is an array of 8 entries, one for each 15 minutes time slots like:

slots : [
  { time: 09:00 , booked : null }
  { time : 09:15 , booked : null }
  { time : 09:30 , booked : null }
  { time : 09:45 , booked : null }
  { time : 11:00 , booked : null }
  { time : 11:15 , booked : null }
  { time : 11:30 , booked : null }
  { time : 11:45 , booked : null }
]

When a booking comes I simply update the booked field with the appropriate booking_id. It is now trivial to find what is booked and what is not booked. For example a reservation for the given resource from 9:15 to 9:45 would result in:

slots : [
  { time : 09:00 , booked : null }
  { time : 09:15 , booked : booking_id_369 }
  { time : 09:30 , booked : booking_id_369 }
  { time : 09:45 , booked : booking_id_369 }
  { time : 11:00 , booked : null }
  { time : 11:15 , booked : null }
  { time : 11:30 , booked : null }
  { time : 11:45 , booked : null }
]

Note that in reality, I do not stored booked:null, I prefer to leave the field missing for space efficiency.

I know this does not answer your question, but it gives some ideas and revive your 4 days old posts.

3 Likes

@steevej Thanks for your feedback! In general it seems like a good approach. My challenge is that the time slots are configurable per tenant, so I can’t guarantee the granularity and it may be changed later on.

I was really looking for some aggregation tricks (or should I say magic?) to count appointments that fall within the pre-defined time slots.