Comparison Operators and Functions

Basic comparison operators

Supported operators >, >=, <, <=, ==, != (or <> )

Supported input data types: INT32, INT64, FLOAT and DOUBLE

Note: It will transform all type to DOUBLE then do computation.

Output data type: BOOLEAN

Example:

  1. select a, b, a > 10, a <= b, !(a <= b), a > 10 && a > b from root.test;
  1. IoTDB> select a, b, a > 10, a <= b, !(a <= b), a > 10 && a > b from root.test;
  2. +-----------------------------+-----------+-----------+----------------+--------------------------+---------------------------+------------------------------------------------+
  3. |                         Time|root.test.a|root.test.b|root.test.a > 10|root.test.a <= root.test.b|!root.test.a <= root.test.b|(root.test.a > 10) & (root.test.a > root.test.b)|
  4. +-----------------------------+-----------+-----------+----------------+--------------------------+---------------------------+------------------------------------------------+
  5. |1970-01-01T08:00:00.001+08:00|         23|       10.0|            true|                     false|                       true|                                            true|
  6. |1970-01-01T08:00:00.002+08:00|         33|       21.0|            true|                     false|                       true|                                            true|
  7. |1970-01-01T08:00:00.004+08:00|         13|       15.0|            true|                      true|                      false|                                           false|
  8. |1970-01-01T08:00:00.005+08:00|         26|        0.0|            true|                     false|                       true|                                            true|
  9. |1970-01-01T08:00:00.008+08:00|          1|       22.0|           false|                      true|                      false|                                           false|
  10. |1970-01-01T08:00:00.010+08:00|         23|       12.0|            true|                     false|                       true|                                            true|
  11. +-----------------------------+-----------+-----------+----------------+--------------------------+---------------------------+------------------------------------------------+

BETWEEN ... AND ... operator

operatormeaning
BETWEEN … AND …within the specified range
NOT BETWEEN … AND …Not within the specified range

Example: Select data within or outside the interval [36.5,40]:

  1. select temperature from root.sg1.d1 where temperature between 36.5 and 40;
  1. select temperature from root.sg1.d1 where temperature not between 36.5 and 40;

Fuzzy matching operator

For TEXT type data, support fuzzy matching of data using Like and Regexp operators.

operatormeaning
LIKEmatches simple patterns
NOT LIKEcannot match simple pattern
REGEXPMatch regular expression
NOT REGEXPCannot match regular expression

Input data type: TEXT

Return type: BOOLEAN

Use Like for fuzzy matching

Matching rules:

  • % means any 0 or more characters.
  • _ means any single character.

Example 1: Query the data under root.sg.d1 that contains 'cc' in value.

  1. IoTDB> select * from root.sg.d1 where value like '%cc%'
  2. +--------------------------+----------------+
  3. | Time|root.sg.d1.value|
  4. +--------------------------+----------------+
  5. |2017-11-01T00:00:00.000+08:00| aabbccdd|
  6. |2017-11-01T00:00:01.000+08:00| cc|
  7. +--------------------------+----------------+
  8. Total line number = 2
  9. It costs 0.002s

Example 2: Query the data under root.sg.d1 with 'b' in the middle of value and any single character before and after.

  1. IoTDB> select * from root.sg.device where value like '_b_'
  2. +--------------------------+----------------+
  3. | Time|root.sg.d1.value|
  4. +--------------------------+----------------+
  5. |2017-11-01T00:00:02.000+08:00|abc|
  6. +--------------------------+----------------+
  7. Total line number = 1
  8. It costs 0.002s

Use Regexp for fuzzy matching

The filter condition that needs to be passed in is Java standard library style regular expression.

Common regular matching examples:

  1. All characters with a length of 3-20: ^.{3,20}$
  2. Uppercase English characters: ^[A-Z]+$
  3. Numbers and English characters: ^[A-Za-z0-9]+$
  4. Starting with a: ^a.*

Example 1: Query the string of 26 English characters for value under root.sg.d1.

  1. IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$'
  2. +--------------------------+----------------+
  3. | Time|root.sg.d1.value|
  4. +--------------------------+----------------+
  5. |2017-11-01T00:00:00.000+08:00| aabbccdd|
  6. |2017-11-01T00:00:01.000+08:00| cc|
  7. +--------------------------+----------------+
  8. Total line number = 2
  9. It costs 0.002s

Example 2: Query root.sg.d1 where the value is a string consisting of 26 lowercase English characters and the time is greater than 100.

  1. IoTDB> select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100
  2. +--------------------------+----------------+
  3. | Time|root.sg.d1.value|
  4. +--------------------------+----------------+
  5. |2017-11-01T00:00:00.000+08:00| aabbccdd|
  6. |2017-11-01T00:00:01.000+08:00| cc|
  7. +--------------------------+----------------+
  8. Total line number = 2
  9. It costs 0.002s

