Time-Series Database Window Queries: A Guide to the Five Window Types

Window query overview

A window query partitions a data set by windows and aggregates the data within each window. This approach is useful for analyzing large volumes of time-series data. For example, a smart meter collects readings every 10 seconds, but the requirement is to query the average temperature at 1-minute intervals. A window query handles this naturally.

TDengine supports five window types: time windows (fixed-interval aggregation), state windows (device status monitoring), session windows (user behavior analysis), event windows (event-driven analysis), and count windows (fixed sample-size analysis).

Time windows

Syntax

The INTERVAL clause specifies the size and offset of each time window. The SLIDING clause defines how far the window advances forward in time. The FILL clause controls how missing data within a window interval is handled.

Time units

TDengine supports the following time units: a (milliseconds), b (nanoseconds), d (days), h (hours), m (minutes), n (months), s (seconds), u (microseconds), w (weeks), and y (years).

Tumbling windows

When SLIDING equals INTERVAL, the result is a tumbling window, where consecutive windows do not overlap. The following example queries the average voltage from table d2 using 1-minute tumbling windows between 00:00 and 00:05 on 2022-01-01:

SELECT AVG(voltage) FROM d2
WHERE ts >= '2022-01-01 00:00:00' AND ts < '2022-01-01 00:05:00'
INTERVAL(1m);

Sliding windows

Sliding windows overlap with each other. Using INTERVAL(1m) SLIDING(30s), each window covers 1 minute of data and advances by 30 seconds:

SELECT AVG(voltage) FROM d2
WHERE ts >= '2022-01-01 00:00:00' AND ts < '2022-01-01 00:05:00'
INTERVAL(1m) SLIDING(30s);

FILL clause

The FILL clause specifies how to handle windows where no data is present. The supported modes are:

• NONE: Do not fill (default).
• VALUE: Fill with a fixed value.
• NULL: Fill with NULL.
• PREV: Fill with the value from the previous window.
• NEXT: Fill with the value from the next window.
• LINEAR: Fill using linear interpolation.

Timestamp pseudo-columns

Window aggregate query results expose the following pseudo-columns:

• _wstart: Window start time.
• _wend: Window end time.
• _wduration: Window duration.
• _qstart: Query window start time.
• _qend: Query window end time.

These are available in the SELECT list and can be used in combination with aggregate functions.

State windows

A state window partitions data based on changes in a device status value. Rows with the same status value are grouped into one window, and the window closes when the status value changes. The syntax is STATE_WINDOW(col).

Common applications include equipment operating state change analysis, temperature range statistics, and alarm status duration tracking.

Session windows

A session window groups records based on their timestamp gaps. Records whose timestamp difference is less than a specified threshold belong to the same session. The syntax is SESSION(ts_col, tol_val).

Session windows are a good fit for user session analysis, device activity period statistics, and consecutive event analysis.

Event windows

An event window dynamically partitions data based on a start condition and an end condition. The syntax is EVENT_WINDOW START WITH start_trigger_condition END WITH end_trigger_condition.

Typical uses include temperature threshold exceedance analysis, device anomaly period statistics, and tracking events triggered by specific conditions.

Count windows

A count window partitions data by the number of rows. Each window contains a fixed number of rows, and aggregation is performed per window. The syntax is COUNT_WINDOW(count_val[, sliding_val]).

Count windows handle tasks like computing statistics every 100 records and performing sliding analysis with a fixed sample size.

Window query rules

1. The window clause appears after the data partitioning clause and cannot be used together with GROUP BY.
2. The SELECT list can contain only constants, pseudo-columns (_wstart, _wend, _wduration), and aggregate functions.
3. The WHERE clause can specify the query time range and other filter conditions.
4. When using INTERVAL, configure the client and server timezone parameters consistently.

Window query best practices

Choosing the right window type:

Use case	Recommended window type
Fixed-interval statistics	Time window
Status change analysis	State window
User session analysis	Session window
Event-driven analysis	Event window
Fixed sample analysis	Count window

Performance tuning: Set reasonable window sizes to avoid excessive computation. Use PARTITION BY together with window queries to increase parallelism. Combine time range conditions to reduce the data volume processed.

Common issues: Use the FILL clause to handle missing data. Understand the difference between sliding and tumbling windows. Ensure timezone configuration is consistent between client and server.

Conclusion

Window queries are a core technique for time-series data analysis. TDengine provides five window types, covering fixed-interval statistics, status changes, sessions, event-driven analysis, and fixed-size samples. Used well, window queries support efficient downsampling, trend analysis, and anomaly detection without moving data out of the database.