notBetween/NOTBETWEEN

select col(s)
from table
where col notBetween value1 and value2

col(s) is column name(s) to select. It can be one or more column names or * (indicating all columns).

table is the table name to query.

col is the column to be filtered.

value1, value2 is any valid expression.value1 indicates the starting value, and value2 indicates the ending value. In general, value1 and value2 should have the same data type as col. When comparing date or datetime data, if the time granularities of value1 and value2 are finer than that of col, the system first converts both range bounds to the data type of col and then performs the range check. 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.

Query records where the price column values are not between 2 and 4:

timeCols = 2024.11.14+1..6
symCols = `APPL`AMZN`IBM`IBM`AAPL`AMZN
priceCols = 1.8 2.3 3.7 3.1 4.2 2.8

t = table(timeCols as time, symCols as sym, priceCols as price);
select * from t where price notBetween 2 and 4

// equivalent to select * from t where price notBetween 2:4
// equivalent to select * from t where price not between 2 and 4

Query all records where the date in the time column is not between 2024.11.16 and 2024.11.18.

select * from t where time notBetween 2024.11.16 and 2024.11.18

In the following example, tradeDate is of type DATE, and both range bounds are of type NANOTIMESTAMP. The system first converts the range bounds to DATE and then returns the records other than those for 2023.01.04.

t = table(2023.01.03..2023.01.05 as tradeDate)

select * from t
where tradeDate not between nanotimestamp(2023.01.04) and nanotimestamp(2023.01.04)