TDengine has supported standard SQL queries since its first release, giving developers a familiar way to work with time-series data.
1. Why SQL for time-series data
1.1 Low learning curve
For developers already familiar with relational databases, querying a time-series database (TSDB) with SQL requires little additional learning. Standard SELECT statements, WHERE conditions, ORDER BY sorting, and other familiar syntax work directly in TDengine.
1.2 Ecosystem compatibility
As the most widely used database query language, SQL has a rich tool ecosystem: direct connections from BI tools, broad ORM framework support, and mature query optimizers. Many of these tools can work with TDengine with little or no modification.
1.3 What TDengine adds to SQL
On top of standard SQL, TDengine adds capabilities designed for time-series data: native time-window queries, a wide range of time-series aggregate functions, and fast time-range filtering.
2. Basic query syntax
2.1 Simple queries
SELECT * FROM meters;
2.2 Conditional filtering
SELECT * FROM meters WHERE voltage > 230 ORDER BY ts DESC LIMIT 5;
This query retrieves records where voltage exceeds 230V from the meters Supertable, sorted by timestamp in descending order, returning only the first five rows.
2.3 Time-range queries
SELECT * FROM meters
WHERE ts >= "2022-01-01T00:00:00+08:00"
AND ts < "2023-01-01T00:00:00+08:00";
3. Query clauses in detail
3.1 SELECT clause
The SELECT clause specifies which columns to return and supports aggregate functions and expressions.
SELECT ts, current, voltage FROM meters;
SELECT COUNT(*), AVG(voltage) FROM meters;
SELECT ts, current * voltage AS power FROM meters;
3.2 WHERE clause
The WHERE clause filters data using comparison operators, logical operators, time ranges, and Tag-based conditions.
3.3 ORDER BY clause
The ORDER BY clause sorts results. It supports ascending order by time (the default), descending order by time, and multi-column sorting.
3.4 LIMIT and OFFSET
Pagination is done with LIMIT and OFFSET:
<em>-- These two statements are equivalent</em>
SELECT * FROM meters LIMIT 10 OFFSET 10;
SELECT * FROM meters LIMIT 10, 10;
4. Supertable queries
4.1 How Supertable queries work
TDengine treats all Subtable data under a Supertable as a single logical dataset. It first uses Tag-based filtering to identify which tables satisfy the conditions, then queries the time-series data within those Subtables individually, and finally merges the results.
4.2 Supertable query example
SELECT groupid, AVG(voltage)
FROM meters
WHERE ts >= "2022-01-01T00:00:00+08:00"
AND ts < "2023-01-01T00:00:00+08:00"
GROUP BY groupid;
This returns each groupid value along with the corresponding average voltage.
4.3 The tbname pseudo-column
SELECT tbname, * FROM meters WHERE voltage > 230 LIMIT 5;
The tbname pseudo-column returns the source Subtable name for each row in the result set.
5. Common query scenarios
Latest data point:
SELECT * FROM d1001 ORDER BY ts DESC LIMIT 1;
Time-period statistics: Use COUNT, AVG, MAX, and other aggregate functions.
Device group statistics: Group by Tag values, such as grouping by location.
Anomaly detection queries:
SELECT * FROM meters WHERE voltage > 250 OR voltage < 180;
6. Query performance tips
6.1 Narrow by time range first
Always use a time-range condition in time-series queries. TDengine is optimized for time-range filtering.
6.2 Use LIMIT sensibly
For large result sets, use LIMIT to restrict the number of rows returned.
6.3 Avoid SELECT *
Query only the columns you need. This reduces data transfer between storage and the query layer.
7. Generating test data
Use taosBenchmark to generate test data:
taosBenchmark \
--start-timestamp=1600000000000 \
--tables=100 \
--records=10000000 \
--time-step=10000
This command generates 1 billion time-series data points: timestamps starting from 1600000000000, across 100 devices (Subtables), with 10 million records per device at a 10-second collection interval.
Summary
TDengine’s SQL support gives developers a familiar way to work with time-series data. Standard SQL syntax means you can start querying immediately without learning a new query language. TDengine also adds time-series-specific capabilities for time-range queries, aggregate calculations, Supertable queries, and test-data generation.
