Duration of Overlapping Time Frames

[ThomasT]

Hi Community, 

I got a small problem at my hands and most of the relating discussions solve something slightly different and with very limited expression skills I'm at a loss here...I hope the example will also help others in the future. 

Problem: 

Calculate the duration of overlapping time frames. Eg. i got a random time frame (date+time) 'a' and a time frame 'b'. I've managed to determine IF they overlap (thanks @david_fetters  for the formula below), but I can't work out how to calculate how long for they overlap. 

#Base Formula from david_fetters to determine if the overlap:
IF (DateTimeDiff([Begin2],[Begin1],"seconds") > 0 &&
DateTimeDiff([Begin2],[End1],"seconds") < 0) ||
(DateTimeDiff([End2],[Begin1],"seconds") > 0 &&
DateTimeDiff([End2],[End1],"seconds") < 0) THEN 1 ELSE 0 ENDIF

#My formula 1 to determine overlap:
IF (DateTimeDiff([start_date_time_b],[start_date_time_a],"seconds") > 0 &&
DateTimeDiff([start_date_time_b],[end_date_time_a],"seconds") < 0) ||
(DateTimeDiff([end_date_time_b],[start_date_time_a],"seconds") > 0 &&
DateTimeDiff([end_date_time_b],[end_date_time_a],"seconds") < 0) THEN 1 ELSE 0 ENDIF

#My formula 2 to determine overlap (a swapped with b):
IF (DateTimeDiff([start_date_time_a],[start_date_time_b],"seconds") > 0 &&
DateTimeDiff([start_date_time_a],[end_date_time_b],"seconds") < 0) ||
(DateTimeDiff([end_date_time_a],[start_date_time_b],"seconds") > 0 &&
DateTimeDiff([end_date_time_a],[end_date_time_b],"seconds") < 0) THEN 1 ELSE 0 ENDIF

Start/End dates already formatted to proper datetime 'dd/MM/yyy hh:mm:ss' - Example attached.

start_date_time_a      | start_date_time_b      | end_date_time_a      | end_date_time_b
2021-01-14 11:55:49  |  2021-01-14 11:55:59 | 2021-01-14 11:56:52 | 2021-01-14 11:56:10

So for this example above, the two time frames would overlap between 11:55:59 until 11:56:10 and I would like to have an output like "11" (seconds). I should be simple maths to subtract start/end times, but I don't know how to write that in expression format.  Another issue is that either 'a' or 'b' can start first or end first hence i had to duplicate the formula above and swap a with b to capture all possible overlaps. 

Hope this makes sense. Looking forward to hear your thoughts and solutions. 

Cheers,

Thomas

[clmc9601 · Accepted solution]

Hi @ThomasT,

Would something like this help?

 

[ThomasT]

HI @clmc9601 

I'm still running on 2019.4, I've tried to import your package but it wouldn't let me open it. Is it possible to save it compatible to 2019.4?

Appreciate the quick feedback to my problem. 

Thanks,

Thomas

[clmc9601 · Accepted solution]

Hi @ThomasT,

If you open the file in a text editor (ie notepad), you can change the version compatibility! Here's a screenshot of how I did so for this file:

 

[ThomasT]

@clmc9601 first of all, thanks for the solution(s). It works really well, I wish i could come up with these kind of multi-row expressions myself...

I have tried to open your Alteryx package with the notepad, but it comes out completely corrupted and I guess it's because i'm trying to open the package with the notepad. I tried the version trick with a workflow file and that definitely works. 

Is there a way to un-zip the Alteryx package file with using Alteryx so that i can only open up the workflow file with Notepad? 

[clmc9601]

Hi @ThomasT,

You'll get there! 🙂  With practice, you'll get faster and more creative at thinking your way logically, piece by piece through the transformation process.

Sorry about the hassle. Here is the unzipped workflow which I edited to be compatible with 2019.4. 

Also, on my previous post, I edited the zip file to contain a workflow that should have been compatible. For some reason, my text editor can work inside the zip file. Did that version (the second one I posted) give you problems as well? Asking so I know for the future how best to make my solutions backward compatible. Thanks!

[ThomasT]

Many thanks @clmc9601

I just realized that my data example had no second values in there, simply because i opened in in excel shortly before i uploaded it...gotta hate excel for it's auto-formatting. 

Anyway, it's working perfectly with seconds as well, just had to change the datetime format. Workflow attached for those who are interested. 

Appreciate you help and quick feedback (and encouraging words) 🙂