SQL language support overview
Developers who know relational databases will find the learning curve for time-series database (TSDB) SQL syntax nearly flat. TDengine supports standard SQL queries, inserts, and deletes, and extends them with features purpose-built for time-series workloads. Database creation, table creation, and data writing are the foundation most applications start from.
TSDBs add capabilities beyond standard SQL, including aggregation queries, downsampling, and interpolation queries. Developers can reuse existing SQL knowledge to get started quickly.
Database creation
Creating a database is the first step when working with TDengine. The standard statement is:
CREATE DATABASE IF NOT EXISTS power
The IF NOT EXISTS clause guarantees idempotency. If the database already exists, the statement does not throw an error. This is useful in automated deployments and scripted workflows.
You can specify several parameters when creating a database: data retention days (KEEP), number of replicas, cache size, and others. Setting these values correctly directly affects storage efficiency and query performance in a TSDB.
Table creation: supertables and subtables
TDengine uses a hierarchy of supertables (STABLE) and subtables, which defines its data model. The supertable is a template that defines the schema, and subtables are the actual storage units that hold the data.
Creating a supertable
The statement to create a supertable looks like this:
CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))
Here, power.meters uses the dbName.tableName format. The supertable contains one timestamp primary key, four data columns, and two tag columns.
Design considerations:
ts TIMESTAMP: The timestamp column is the primary key of the table. Every table must include it.current FLOAT,voltage INT,phase FLOAT: These are ordinary data columns that store the collected readings.TAGS (groupId INT, location BINARY(24)): Tag columns identify the data source. Tag values are fixed at the subtable level.- The supertable itself stores no data. It acts as a template. Data is written to and queried from subtables.
Subtable creation methods
There are two ways to create subtables.
Method 1: Create the subtable manually with a CREATE TABLE statement, then write data with INSERT.
Method 2: Use Automatic Table Creation syntax. Include the supertable name and tag values in the INSERT statement, and the system creates the subtable automatically. This approach is cleaner and faster, and it is the recommended practice.
| Dimension | Manual creation | Automatic Table Creation |
|---|---|---|
| Workflow | Two-step: CREATE TABLE, then INSERT | Single INSERT handles both |
| Code overhead | Requires a separate DDL statement per device | No DDL needed per device |
| IoT scalability | Breaks down with large, dynamic device fleets | Handles dynamic device additions naturally |
| Use case | Static tables with known schemas | IoT and industrial environments with many devices |
Data writing in detail
Automatic Table Creation with writes
The syntax for Automatic Table Creation with a write:
INSERT INTO power.d1001 USING power.meters TAGS(2,'California.SanFrancisco') VALUES (NOW + 1a, 10.30000, 219, 0.31000)
This single statement creates the subtable and writes data to it at the same time. It is a key feature that simplifies the development workflow for TSDBs.
Breakdown of the statement:
power.d1001: The target subtable name.USING power.meters: Specifies the parent supertable.TAGS(2,'California.SanFrancisco'): The tag values for this subtable.VALUES (...): The actual data row to write.
Time functions and time offsets
Flexible time expressions are a major strength of TSDB data writing. The NOW function returns the current time of the client computer. Supported time offsets include NOW + 1a (milliseconds), NOW + 1s (seconds), NOW + 1m (minutes), NOW + 1h (hours), NOW + 1d (days), NOW + 1w (weeks), NOW + 1n (months), and NOW + 1y (years).
Time unit reference: a (milliseconds), s (seconds), m (minutes), h (hours), d (days), w (weeks), n (months), y (years). This syntax is convenient for bulk-writing historical data or generating simulated datasets.
SQL writing best practices
Use the dbName.tableName format. Always reference tables with the full dbName.tableName format in SQL statements, rather than switching databases with USE dbName. This matters most in connection pool environments, where a USE statement can scramble the context across different requests.
Rely on Automatic Table Creation. In IoT scenarios where device counts are large and change over time, Automatic Table Creation simplifies application logic. It avoids the tedious work of pre-creating large numbers of subtables.
Choose data types carefully. TDengine supports FLOAT, INT, BINARY, NCHAR, and other data types. For tag columns, BINARY and NCHAR require a length specification, such as BINARY(24). In schemaless writing mode, if incoming data exceeds the defined column length, the system expands the column automatically.
Time-series query features
Beyond standard CRUD operations, TSDBs provide specialized time-series query capabilities:
- Aggregation queries: Apply statistical functions (average, max, min) to data within defined time windows.
- Downsampling: Reduce high-frequency data into coarser time windows, shrinking data volume while preserving overall trends.
- Interpolation queries: Fill in missing time points to maintain data continuity across gaps.
These capabilities set TSDBs apart from traditional relational databases and simplify time-series analysis work.
Summary
Knowing the SQL dialect of your TSDB is central to writing efficient data pipelines. TDengine TSDB’s SQL design stays compatible with standard SQL while addressing the needs of time-series data directly. The supertable/subtable model, Automatic Table Creation syntax, and flexible time expressions all reduce development complexity.
Stick to the dbName.tableName naming convention, prefer Automatic Table Creation when devices are numerous or changing, and use time offset expressions for flexible timestamp generation. These habits lead to cleaner, more reliable data processing workflows.
