intervalLengthSum

Syntax

Arguments

• — The starting value of the interval. Int32, , UInt32, , Float32, , DateTime or .
• end — The ending value of the interval. Int32, , UInt32, , Float32, , DateTime or .

Note

Arguments must be of the same data type. Otherwise, an exception will be thrown.

• Total length of union of all ranges (segments on numeric axis). Depending on the type of the argument, the return value may be UInt64 or type.

Examples

1. Input table:

┌─id─┬─start─┬─end─┐
│ a  │   1.1 │ 2.9 │
│ a  │   2.5 │ 3.2 │
└────┴───────┴─────┘

In this example, the arguments of the Float32 type are used. The function returns a value of the Float64 type.

Result is the sum of lengths of intervals (union of [1.1, 2.9] and [2.5, 3.2]) and [4, 5]

Query:

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM fl_interval GROUP BY id ORDER BY id;

1. Input table:

┌─id─┬───────────────start─┬─────────────────end─┐
│ a  │ 2020-01-01 01:12:30 │ 2020-01-01 02:10:10 │
│ a  │ 2020-01-01 03:11:22 │ 2020-01-01 03:23:31 │

In this example, the arguments of the DateTime type are used. The function returns a value in seconds.

Query:

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM dt_interval GROUP BY id ORDER BY id;

Result:

1. Input table:

┌─id─┬──────start─┬────────end─┐
│ a  │ 2020-01-01 │ 2020-01-04 │
│ a  │ 2020-01-12 │ 2020-01-18 │

In this example, the arguments of the Date type are used. The function returns a value in days.

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM date_interval GROUP BY id ORDER BY id;

Result: