Missing/overlapping time intervals

Summary

This operator takes a Durations table (see Types of Tables) as input table and compares its intervals to a set of intervals defined in another data node (interval node, see below). The interval node specifies valid intervals for each day of the week.

Its result table compares these two tables in different ways, depending on the settings. It can further allocate intervals of the input table to intervals of the interval node according to identifiers.

Example

Situation	We want to know how our work time records (input table) overlap with our core working hours (interval node). We have two groups of employees, office staff and production workers. The table below is our input table containing the work time records (data node A01 in project file below). It indicates the employee group in column A and the actual working intervals in columns B and C. Column D is not required, it is added for a better understanding of later examples. The next table is our interval table containing the core working hours (data node I01 in project file below). Note: The columns "From" ("Von") and "To" ("Bis") only specify times, not dates. Their creation date is added per default because the Date/Time format requires a date. Hence, in this table, the dates "06.04.2017" are ignored, only the times are relevant. The columns C to J indicate on which days of the week the intervals are valid. Column K indicates for which employee group the intervals are valid.
Settings	We set the columns A of the input table and K of the interval table as identifiers to allocate the data rows to the employee groups (operation in data node C01). We choose "Overlapping intervals (first)" for the parameter "Result" (German "Überlappende Intervalle" for "Ergebnis). Further, we specify the interval node "I01 Intervals" with its columns "From" ("Spalte Intervallzeit von"), "To" ("Spalte Intervallzeit bis") , and the columns indicating the validities per weekday. ("Spalten Wochentage").
Result	The result table contains one row for each overlap between an interval of the input table with an interval of the interval table with matching identifiers (data node C01). All fields of these rows of both tables are repeated. Additionally, the two columns "Result From" and "Result To" are added to indicate the overlap.
Project File	Confluence missing%2foverlapping op.gzip

Interval node and holiday node

The interval node is a different data node in the same project. It defines time intervals with a validity on specific days of the week and for holidays. The time intervals of the input table are compared to the intervals valid on the respective day.

The interval node must contain the following columns:

Name convention	Description	Type	Optional/required	Remark
From	Start time of the interval	Time	Required	Only the time is specified and relevant, but the creation date is added automatically. The date is ignored for all functionality of this operator.
To	End time of the interval	Time	Required	Only the time is specified and relevant, but the creation date is added automatically. The date is ignored for all functionality of this operator.
Mo	Validity of interval on Mondays	Boolean	Required
Tue	Validity of interval on Tuesdays	Boolean	Required
Wed	Validity of interval on Wednesdays	Boolean	Required
Thu	Validity of interval on Thursdays	Boolean	Required
Fri	Validity of interval on Fridays	Boolean	Required
Sat	Validity of interval on Saturdays	Boolean	Required
Sun	Validity of interval on Sundays	Boolean	Required
Holiday	Validity of interval on holidays	Boolean	Optional	Holidays are specified in a separate data node.

The order of the Boolean columns is compulsory.

The time in the "From" column can be later than or equal to the time in the "To" column. This way, intervals spanning over two days can be defined. In these cases, different interpretations are possible. The options can be chosen by setting the parameters "Same From and To time means" and "Interval 23:00-06:00 means", respectively.

Further, the interval table may contain one or more columns of identifiers. They can be used to distinguish, e.g., working times for different groups of employees.

Holidays are listed explicitly in a different data node. This data node's result table must contain a Date column. The data node and the date column in the data node are specified in the operator's parameters "Holidays data node" and "Holiday column", respectively.

Classification of interval relations

An interval specified in the input table can have one or more of the following relations to an interval specified in the interval table.

English name

German name

Description

Example

Overlapping intervals

Überlappende Intervalle

The input interval overlaps with an interval from the interval table

Indicates the overlap.

Interval 17:00 to 19:00 from the input table overlaps with the interval 14:00 to 18:00 from the interval table. The overlap is from 17:00 to 18:00.

Missing intervals

Fehlende Intervalle

The input interval overlaps only with parts of an interval from the interval table or it does not overlap at all with an interval of the interval table.

Indicates (part of) the not-overlapped interval from the interval table.

In row 1, the input interval 10:00 to 13:00 does not overlap the interval 05:00 to 09:00 from the interval table. The whole interval 05:00 to 09:00 is missed by the input interval.

In row 5, the input interval 04:00 to 08:00 overlaps only parts of interval 05:00 to 09:00 from the interval table. It misses 08:00 to 09:00

Times not included in intervals

Zeiten kommen in Intervallen nicht vor

The input interval overlaps only with parts of an interval from the interval table or it does not overlap at all with an interval of the interval table.

Indicates (part of) the not-overlapped interval from the input table.

In row 3, the interval 4:00 to 5:00, which is part of the input interval 4:00 to 8:00 does not overlap with any interval from the interval table.

Settings

This operator computes overlaps between a Durations table (see Types of Tables) and a set of intervals defined in the interval node (see above). The settings define the (parts of the) input table, the (parts of the) interval table and the holiday table, and the columns and calculations in the result table.

Columns of input table

Parameters

Result options and result table

Independently of the result option, the result table contains

all columns of the input table in their order,
all columns of the interval table in their order,
one DateTime column "Result From", and
one DateTime column " Result To"

in this order.

The value of the parameter "Result" decides which rows of the input table are contained in the result table, with which rows of the interval table they are combined, and the information shown in the "Result From" and "Result To" columns.

Overlapping (first)

These option computes the intervals and parts of intervals where data from the input table and data from the interval table overlap.

The result table contains one row for each overlap between the input intervals and the intervals of the interval table.

It contains more than one row for one input interval, if this interval overlaps with more than one interval of the interval table.

However, if an input interval overlaps with more than one interval of the interval table and these intervals of the interval tables have the same "From" time and the same "To" time, then only the overlap with the first interval, with respect to the ordering of the rows in the interval table, is contained in the result table.

The result table copies all fields of the respective row in the input table and all fields of the row in the interval table that contains the interval it overlaps with. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the overlap.

See also "Overlapping intervals" in the classification of interval relations above. Table below shows result table of data node C02 of project file.

Overlapping (all)

These option computes the intervals and parts of intervals where data from the input table and data from the interval table overlap.

The result table contains one row for each overlap between the input intervals and the intervals of the interval table.

It contains more than one row for one input interval, if this interval overlaps with more than one interval of the interval table, regardless of the double intervals having the same "From" and "To" times (this is the difference to the option Overlapping (first)).

The result table copies all fields of the respective row in the input table and all fields of the row in the interval table that contains the interval it overlaps with. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the overlap.

See also "Overlapping intervals" in the classification of interval relations above. Table below shows result table of data node C03 of project file. Note row 2 of this table, which is not included in the table computed with option "Overlapping (first)" above.

Missing intervals (view by row)

This option computes the intervals and parts of intervals of the interval table not overlapped by intervals of the input table. It considers for each entry in the input table all intervals valid for that day.

The result table contains one row for each input interval and interval of the interval table valid for the respective day and not at all or not completely overlapped by the input interval. It hence contains more than one row for one input interval if there is more than one interval in the interval table valid on the day which the input interval does not overlap.

The result table copies all fields of the respective rows of both the input table and the interval table. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) interval that is not overlapped by the input data on the respective day.

