between
The between operator selects values within a given range where begin and
end values are included. It is used with keyword and, which is equivalent to the
function between.
Syntax
select col(s)
from table
where col [not] between value1 and value2Compare Date and Datetime Types
When comparing date or datetime data using col between value1 and
value2, if col does not match the data type of the range bounds
and the time granularity of value1 and value2 is finer than that of
col, the system first converts both range bounds to the data type of
col and then performs the range check.
Automatic conversion can only convert range bounds 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.
Examples
t = table(`APPL`AMZN`IBM`IBM`AAPL`AMZN as sym, 1.8 2.3 3.7 3.1 4.2 2.8 as price);
select * from t where price between 2 and 4
// equivalent to `select * from t where price between 2:4`| sym | price |
|---|---|
| AMZN | 2.3 |
| IBM | 3.7 |
| IBM | 3.1 |
| AMZN | 2.8 |
$ select * from t where sym between `A and `H| sym | price |
|---|---|
| APPL | 1.8 |
| AMZN | 2.3 |
| APPL | 4.2 |
| AMZN | 2.8 |
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 queries the records corresponding to 2023.01.04.
t = table(2023.01.03..2023.01.05 as tradeDate)
select * from t
where tradeDate between nanotimestamp(2023.01.04) and nanotimestamp(2023.01.04)| tradeDate |
|---|
| 2023.01.04 |