notBetween

notBetween(X, Y)

Check if each element of X is outside the range specified by Y.

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 (both boundaries are inclusive).

A Boolean scalar or vector/matrix of the same dimension as X.

notBetween([1, 5.5, 6, 8], 1:6);
// output: [false,false,false,true]

notBetween(1 2.4 3.6 2 3.9, 2.4:3.6);
// output: [true,false,false,true,true]

notBetween can be used with SQL SELECT tocheck whether values fall outside the specified range:

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

notBetweencan also be applied to queries on DFS tables:

login(`admin,`123456)
dbName="dfs://database1"
if(existsDatabase(dbName)){
	dropDatabase(dbName)
}
db=database(dbName,VALUE,2019.01.01..2019.01.03)
n=100
datetime=take(2019.01.01 +0..100,n)
sym = take(`C`MS`MS`MS`IBM`IBM`IBM`C`C$SYMBOL,n)
price= take(49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29,n)
qty = take(2200 1900 2100 3200 6800 5400 1300 2500 8800,n)
t=table(datetime, sym, price, qty)
trades=db.createPartitionedTable(t,`trades,`datetime).append!(t)

select * from trades where qty notBetween 1300:6800

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.

notBetween(2023.01.04, nanotimestamp(2023.01.04):nanotimestamp(2023.01.04))
// Output: false

Related function: between