See also "Missing intervals" in the classification of interval relations above. Table below shows result table of data node C04 of project file.

Missing intervals (all)

This option computes intervals and parts of intervals of the interval table not overlapped by intervals of the input table, like option "Missing intervals (view by row). The difference is that it considers for each day between and including the earliest and the latest day of the input table each interval valid on that day.

The result table contains one row for each interval of the interval table for each day for which it is valid between the earliest and the latest day of the input table where it is not completely covered by an interval of the input table.

The result table fills all fields of the input table with "-" and copies those of the interval table. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) interval that is not overlapped by the input interval on the respective day.

See also "Missing intervals" in the classification of interval relations above. Table below shows result table of data node C05 of project file.

Times not included in intervals

This option computes intervals of the input table not overlapped by intervals of the interval table (the inverse of option "Missing intervals (view by row)").

The result table contains one row for each input interval that is not fully overlapped by an interval of the interval table. It hence contains at most one row for each row of the input table. It copies the rows of the input table and fills all fields of the interval table with "-". The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) input interval not overlapped by an interval of the interval table.

See also "Times not included in intervals" in the classification of interval relations above. Table below shows result table of data node C06 of project file.

Classification of times

This option considers each row of the input intervals and classifies its interval according to the classifications described above ("Classification of interval relations").

The result table contains one row for each input interval and its classification(s). Hence, if an input interval falls into more than one classification, then the result table contains more than one row for this interval. The "Result From" and "Result To" columns contain the start and the end time of different intervals depending on its classification:

Overlapping intervals (Überlappende Intervalle): Start time and end time of the overlap inside the interval of the interval table.
Missing intervals (Fehlende Intervalle): Start time and end time of the non-overlap inside the interval of the interval table.
Times not included in intervals (Zeiten kommen in Intervallen nicht vor): Start time and end time of the non-overlap inside the input interval.

Table below shows result table of data node C07 of project file.

Examples

Introduction

The following examples refer to the following interval table, holiday table, and input table, unless indicated otherwise. The examples are contained in this project and can be imported into TIS. Confluence missing%2foverlapping op.gzip

Interval table (data node "Interval tables/I01 Intervals", dates blanked out for better readability as they are not relevant)

Holiday table (data node "Interval tables/I02 Holidays")

Input table (data node "Raw data/A02 simple with missing days"

Overlap (first)

Data node	Settings	Result	Remarks
Examples/E02			"Result From" and "Result To" fields indicate the overlap between the columns B and C (input interval) and columns E and F (interval from interval table).

Overlap (all)

Data node

Settings

Result

Remarks

Examples/E03

"Result From" and "Result To" fields indicate the overlap between the columns B and C (input interval) and columns E and F (interval from interval table).

Rows 1 and 2 contain an interval from the interval table with the same "From" and "To" times, both valid on Wednesday. Other than for option "Overlap (first)", here they are both included in the result table.