Example 3:

  1. select b, b like '1%', b regexp '[0-2]' from root.test;

operation result

  1. +-----------------------------+-----------+------- ------------------+--------------------------+
  2. | Time|root.test.b|root.test.b LIKE '^1.*?$'|root.test.b REGEXP '[0-2]'|
  3. +-----------------------------+-----------+------- ------------------+--------------------------+
  4. |1970-01-01T08:00:00.001+08:00| 111test111| true| true|
  5. |1970-01-01T08:00:00.003+08:00| 333test333| false| false|
  6. +-----------------------------+-----------+------- ------------------+--------------------------+

IS NULL operator

operatormeaning
IS NULLis a null value
IS NOT NULLis not a null value

Example 1: Select data with empty values:

  1. select code from root.sg1.d1 where temperature is null;

Example 2: Select data with non-null values:

  1. select code from root.sg1.d1 where temperature is not null;

IN operator

operatormeaning
IN / CONTAINSare the values ​​in the specified list
NOT IN / NOT CONTAINSnot a value in the specified list

Input data type: All Types

return type BOOLEAN

**Note: Please ensure that the values ​​in the collection can be converted to the type of the input data. **

For example:

s1 in (1, 2, 3, 'test'), the data type of s1 is INT32

We will throw an exception because 'test' cannot be converted to type INT32

Example 1: Select data with values ​​within a certain range:

  1. select code from root.sg1.d1 where code in ('200', '300', '400', '500');

Example 2: Select data with values ​​outside a certain range:

  1. select code from root.sg1.d1 where code not in ('200', '300', '400', '500');

Example 3:

  1. select a, a in (1, 2) from root.test;

Output 2:

  1. +-----------------------------+-----------+------- -------------+
  2. | Time|root.test.a|root.test.a IN (1,2)|
  3. +-----------------------------+-----------+------- -------------+
  4. |1970-01-01T08:00:00.001+08:00| 1| true|
  5. |1970-01-01T08:00:00.003+08:00| 3| false|
  6. +-----------------------------+-----------+------- -------------+

Condition Functions

Condition functions are used to check whether timeseries data points satisfy some specific condition.

They return BOOLEANs.

Currently, IoTDB supports the following condition functions:

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeSeries Data Type Description
ON_OFFINT32 / INT64 / FLOAT / DOUBLEthreshold: a double type variateBOOLEANReturn ts_value >= threshold.
IN_RANGRINT32 / INT64 / FLOAT / DOUBLElower: DOUBLE type
upper: DOUBLE type		BOOLEANReturn ts_value >= lower && value <= upper.

Example Data:

  1. IoTDB> select ts from root.test;
  2. +-----------------------------+------------+
  3. |                         Time|root.test.ts|
  4. +-----------------------------+------------+
  5. |1970-01-01T08:00:00.001+08:00|           1|
  6. |1970-01-01T08:00:00.002+08:00|           2|
  7. |1970-01-01T08:00:00.003+08:00|           3|
  8. |1970-01-01T08:00:00.004+08:00|           4|
  9. +-----------------------------+------------+
Test 1

SQL:

  1. select ts, on_off(ts, 'threshold'='2') from root.test;

Output:

  1. IoTDB> select ts, on_off(ts, 'threshold'='2') from root.test;
  2. +-----------------------------+------------+-------------------------------------+
  3. |                         Time|root.test.ts|on_off(root.test.ts, "threshold"="2")|
  4. +-----------------------------+------------+-------------------------------------+
  5. |1970-01-01T08:00:00.001+08:00|           1|                                false|
  6. |1970-01-01T08:00:00.002+08:00|           2|                                 true|
  7. |1970-01-01T08:00:00.003+08:00|           3|                                 true|
  8. |1970-01-01T08:00:00.004+08:00|           4|                                 true|
  9. +-----------------------------+------------+-------------------------------------+
Test 2

Sql:

  1. select ts, in_range(ts, 'lower'='2', 'upper'='3.1') from root.test;

Output:

  1. IoTDB> select ts, in_range(ts,'lower'='2', 'upper'='3.1') from root.test;
  2. +-----------------------------+------------+--------------------------------------------------+
  3. |                         Time|root.test.ts|in_range(root.test.ts, "lower"="2", "upper"="3.1")|
  4. +-----------------------------+------------+--------------------------------------------------+
  5. |1970-01-01T08:00:00.001+08:00|           1|                                             false|
  6. |1970-01-01T08:00:00.002+08:00|           2|                                              true|
  7. |1970-01-01T08:00:00.003+08:00|           3|                                              true|
  8. |1970-01-01T08:00:00.004+08:00|           4|                                             false|
  9. +-----------------------------+------------+--------------------------------------------------+