How to filter by date intervals

A usual question we see in the OutSystems Forum and on projects, is how to search for events in a date interval. Because I answered it twice in September, it is time to turn it into views and write it down.

Although it is an easy task for a human, it is harder to explain to a computer. Let’s see how we can make it easy to program.

And because we just had ONE, we will use the dates of the conference as an example.

Big rule: Date is not the same as Date Time!

If we talk about hotel reservations, we have a start date and an end date. If we talk about a session you need to attend, it has a specific date and time.

You have a hotel reservation for September 11^th to 14^th. The check in page at the hotel will show everyone arriving on the 11^th that hasn’t checked in yet and they only need to search you by name.

Reservation.CheckInDate = CurrDate()

The same happens at checkout. On the 14^th they will see everyone booked to leave on the 14^th that hasn’t checked out yet and only need to search by name or room number.

Reservation.CheckOutDate = CurrDate()

Now imagine you are looking at the event agenda. The filter on top is by day and you see multiple events. The filter will be by day, even though the session has a datetime. The easiest way is to do

DateTimeToDate(Session.StartTime) = SelectedDay

so only the date part is considered, instead of a much more complex placement in the time slots of the day

Session.StartTime >= NewDateTime(Year(SelectedDay),Month(SelectedDay),Day(SelectedDay), 0, 0, 0)
and
Session.EndTime   >= NewDateTime(Year(SelectedDay),Month(SelectedDay),Day(SelectedDay),23,59,59)

Making sense so far?

The above example is filtering on a single date. Now, let’s see how to filter by interval.

Filtering by Start and End Date

The tricky part is to think in all possible combinations.

Event Start Date can be before/at/after Filter Start Date.

Event End Date can be before/at/after Filter End Date.

And of course, Event End can’t be before Event Start.

That makes 15 possible combinations that we can represent visually.

Making a filter with 15 rules is crazy, so we will do it with a small trick that can simplify this to only two filters.

If the rule is “part of the event is inside the interval” the trick is to notice that most cases fit into the interval.

Only events that ended before Start Date or that start after End Date are to be ignored.

So the perfect filter is as simple as

Event.StartDate <= FilterEndDate and Event.EndDate >= FilterStartDate

If the rule is “all the event is inside the interval” the valid combinations are less.

And the filter is almost the same.

Event.StartDate >= FilterStartDate and Event.EndDate <= FilterEndDate

You may need to adapt it a bit to include the DateTimeToDate like explained before, but it is just a small detail. Having this “two filters mindset” you can do anything.