Determine whether two Date Ranges overlap and handle it

Let’s discuss the case of having these data below:


As you can see we have updated_at and utc_date columns.

In the second and in the third row we have a range overlap:

2018-02-28T23:02:59 -> 2018-02-28T23:03:16
2018-02-28T23:03:04 -> 2018-02-28T23:03:32

The ending date ends after the start of the utc_date on the second line.

Which is the best practice for handle this sort of date range overlap if I want for example to avoid the overlap?

Please someone that can provide me a multi-ways solution:

  • one for remove it the overlapping case

  • one for handle it without removing the row

Thanks in advance.

Depends on what date format you use.

Forgive me but I don’t need to calculate timediff in this case. I need a way to handle the overlapping cases between the next row values of utc_date column and the previous row value of updated_at column. Probably I misunderstood your reply, can you be more specific?

Thanks in advance.

Okay. I found by myself a work-around that in my opinion does the job in the right way.

I have calculated the timediff in seconds between utc_date and updated_at and th timediff in seconds for utc_date. Then I have applied the lag column node for the first timediff and then with rule-based row filter I have set the rule as below:

$timediff_1(-1)$ <= $timediff_2$=>TRUE


1 Like