between

between(X, Y)

Check if each element of X is between the pair indicated by Y (both boundaries are inclusive). The result is of the same dimension as X.

When X and Y are both date or datetime types, but their data types do not match, if the time granularity of Y is finer than that of X, the system first converts Y to the data type of X and then performs the comparison. This automatic conversion can only move from finer-grained time types to coarser-grained time types; reverse conversion is not supported. MONTH cannot be used as the target type for automatic conversion. If the conditions above are not met, the system throws an exception for inconsistent temporal types.

X is a scalar/pair/vector/matrix.

Y is a pair indicating a range.

BOOL type with the same data form as X.

between([1, 5.5, 6, 8], 1:6);
// output: [1,1,1,0] // 1, 5.5 and 6 are between 1 and 6, but 8 is not.

between(1 2.4 3.6 2 3.9, 2.4:3.6);
// output: [0,1,1,0,0]

between can be used with select to filter columns:

t = table(`abb`aac`aaa as sym, 1.8 2.3 3.7 as price);
select * from t where price between 1:3;

The following example explains how null values of Y are handled.

If the configuration parameter nullAsMinValueForComparison is set to true, null values are treated as the minimum value of the current data type. Otherwise null values are processed as NULL and the comparison returns NULL.

between can also be used with template nullCompare, and the result of comparison involving null values is always NULL, regardless of the nullAsMinValueForComparison setting.

between(10,:10)
// output: true      // when nullAsMinValueForComparison=true

between(10,:10)
// output: NULL     // when nullAsMinValueForComparison=false

nullCompare(between, 10, :10)
// output: NULL

In the following function, X is of type DATE, and Y is of type NANOTIMESTAMP. Because NANOTIMESTAMP is finer-grained than DATE, the system first converts Y to DATE and then performs the range check.

between(2023.01.04, nanotimestamp(2023.01.04):nanotimestamp(2023.01.04))
// Output: true

Automatic conversion is one-way. In the following function, the specified range bounds are of type DATE. Because their time granularity is coarser than that of the compared NANOTIMESTAMP value, the system will not convert X in reverse, so an error is raised.

between(nanotimestamp(2023.01.04), 2023.01.04:2023.01.04)
// Usage: between(X, Y). Temporal data comparison should have the same data type.