In IoTDB query statements, two filter conditions, time filter and value filter, are supported.
The supported operators are as follows:
>
), greater than or equal ( >=
), equal ( =
or ==
), not equal ( !=
or <>
), less than or equal ( <=
), less than ( <
).AND
or &
or &&
), or ( OR
or |
or ||
), not ( NOT
or !
).IN
).LIKE
, REGEXP
.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:
Select data with timestamp greater than 2022-01-01T00:05:00.000:
select s1 from root.sg1.d1 where time > 2022-01-01T00:05:00.000;
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;
Select the data in the time interval [2017-11-01T00:05:00.000, 2017-11-01T00:12:00.000):
select s1 from root.sg1.d1 where time >= 2022-01-01T00:05:00.000 and time < 2017-11-01T00:12:00.000;
Note: In the above example, time
can also be written as timestamp
.
Use value filters to filter data whose data values meet certain criteria. Allow to use a time series not selected in the select clause as a value filter.
An example is as follows:
Select data with a value greater than 36.5:
select temperature from root.sg1.d1 where temperature > 36.5;
Select data with value equal to true:
select status from root.sg1.d1 where status = true;
Select data for the interval [36.5,40] or not:
select temperature from root.sg1.d1 where temperature between 36.5 and 40;
select temperature from root.sg1.d1 where temperature not between 36.5 and 40;
Select data with values within a specific range:
select code from root.sg1.d1 where code in ('200', '300', '400', '500');
Select data with values outside a certain range:
select code from root.sg1.d1 where code not in ('200', '300', '400', '500');
Use null filters to filter data whose data value is null or not.
Select data with values is null:
select code from root.sg1.d1 where temperature is null;
Select data with values is not null:
select code from root.sg1.d1 where temperature is not null;
Fuzzy query is divided into Like statement and Regexp statement, both of which can support fuzzy matching of TEXT type data.
Like statement:
Like
In the value filter condition, for TEXT type data, use Like
and Regexp
operators to perform fuzzy matching on data.
Matching rules:
%
) wildcard matches any string of zero or more characters._
) wildcard matches any single character.Example 1: Query data containing 'cc'
in value
under root.sg.d1
.
IoTDB> select * from root.sg.d1 where value like '%cc%' +-----------------------------+----------------+ | 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 It costs 0.002s
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_' +-----------------------------+----------------+ | Time|root.sg.d1.value| +-----------------------------+----------------+ |2017-11-01T00:00:02.000+08:00| abc| +-----------------------------+----------------+ Total line number = 1 It costs 0.002s
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 It costs 0.002s
Example 2: Query root.sg.d1 where the value value is a string composed of 26 lowercase English characters and the time is greater than 100
IoTDB> select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100 +-----------------------------+----------------+ | 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 It costs 0.002s