Overview

Syntax Definition

In IoTDB, SELECT statement is used to retrieve data from one or more selected time series. Here is the syntax definition of SELECT statement:

  1. [TRACING?] SELECT 
  2.     [LAST?] selectExpr (, selectExpr)*
  3.     <fromClause> FROM prefixPath (, prefixPath)*
  4.     <whereClause?> WHERE queryFilter
  5.     <orderByTimeClause?> ORDER BY TIME [ASC | DESC]
  6.     <paginationClause?> [LIMIT | SLIMIT] INT [OFFSET | SOFFSET] INT
  7.     <groupByLevelClause?> GROUP BY LEVEL = INT
  8.     <groupByTimeClause?> GROUP BY ([startTime, endTime), slidingStep)
  9.     <fillClause?> FILL ([PREVIOUS, beforeRange | LINEAR, beforeRange, afterRange | constant])
  10.     <withoutNullClause?> WITHOUT NULL [ANY | ALL]
  11.     <alignClause?> [ALIGN BY DEVICE | DISABLE ALIGN]

The most commonly used clauses of SELECT statements are these:

  • Each selectExpr indicates a column that you want to retrieve, which may be a suffix of time series paths, an aggregate function and so on. There must be at least one selectExpr. For more details for selectExpr, please refer to Select Expression .
  • fromClause contains the prefix of one or more time-series paths to query.
  • whereClause (Optional) specify the filter criterion named queryfilter. queryfilter is a logical expression that returns the data points which calculation result is TRUE. If you do not specify whereClause, return all data points in the time series. For more details, please refer to Query Filter.
  • The query results are sorted in ascending order by timestamp. You can specify the results to be sorted in descending order by timestamp through ORDER BY TIME DESC clause.
  • When there is a large amount of query result data, you can use LIMIT/SLIMIT and OFFSET/SOFFSET to paginate the result set, see Query Result Pagination for details.
  • The query result set is aligned according to the timestamp by default, that is, the time series is used as the column, and the timestamp of each row of data is the same. For other result set alignments, see Query Result Alignment.

Basic Examples

Select a Column of Data Based on a Time Interval

The SQL statement is:

  1. select temperature from root.ln.wf01.wt01 where time < 2017-11-01T00:08:00.000

which means:

The selected device is ln group wf01 plant wt01 device; the selected timeseries is the temperature sensor (temperature). The SQL statement requires that all temperature sensor values before the time point of “2017-11-01T00:08:00.000” be selected.

The execution result of this SQL statement is as follows:

  1. +-----------------------------+-----------------------------+
  2. |                         Time|root.ln.wf01.wt01.temperature|
  3. +-----------------------------+-----------------------------+
  4. |2017-11-01T00:00:00.000+08:00|                        25.96|
  5. |2017-11-01T00:01:00.000+08:00|                        24.36|
  6. |2017-11-01T00:02:00.000+08:00|                        20.09|
  7. |2017-11-01T00:03:00.000+08:00|                        20.18|
  8. |2017-11-01T00:04:00.000+08:00|                        21.13|
  9. |2017-11-01T00:05:00.000+08:00|                        22.72|
  10. |2017-11-01T00:06:00.000+08:00|                        20.71|
  11. |2017-11-01T00:07:00.000+08:00|                        21.45|
  12. +-----------------------------+-----------------------------+
  13. Total line number = 8
  14. It costs 0.026s

Select Multiple Columns of Data Based on a Time Interval

The SQL statement is:

  1. select status, temperature from root.ln.wf01.wt01 where time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000;

which means:

The selected device is ln group wf01 plant wt01 device; the selected timeseries is “status” and “temperature”. The SQL statement requires that the status and temperature sensor values between the time point of “2017-11-01T00:05:00.000” and “2017-11-01T00:12:00.000” be selected.

The execution result of this SQL statement is as follows:

  1. +-----------------------------+------------------------+-----------------------------+
  2. |                         Time|root.ln.wf01.wt01.status|root.ln.wf01.wt01.temperature|
  3. +-----------------------------+------------------------+-----------------------------+
  4. |2017-11-01T00:06:00.000+08:00|                   false|                        20.71|
  5. |2017-11-01T00:07:00.000+08:00|                   false|                        21.45|
  6. |2017-11-01T00:08:00.000+08:00|                   false|                        22.58|
  7. |2017-11-01T00:09:00.000+08:00|                   false|                        20.98|
  8. |2017-11-01T00:10:00.000+08:00|                    true|                        25.52|
  9. |2017-11-01T00:11:00.000+08:00|                   false|                        22.91|
  10. +-----------------------------+------------------------+-----------------------------+
  11. Total line number = 6
  12. It costs 0.018s

Select Multiple Columns of Data for the Same Device According to Multiple Time Intervals

IoTDB supports specifying multiple time interval conditions in a query. Users can combine time interval conditions at will according to their needs. For example, the SQL statement is:

  1. select status,temperature from root.ln.wf01.wt01 where (time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000) or (time >= 2017-11-01T16:35:00.000 and time <= 2017-11-01T16:37:00.000);

which means:

The selected device is ln group wf01 plant wt01 device; the selected timeseries is “status” and “temperature”; the statement specifies two different time intervals, namely “2017-11-01T00:05:00.000 to 2017-11-01T00:12:00.000” and “2017-11-01T16:35:00.000 to 2017-11-01T16:37:00.000”. The SQL statement requires that the values of selected timeseries satisfying any time interval be selected.

