Overlapping Dates

KNIME Analytics Platform
1

Hi everybody,

I'm a beginner in Knime and would like to manage the overlapping of several dates.

I have 2 columns like that, the first one for startdate and the second one for enddate (in Date/Calendar format) :

[startdate, endate]

01/07/2012 06/07/2012
06/07/2012 12/11/2012
01/10/2012 11/11/2012
12/11/2012 12/11/2012
02/07/2012 17/09/2012
26/03/2013 31/12/2013
01/07/2012 31/01/2013

I have calculated some time operations like (startdate(i)-startdate(i+1)),(enddate(i)-enddate(i+1)) and (startdate(i)-enddate(i)) with the available (time)nodes in Knime, in order to deduce an algorithm. But no way.

Do you have already be faced to this problem ?

Thanks in advance,

Ahmed

2

Hi Ahmed,

what was the goal of your calculation? Could you give an example of the expected outcome?

 

Cheers, Iris

 

3

Hi Iris,

Thank you for your quick reply.

The main goal is to identify if there is an overlap between each dates intervals and to extract the duration of the eventual overlap.

Like these 2 dates intervals :

06/07/2012 12/11/2012
01/10/2012 11/11/2012 
		<p>There is an overlap (a boolean = TRUE/FALSE), in this case it&#39;s TRUE. And&nbsp;to obtain the duration in days of the overlapping, in this case is 41 days.</p>
		</td>
	</tr>
	<tr>
		<td>
		<p>Cheers,</p>

		<p>Ahmed</p>
		</td>
		<td>&nbsp;</td>
	</tr>
</tbody>

 

4

Hi,

 

so that's not easy... I wold use the Lag Column node on both date columns.

so you will achieve in each row the previous and consequent interval.

For detecting an overlap you need to check different szenarios.

startdate1<startdate2 AND enddate1 > startdate2

OR

startdate1>startdate2 And enddate2 > startdate1

Should output if they overlap.

for calculating the overlap you also need to check if one interval is included in the other.

Iris

5

I think if sorting the data by startdates is allowed, you only have to check if the previous row's enddate is greater than the current row's. (So Sorter, Lag and probably Rule Engine or Java Snippet.) I assume startdate < enddate for all rows.

If the goal is to compute all row's overlap, I think the best option would be to implement a distance function for time intervals (or date collections with the assumption of two values there). That way, you will get a (half) matrix of overlapping values.

6

Thank you guys, your advices help me a lot.

Cheers,

Ahmed