MSQL Reference – Range Functions

fn RANGE

RANGE(start: T1, end: T2) -> range[T]

Constructs a range from its elements.

Arguments

The arguments must be either both numeric or both timepoints (timestamp or unixepoch).

Return type

The function returns a range of a common super-type of the given arguments.

Examples

SELECT RANGE(10, 20);
  --> [10, 20]

SELECT RANGE(10, 20.5);
  --> [10, 20.5]

SELECT RANGE(unixepoch '2021-01-01', unixepoch '2021-07-01');
  --> [1609459200, 1625097600]

fn LBOUND

LBOUND(r: range[T]) -> T

Returns the lower (left) bound of the given range.

Examples

SELECT LBOUND(RANGE(10, 20));
  --> 10

SELECT LBOUND(RANGE(20, 10));
  --> 10

See also

RBOUND RSPAN

fn RBOUND

RBOUND(r: range[T]) -> T

Returns the upper (right) bound of the given range.

Examples

SELECT RBOUND(RANGE(10, 20));
  --> 20

SELECT RBOUND(RANGE(20, 10));
  --> 20

See also

LBOUND RSPAN

fn RDISTANCE

RDISTANCE(r1: range[T], r2: range[T]) -> T

Returns the signed distance between two ranges.

Notes

If the given ranges intersect, the result will be 0. Otherwise, the function will return the signed distance from the first range to the second.

Examples

SELECT RDISTANCE(RANGE(0, 10), RANGE(30, 40));
  --> 20

SELECT RDISTANCE(RANGE(40, 20), RANGE(-10, -30));
  --> -30

SELECT RDISTANCE(RANGE(0, 20), RANGE(10, 30));
  --> 0

See also

<-> (DISTANCE)

fn REXPAND

REXPAND(r: numericrange, delta: T) -> numericrange

REXPAND(r: rtimestamp | runixepoch, delta: timeperiod) -> rtimestamp

Expands the range bounds by delta.

Notes

The function both decreases the lower bound and increases the upper bound of the range by delta.

The value of delta can be negative, in which case the range bounds will be contracted.
If the absolute value of delta (when it's negative) is larger than RSPAN(r) / 2, the upper and lower bounds of the range will be swapped, e.g. REXPAND(RANGE(0, 10), -10) -> [10,0].

Examples

SELECT REXPAND(RANGE(10, 20), 5);
  --> [5, 25]

SELECT REXPAND(RANGE(20, 10), 5);
  --> [25, 5]

SELECT REXPAND(RANGE(20, 10), -15);
  --> [5, 25]

fn RNORMALIZE

RNORMALIZE(r: range[T]) -> range[T]

Returns a normalized range so that the lower bound of the result is the first element, and the upper bound is the second element.

Examples

SELECT RNORMALIZE(RANGE(20, 10));
  --> [10, 20]

SELECT RNORMALIZE(RANGE(0, 10));
  --> [0, 10]

See also

LBOUND RBOUND RANGE

fn RSPAN

RSPAN(r: intXrange) -> int64

RSPAN(r: floatXrange) -> float64

RSPAN(r: rtimestamp | runixepoch) -> timeperiod

Returns the span of the given range, i.e. r[1] - r[0].

Notes

The result of the function can be negative.

Examples

SELECT RSPAN(RANGE(10, 40));
  --> 30

SELECT RSPAN(RANGE(40, 10));
  --> -30

fn RVALID

RVALID(r: range[T]) -> range[T]

Returns whether a given range is valid.

Notes

Only temporal ranges can be invalid, if their end point is before their start point, i.e. if r[1] < r[0].

Examples

SELECT RVALID(RANGE(20, 10));
  --> true

SELECT RVALID(tsrange '[2021-01-01, 2020-12-01]');
  --> false

See also

RNORMALIZE

fn DURATION

DURATION(r: timerange) -> timeperiod

Alias for the RSPAN function.

Examples

SELECT DURATION(rtimestamp '[2021-01-01 08:00,2021-01-01 15:30]');
  --> 27000

SELECT DURATION(repoch '[2022-01-15 08:00,2022-01-16 08:00]');
  --> 86400

fn LERP

LERP(r: range[T], p: float64) -> T

LERP(r: range[T], tr: rtimestamp, t: timestamp) -> T

LERP(r0: T, r1: T, p: float64) -> T

LERP(r0: T, r1: T, tr: rtimestamp, t: timestamp) -> T

Linearly interpolates/extrapolates the value of the given range at the given position.

Notes

This function is not yet implemented.

fn MIDPOINT

MIDPOINT(r: range[T]) -> T

Returns the midpoint of the given range.

Notes

This function is not yet implemented.