How do you compare a date range with a list of date ranges?

Need help
1

What’s the best way to handle date range comparisons in lists?

Basically, I need to compare if a date range DOES NOT OVERLAP with any other range in a list of date ranges.

Currently, Bubble only uses the “Overlaps with” opperator, but what I want is “Does not overlap with” and it seems that this comparison isn’t possible, even with the existing operator “Overlaps with” it seems impossible to compare a date range with a list of date ranges.

I searched other threads and it seems this problem is already old.

Could someone suggest any solution? A plugin, something like that?

2

Some questions which help decide on the best implementation …

  1. Is it sufficient to rearrange the comparison to (search list (list item range overlaps comparison range)):count is zero
  2. What will the app do with the comparison result?
  3. Will the comparison happen on backend or frontend?
  4. How large is the list of date ranges?
  5. Will most comparisons find an overlapping date range?
  6. Is it ok for the implementation to load the entire list, or process one at a time and stop when an overlap is found?

Here’s a javascript approach to a similar-ish question: Toolbox example servscr-merge-dates

Edited simple answer (1 above)

3

Thanks for the explanation @mishav but I’m still confused.

Let me try to give a better example.

Well, it’s not my case, but let’s take an example of a rental application. One with a system a little bit different from Airbnb.

Let’s say I have a list of rental locations where each one has a range of available dates. And these dates are stored in fields like this:

  • Start (date)
  • End (date)
  • Interval (date range)

Let’s call this list of available locations as List A.

Now imagine that the user makes some reservations at some of these places and that we save a reference to these reservations along with other information (which is not relevant here).

Let’s call this list of booked locations as List B.

Now imagine that I want to filter and hide from the list of available locations (List A) all those locations where the availability range overlaps with some range in the list of booked locations (List B).

Something like:

Search for List A where interval (date range) field overlaps with List B interval (date range) field

That’s the problem, I can’t use those operators when comparing a list of ranges with another list of ranges.

And answering your questions:

  1. I didn’t quite understand this approach
  2. I need the return to be a list of objects or at least a list of unique IDs.
  3. I need it to be done on the frontend, but I’m open to suggestions.
  4. I would say that currently List A might contain a few dozen entries, maybe hundreds, but hardly thousands. List B would be even smaller, dozens, never hundreds.
  5. Possibly, but not always.
  6. The entire list needs to be loaded.
4

@Lumyna here’s an inefficient solution using search, :minus list, and advanced filter.

image
image684×266 23.9 KB

image
image339×208 10.3 KB

It will likely be costly in WU, repeating the advanced filter for every entry in List A.

If you were okay with switching to a workflow-driven approach, i.e. refresh data on demand instead of auto-update when changed in the database … I think that setting partial results in Custom States, or a JS filter, would save on WU.

1 Like