The execution result of this SQL statement is as follows:

  1. +-----------------------------+------------------------+-----------------------------+
  2. |                         Time|root.ln.wf01.wt01.status|root.ln.wf01.wt01.temperature|
  3. +-----------------------------+------------------------+-----------------------------+
  4. |2017-11-01T00:06:00.000+08:00|                   false|                        20.71|
  5. |2017-11-01T00:07:00.000+08:00|                   false|                        21.45|
  6. |2017-11-01T00:08:00.000+08:00|                   false|                        22.58|
  7. |2017-11-01T00:09:00.000+08:00|                   false|                        20.98|
  8. |2017-11-01T00:10:00.000+08:00|                    true|                        25.52|
  9. |2017-11-01T00:11:00.000+08:00|                   false|                        22.91|
  10. |2017-11-01T16:35:00.000+08:00|                    true|                        23.44|
  11. |2017-11-01T16:36:00.000+08:00|                   false|                        21.98|
  12. |2017-11-01T16:37:00.000+08:00|                   false|                        21.93|
  13. +-----------------------------+------------------------+-----------------------------+
  14. Total line number = 9
  15. It costs 0.018s

Choose Multiple Columns of Data for Different Devices According to Multiple Time Intervals

The system supports the selection of data in any column in a query, i.e., the selected columns can come from different devices. For example, the SQL statement is:

  1. select wf01.wt01.status,wf02.wt02.hardware from root.ln where (time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000) or (time >= 2017-11-01T16:35:00.000 and time <= 2017-11-01T16:37:00.000);

which means:

The selected timeseries are “the power supply status of ln group wf01 plant wt01 device” and “the hardware version of ln group wf02 plant wt02 device”; the statement specifies two different time intervals, namely “2017-11-01T00:05:00.000 to 2017-11-01T00:12:00.000” and “2017-11-01T16:35:00.000 to 2017-11-01T16:37:00.000”. The SQL statement requires that the values of selected timeseries satisfying any time interval be selected.

The execution result of this SQL statement is as follows:

  1. +-----------------------------+------------------------+--------------------------+
  2. |                         Time|root.ln.wf01.wt01.status|root.ln.wf02.wt02.hardware|
  3. +-----------------------------+------------------------+--------------------------+
  4. |2017-11-01T00:06:00.000+08:00|                   false|                        v1|
  5. |2017-11-01T00:07:00.000+08:00|                   false|                        v1|
  6. |2017-11-01T00:08:00.000+08:00|                   false|                        v1|
  7. |2017-11-01T00:09:00.000+08:00|                   false|                        v1|
  8. |2017-11-01T00:10:00.000+08:00|                    true|                        v2|
  9. |2017-11-01T00:11:00.000+08:00|                   false|                        v1|
  10. |2017-11-01T16:35:00.000+08:00|                    true|                        v2|
  11. |2017-11-01T16:36:00.000+08:00|                   false|                        v1|
  12. |2017-11-01T16:37:00.000+08:00|                   false|                        v1|
  13. +-----------------------------+------------------------+--------------------------+
  14. Total line number = 9
  15. It costs 0.014s

Order By Time Query

IoTDB supports the ‘order by time’ statement since 0.11, it’s used to display results in descending order by time. For example, the SQL statement is:

  1. select * from root.ln.** where time > 1 order by time desc limit 10;

The execution result of this SQL statement is as follows:

  1. +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
  2. |                         Time|root.ln.wf02.wt02.hardware|root.ln.wf02.wt02.status|root.ln.wf01.wt01.temperature|root.ln.wf01.wt01.status|
  3. +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
  4. |2017-11-07T23:59:00.000+08:00|                        v1|                   false|                        21.07|                   false|
  5. |2017-11-07T23:58:00.000+08:00|                        v1|                   false|                        22.93|                   false|
  6. |2017-11-07T23:57:00.000+08:00|                        v2|                    true|                        24.39|                    true|
  7. |2017-11-07T23:56:00.000+08:00|                        v2|                    true|                        24.44|                    true|
  8. |2017-11-07T23:55:00.000+08:00|                        v2|                    true|                         25.9|                    true|
  9. |2017-11-07T23:54:00.000+08:00|                        v1|                   false|                        22.52|                   false|
  10. |2017-11-07T23:53:00.000+08:00|                        v2|                    true|                        24.58|                    true|
  11. |2017-11-07T23:52:00.000+08:00|                        v1|                   false|                        20.18|                   false|
  12. |2017-11-07T23:51:00.000+08:00|                        v1|                   false|                        22.24|                   false|
  13. |2017-11-07T23:50:00.000+08:00|                        v2|                    true|                         23.7|                    true|
  14. +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
  15. Total line number = 10
  16. It costs 0.016s

Usage in Different Clients

Data query statements can be used in SQL command-line terminals, JDBC, JAVA / C++ / Python / Go and other native APIs, and RESTful APIs.

  • Execute the query statement in the SQL command line terminal: start the SQL command line terminal, and directly enter the query statement to execute, see SQL command line terminal.

  • Execute query statements in JDBC, see JDBC for details.

  • Execute query statements in native APIs such as JAVA / C++ / Python / Go. For details, please refer to the relevant documentation in the Application Programming Interface chapter. The interface prototype is as follows:

    1. SessionDataSet executeQueryStatement(String sql)

  • Used in RESTful API, see HTTP API for details.