Overlapping timestamp

[FMG]

Hello,

I have a bunch of events stored on a DB, all of which contain a start time and end time in two different columns. The events have different types, which are also stored in a column. For a given time range, I would like to get how many time of events type A are overlaping with events type B and how many time events of type A are not overlapping with any other event type. 

Here is a simplified example ( in my real data, I have thousands of rows and I need to be accurate to the seconds). 

Events	Type	Start time	End time
1	B	02:00:00	04:00:00
2	B	04:00:00	05:00:00
3	A	04:30:00	05:30:00
4	B	05:30:00	07:30:00
5	A	07:25:00	07:55:00
6	B	07:50:00	08:50:00
7	B	08:50:00	09:00:00
8	B	09:00:00	10:00:00

In this I would like to get the Overlapping A on B from 02:00:00 to 10:00:00 and A non overlapping with any other event.

I would get : 40 minutes overlapping A on B and  50 minutes non overlapping

Any clue how I could get that done? I have tried manipulating the data with multi-row formula, but it gets really tricky and I don't believe I will be able to get to a solution efficienctly. 

[BenMoss · Accepted solution]

Here is a starting point (attached).

The generate rows tool is your friend here (I say this too much, but it's bloody useful when working with ranges!).

I actually get '47' minutes overlap. 

This is because each of your time slots actually shares a minute too.

i.e. 16:00-17:00 and 17:00-18:00 share a minute.

[FMG]

Hi Ben,

Thanks for your quick response.

This will definitely help.

[david_fetters]

Depending on how spread out the data set is, using Create Records may not work perfectly, especially if your time scale includes seconds.

I posted about the DateTimeDiff function and how you can use it to figure out if there is overlap between two time periods here:

https://community.alteryx.com/t5/Data-Preparation-Blending/Best-way-to-find-concurrent-DateTime-rang...

[Hiblet]

I had a similar problem and wrote a macro for it.  See "TimeMatch" on the public gallery.  You pass in two datastreams that have timestamp fields, and give the macro a tolerance timespan.  The tolerance defines what you mean by "at the same time" ie within this many seconds.  This works with fractions of a second to allow for very precise matching.