Advanced query in Narrow timescale

Overview

Advanced queries allow you to write custom PostgreSQL expressions for alert conditions on narrow table data. The system automatically detects which fields you reference and pivots the narrow table structure into a queryable wide format.

Basic Syntax

Reference fields using double quotes:

avg("temperature") + avg("pressure") * 0.5

The system will:

1. Extract temperature and pressure from your query
2. Detect which value column each uses (Value, value_str, or value_json)
3. Pivot the narrow table to create these columns
4. Execute your expression

max("value") + 10

These fields are directly available from the narrow table schema without pivoting:

• time
• gateway_id
• gateway
• source
• name
• tags
• value
• value_str
• value_json

Field Variables (Auto-Pivot)

Any field stored in the Name column that gets pivoted:

avg("temperature"), max("humidity")

Bracket Escaping [field]

Force a standard column name to be treated as a field variable:

avg([value])  -- Treats "value" as a field name, not the value column

Use when: A field in your data has the same name as a standard column.

Value Type Detection

Fields are stored across three columns in narrow tables:

• value - numeric data
• value_str - string data
• value_json - JSON data The system automatically detects which column contains each field by querying recent data. If detection fails, defaults to Value.

Aggregation Functions

When using aggregate functions (avg, max, min, sum, count, etc.), the system automatically aggregates time and tags columns to match your aggregation level. Without aggregation:

"temperature" + "humidity"  -- Returns all data points

With aggregation:

avg("temperature") + avg("humidity")  -- Returns single aggregated result

Limitations

• All referenced fields must exist in the time range being queried
• Mixing aggregated and non-aggregated expressions requires proper SQL syntax
• Field names are case-sensitive
• PostgreSQL/TimescaleDB SQL syntax only (not InfluxQL)

Examples

Simple arithmetic:

"pressure" - "baseline"

Aggregations:

max("temperature") - min("temperature")

Escape standard column:

avg([name])  -- If you have a field called "name"