Use the CREATE CONTINUOUS QUERY statement - Lindorm

A continuous query (CQ) runs automatically on time series data in LindormTSDB at a fixed interval, aggregating recent data and writing the results to a destination table. Use CQs to downsample high-frequency data for long-term storage without manual intervention.

Applicable engines and versions

CREATE CONTINUOUS QUERY applies to all versions of LindormTSDB.

Syntax

create_cq_statement ::= CREATE CONTINUOUS QUERY [database_identifier.] cq_identifier
                         WITH ( cq_attribute_statement )
                         AS insert_select_statement

cq_attribute_statement ::= attribute_definition (',' attribute_definition)*
attribute_definition   ::= attr_identifier '=' attr_val

How it works

Each CQ runs on a fixed schedule controlled by three attributes:

`interval` — how often the CQ runs (required)
`window` — the time range of source data each run covers (defaults to interval)
`offset` — shifts the execution time relative to the UTC epoch (defaults to no shift)

At each scheduled execution, LindormTSDB determines the time range automatically based on these attributes and writes the aggregated results to the destination table. The write statement does not require an explicit time filter.

Parameters

database_identifier

The name of the database that owns the CQ. If omitted, the current database is used. Enclose the name in backticks. Example: ` db_sensor `.

cq_identifier

The name of the CQ. Enclose the name in backticks. Example: ` my_cq `.

cq_attribute_statement

Attributes that control how the CQ runs:

Attribute	Type	Required	Default	Description
`interval`	STRING	Yes	—	How often the CQ runs. The interval value can be accurate to seconds. Format: `%d%h%m%s` (d=days, h=hours, m=minutes, s=seconds). Example: `1h30s` = 1 hour 30 seconds.
`window`	STRING	No	Same as `interval`	The time range of source data each run covers. The window is inclusive of the start time and exclusive of the end time. Example: `window='20m'` with `interval='10m'` means each run covers the past 20 minutes of data.
`offset`	STRING	No	No offset (aligned to `1970-01-01 00:00:00 UTC`)	Shifts the execution time relative to the UTC epoch. Use this to align runs with a specific local time. Example: `interval='1d'` with `offset='16h'` runs the daily CQ at 00:00 UTC+8 (Beijing time).

insert_select_statement

The write statement that defines the aggregation logic and destination. Key constraints:

Do not include a time filter in the SELECT clause. LindormTSDB derives the time range from the CQ attributes automatically.
The number of columns in the SELECT clause must match the destination table, including all fields and tags.
For the full syntax, see Write data.

Example

The following CQ runs every hour and aggregates the past two hours of sensor data into hourly averages.

CREATE CONTINUOUS QUERY `default`.`my_cq`
WITH (`INTERVAL`='1h', `WINDOW`='2h')
AS
INSERT INTO `default`.`sensor`
SELECT AVG(`temperature`) AS `temperature`,
       AVG(`humidity`)    AS `humidity`,
       `device_id`,
       `region`
FROM   `default`.`sensor`
SAMPLE BY 1h;

What this CQ does at each run:

Calculates the average temperature and humidity, grouped by device_id and region.
Writes the results back to the sensor table in the default database.