Query Filter

The supported operators are as follows:

• Comparison operators: greater than (>), greater than or equal ( >=), equal ( = or ==), not equal ( != or <>), less than or equal ( <=), less than ( <).
• Range contains operator: contains ( IN ).
• Logical operators: and ( AND or & or &&), or ( OR or | or ||), not ( NOT or !).

Use time filters to filter data for a specific time range. For supported formats of timestamps, please refer to Timestamp .

An example is as follows:

1. Select data with timestamp greater than 2022-01-01T00:05:00.000:

2. Select data with timestamp equal to 2022-01-01T00:05:00.000:

   select s1 from root.sg1.d1 where time = 2022-01-01T00:05:00.000;

3. Select the data in the time interval [2017-11-01T00:05:00.000, 2017-11-01T00:12:00.000):

Note: In the above example, time can also be written as timestamp.

An example is as follows:

1. Select data with a value greater than 36.5:

2. Select data with value equal to true:

3. Select data for the interval (36.5,40]:

   select temperature from root.sg1.d1 where temperature > 36.5 and temperature < 40;

4. Select data with values within a specific range:

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

5. Select data with values outside a certain range:

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

Fuzzy query is divided into Like statement and Regexp statement, both of which can support fuzzy matching of TEXT type data.

In the value filter condition, for TEXT type data, use Like and Regexp operators to perform fuzzy matching on data.

Matching rules:

• The percentage (%) wildcard matches any string of zero or more characters.
• The underscore (_) wildcard matches any single character.

Example 1: Query data containing 'cc' in value under root.sg.d1.

Example 2: Query data that consists of 3 characters and the second character is 'b' in value under root.sg.d1.

IoTDB> select * from root.sg.device where value like '_b_'
+-----------------------------+----------------+
+-----------------------------+----------------+
|2017-11-01T00:00:02.000+08:00|             abc| 
Total line number = 1
It costs 0.002s

Fuzzy matching using Regexp

The filter conditions that need to be passed in are regular expressions in the Java standard library style.

Examples of common regular matching:

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

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

IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$'
+-----------------------------+----------------+
|                         Time|root.sg.d1.value|
+-----------------------------+----------------+
|2017-11-01T00:00:00.000+08:00|        aabbccdd| 
|2017-11-01T00:00:01.000+08:00|              cc|
+-----------------------------+----------------+
Total line number = 2