GS SQL™ — Temporal Entity Query (TEQ™)

Template Tables	Stream Columns	System Columns	Temporal Parameters	vs SQL:2011	FK JOINs	Diagrams
Grammar - DDL	Supported SQL	Limitations	Security	AI Agent Guidance	Examples

Overview

Temporal Entity Query (TEQ) lets you query using component templates as SQL tables, streams as columns, and components as rows. Use your template ID as the table name and your stream IDs as column names — no complex self-JOINs required.

The mapping is straightforward: each component template becomes a virtual table, each stream ID defined in the template becomes a column, and each component linked to that template becomes a row. By default, stream columns return the _last_value for each component.

TEQ supports the full GS SQL grammar — all the same clauses, functions, operators, aggregates, and SQL features — but uses template tables instead of system tables and stream IDs instead of the _sample column. See the Grammar Reference for a complete listing of supported grammar, and the GS SQL Overview for conventions, data types, and general information.

Prerequisites

• Component templates must be defined with stream IDs in your organization.
• Components must be linked to those templates (each component's _template_uid references the template).
• For FK JOINs: streams must be configured as foreign keys with a target template reference (fk_target_template).

Template Tables

In the FROM clause, use your component template ID as the table name. The GS SQL engine matches the table name (case-insensitive) against your organization's component template IDs. If a match is found, the query runs in TEQ mode; if no match is found, it falls back to TDQ mode (system tables).

SELECT _component_name, temperature FROM sensor

In this example, sensor is a component template ID. The engine resolves it, discovers the template's streams (e.g., temperature, humidity), and produces one row per component linked to that template.

How it works: The engine internally rewrites the query into an equivalent system table query. You never need to write the rewrite yourself — the engine handles it automatically.

Table aliases work as expected:

SELECT s._component_name, s.temperature FROM sensor s WHERE s.temperature > 30

Stream Columns

Use stream IDs as column names in your SELECT, WHERE, ORDER BY, and other clauses. By default (without temporal parameters), each stream column returns the _last_value for that stream on each component.

The engine automatically casts values to the appropriate GS SQL data type based on each stream's configured value type:

Stream Value Type	GS SQL Type
DOUBLE, FLOAT	DOUBLE
LONG, INTEGER, SHORT, DATETIME	LONG
BIG_DECIMAL	BIGDECIMAL
BOOLEAN	BOOLEAN
STRING	STRING

Column reference syntax:

Syntax	Description
temperature	Stream column — returns _last_value
s.temperature	Qualified with table alias
temperature(range(last=100))	With temporal parameters — returns multiple rows per component
s.temperature(range(sd=-1d))	Qualified with alias and temporal parameters

Component System Columns

Every template table automatically includes the following system columns. These are always available regardless of which streams are defined in the template. In template diagrams, system columns are hidden by default — click the display system columns toggle to show them (they appear grayed out to distinguish them from stream columns):

Column	Type	Description
_component_name	String	Component name
_component_uid	String	Component UID (unique identifier)
_component_id	String	Component ID (programmatic identifier)
_component_created_date	Long	Component creation date (epoch milliseconds)
_folder_path	String	Folder path containing the component

When any stream column uses temporal parameters such as range(), the following additional columns become available. These are sample-level columns, not component properties, and do not appear in template diagrams:

Column	Type	Description
_time	Long	Sample timestamp (epoch milliseconds). For regular streams, equals the sample date. For interval streams, equals the interval end date (same as _time_ed).
_time_sd	Long	Interval sample start date (epoch milliseconds). For regular streams, equals _time. For interval streams, the start of the interval.
_time_ed	Long	Interval sample end date (epoch milliseconds). For regular streams, equals _time. For interval streams, the end of the interval.

Per-Column Temporal Parameters

In TEQ queries, temporal parameters are applied directly to each stream column by name. All standard temporal parameters from the Grammar Reference apply — Range, CycleId, Stat, TimeFilterId, GapFill, and Window parameters (lag, lead, slide/slidestat, running) — just use them on individual stream columns:

Example	Description
SELECT _component_name, temperature(range(last=100)) FROM sensor	Last 100 samples per component
SELECT _component_name, kwh(cycleId='day', stat='avg') FROM meter	Daily average cycle aggregation
SELECT _component_name, kwh(range(sd=-1d)), voltage FROM meter	Mixed temporal and _last_value columns
SELECT _component_name, temperature(lag=1, range(last=100)) FROM sensor	Previous temperature value (window parameter)
SELECT _component_name, kwh(running='sum', range(currentCycle='month')) FROM meter	Cumulative kWh total (running aggregate)

When temporal parameters are used on any stream column, the query switches from returning one row per component (_last_value mode) to returning multiple rows per component (one per sample).

Mixed mode: You can mix temporal columns (with temporal parameters) and _last_value columns (without parameters) in the same query. Columns without temporal parameters will repeat their _last_value on each row:

SELECT _component_name, kwh(range(sd=-1d)), voltage FROM meter

Here kwh returns multiple time-series rows while voltage repeats its _last_value on each row.

The _time, _time_sd, and _time_ed columns are available when temporal parameters are used. _time provides the epoch millisecond timestamp of each sample. For interval streams, _time_sd and _time_ed provide the interval start and end dates:

SELECT _component_name, formatDate(_time) AS 'time', temperature(range(last=10)) FROM sensor

Per-Column Temporal Parameters vs SQL:2011

The ISO SQL:2011 standard introduced temporal query syntax with FOR SYSTEM_TIME AS OF and FOR BUSINESS_TIME FROM ... TO .... These operate at the table or row level — they select a temporal slice of the entire row at a point in time or over a range. This is the right model for versioned records: "show me this customer record as it was on January 1st."

GS SQL's per-column temporal parameters solve a different problem. In time-series and temporal intelligence workloads, each property (column) on an entity often has its own independent time axis. A meter's kwh stream may report every 15 minutes while its voltage stream reports every second. A customer's rate changes quarterly while their region changes annually. SQL:2011's row-level temporal model cannot express "give me the last 100 kWh readings alongside the current voltage" in a single query — because each column needs its own time window.

Capability	SQL:2011 Temporal	GS SQL Per-Column Temporal
Scope	Table/row — all columns share one time axis	Per-column — each column has its own time axis
Primary use case	Versioned records (show row as-of a point in time)	Independent time-series (each property sampled at its own rate)
Mixed time windows	Not supported — one temporal predicate per table	Native — kwh(range(last=100)), voltage applies different windows per column
Sparse data	Requires NULL filling or interpolation	Native — each stream has only the timestamps where data exists
Aggregation over time	Standard GROUP BY with temporal filtering	Built-in cycle aggregation — cycleId='day', stat='avg'
Temporal relationships	Not addressed (join tables are static)	FK streams with temporal history — customerUid(range(sd=0)) resolves relationships at each time point

Foreign Key JOINs

Template tables can be joined using foreign key streams. An FK stream stores component UIDs that link to another template table's component rows.

Use standard SQL JOIN syntax with the FK stream as the join column:

SELECT m._component_name AS Meter, c._component_name AS Customer, m.kwh
FROM meter m JOIN customer c ON m.customerUid = c._component_uid

In this example, customerUid is a stream in the meter template that is configured as a foreign key pointing to the customer template. Its values are component UIDs of customer components.

All join types are supported:

• INNER JOIN — Only matching rows from both template tables
• LEFT OUTER JOIN — All rows from the left table, matching rows from the right
• RIGHT OUTER JOIN — All rows from the right table, matching rows from the left
• FULL OUTER JOIN — All rows from both tables
• CROSS JOIN — Cartesian product of both tables

Chained JOINs across multiple template tables are supported:

SELECT m._component_name, m.kwh, c._component_name AS customer, r._component_name AS region
FROM meter m
JOIN customer c ON m.customerUid = c._component_uid
JOIN region r ON c.regionUid = r._component_uid

Template Diagrams

Observation Studio provides a visual diagram view (under the Tools tab) that shows your component templates as tables with FK relationship lines drawn between them. This diagram is useful for understanding the available template tables, their stream columns, and the FK JOINs available before writing TEQ queries.

Supported SQL Features

TEQ queries support the full GS SQL grammar. The only difference is the FROM clause (template tables instead of system tables) and column syntax (stream IDs instead of the _sample column). Everything else works the same:

• WHERE — Filter on stream columns and system columns
• GROUP BY / HAVING — Aggregate across components
• ORDER BY — Sort by any column or expression
• LIMIT / OFFSET — Paginate results
• Aggregate functions — COUNT, SUM, AVG, MIN, MAX, STDEV, etc.
• Subqueries — In-line views within TEQ queries
• CTEs — WITH clause for common table expressions
• UNION / INTERSECT / EXCEPT — Compound operators
• User-defined variables — SET @var = expr
• All functions — String, date, math, JSON, control functions
• DDL — CREATE TABLE, ALTER TABLE, DROP TABLE for template management (see Grammar - DDL)

See the Grammar Reference for the complete listing of supported clauses, functions, operators, predicates, and temporal parameters.

DDL — Data Definition Language

DDL statements for managing templates, resource entities, and views have moved to the Grammar - DDL page. This includes:

• CREATE TABLE, ALTER TABLE, DROP TABLE — template management
• Resource DDL — Cycles, Rollup Calendars, Stream Groups, Runnables
• INSERT INTO — component creation
• Derived Columns — Expressions — auto-computed streams
• Derived Columns — Aggregation — stream group aggregations
• Views — materialized and live views

Limitations

• Cannot mix template tables and system tables in the same query. For example, you cannot JOIN a template table (sensor) with the system system.stream table or system.active_event table. Use separate queries instead.
• Stream IDs that are SQL keywords must be quoted (e.g., "select", [order]).
• TEQ UPDATE appends new values at NOW — it does not modify historical sample data. Use UPDATE system.sample to modify samples at specific timestamps.
• TEQ INSERT uses INSERT INTO (without OR REPLACE). INSERT OR REPLACE is only for the system.sample table.
• Auto-rollup and derivation: All data modifications (INSERT, UPDATE, DELETE — including historical changes via the system.sample table) automatically trigger rollup recalculation and derived stream recomputation for any dependent streams. Changes propagate up the entire dependency tree.

AI Agent Guidance

This section helps AI agents decide when to use template table queries vs system table queries and provides quick-reference syntax.

When to use template table queries (TEQ):

• The organization has component templates defined
• You want stream IDs as columns instead of rows
• You need FK JOINs between template tables
• You want one row per component with multiple stream values

When to use system table queries (TDQ):

• You need system tables (system.active_event, system.user_notification, system.system_notification, system.job_notification)
• The organization does not use component templates
• You need internal metadata columns not exposed in template tables
• You need INSERT OR REPLACE on the system.sample table (modify sample data at specific timestamps)
• You need DELETE FROM system.sample (delete sample data by stream UID and optional time range)
• You need UPDATE system.sample (update a sample value at a specific timestamp)

Quick-reference syntax for template table queries:

Pattern	Example
Basic SELECT	SELECT _component_name, temperature FROM sensor
Filtering	SELECT _component_name, kwh FROM meter WHERE kwh > 100
Temporal (per-column)	SELECT _component_name, kwh(range(last=100)) FROM meter
Mixed temporal/_last_value	SELECT _component_name, kwh(range(sd=-1d)), voltage FROM meter
FK JOIN	SELECT m._component_name, c.region FROM meter m JOIN customer c ON m.customerUid = c._component_uid
Aggregate with JOIN	SELECT c.region, AVG(m.kwh) FROM meter m JOIN customer c ON m.customerUid = c._component_uid GROUP BY c.region
INSERT (create component)	INSERT INTO sensor (_component_name, temperature) VALUES ('NewSensor', 42.0)
UPDATE (append values at NOW)	UPDATE sensor SET temperature = 100.0 WHERE _component_name = 'Sensor_A'
DELETE (remove component)	DELETE FROM sensor WHERE _component_name = 'Sensor_B'
CREATE TABLE (template)	CREATE TABLE sensor (temperature DOUBLE, humidity DOUBLE) WITH (TIMEZONE_ID = 'US/Eastern')
ALTER TABLE (add column)	ALTER TABLE sensor ADD COLUMN pressure DOUBLE
CREATE TABLE (derived column)	CREATE TABLE sensor (a DOUBLE, b DOUBLE, total DOUBLE DERIVED AS 'x + y' DEPENDS ON (x FROM a, y FROM b))
DROP TABLE (template)	DROP TABLE IF EXISTS sensor CASCADE
CREATE CYCLE	CREATE CYCLE hourly INTERVAL 1 HOUR WITH (NAME = 'Hourly')
ALTER CYCLE	ALTER CYCLE hourly INTERVAL 2 HOUR
DROP CYCLE	DROP CYCLE IF EXISTS hourly
CREATE ROLLUP CALENDAR	CREATE ROLLUP CALENDAR my_rc CYCLES ('hourly', 'daily')
ALTER ROLLUP CALENDAR	ALTER ROLLUP CALENDAR my_rc CYCLES ('hourly', 'daily', 'weekly')
DROP ROLLUP CALENDAR	DROP ROLLUP CALENDAR IF EXISTS my_rc
CREATE STREAM GROUP	CREATE STREAM GROUP energy_streams WITH (STREAM_NAME_FILTER = 'kwh*')
CREATE STREAM GROUP (GSQL)	CREATE STREAM GROUP temp_streams AS SELECT uid FROM system.stream WHERE name = 'temperature'
ALTER STREAM GROUP	ALTER STREAM GROUP energy_streams SET (NAME = 'Updated Energy')
DROP STREAM GROUP	DROP STREAM GROUP IF EXISTS energy_streams
CREATE RUNNABLE	CREATE RUNNABLE hourly_agg SCHEDULE 'hourly' ITEMS (STREAM GROUP 'energy_streams' CYCLE 'hourly')
ALTER RUNNABLE	ALTER RUNNABLE hourly_agg SCHEDULE 'daily'
DROP RUNNABLE	DROP RUNNABLE IF EXISTS hourly_agg
RUN RUNNABLE	RUN RUNNABLE hourly_agg
CREATE TABLE (aggregation)	CREATE TABLE totals (total_kwh DOUBLE INTERVAL BASE_CYCLE 'hour' AGGREGATION SUM STREAM GROUP 'all_meters')
CREATE MATERIALIZED VIEW	CREATE MATERIALIZED VIEW daily_energy AS SELECT _component_id, kwh FROM meter WHERE kwh > 0
REFRESH MATERIALIZED VIEW	REFRESH MATERIALIZED VIEW daily_energy
DROP MATERIALIZED VIEW	DROP MATERIALIZED VIEW IF EXISTS daily_energy
CREATE VIEW (live)	CREATE VIEW active_sensors AS SELECT _component_id, _component_name, temperature FROM sensor WHERE temperature IS NOT NULL
DROP VIEW	DROP VIEW IF EXISTS active_sensors

Examples

Basic SELECT — list all sensor components with their latest temperature readings

SELECT _component_name, temperature FROM sensor

Filtering — find sensors with temperature above 30

SELECT _component_name, temperature FROM sensor WHERE temperature > 30

Multiple stream columns

SELECT _component_name, temperature, humidity FROM sensor ORDER BY _component_name

Temporal per-column — last 100 kWh samples per meter

SELECT _component_name, formatDate(_time) AS 'time', kwh(range(last=100)) FROM meter

Mixed temporal and _last_value — last day of kWh with constant voltage

SELECT _component_name, kwh(range(sd=-1d)), voltage FROM meter

Cycle aggregation — daily average kWh

SELECT _component_name, kwh(cycleId='day', stat='avg', range(currentCycle='month')) FROM meter

FK JOIN — meters with their customer names

SELECT m._component_name AS Meter, c._component_name AS Customer, m.kwh
FROM meter m JOIN customer c ON m.customerUid = c._component_uid

Aggregate with FK JOIN — average kWh by customer region

SELECT c.region, AVG(m.kwh) AS avg_kwh
FROM meter m JOIN customer c ON m.customerUid = c._component_uid
GROUP BY c.region

Temporal FK JOIN — last month of kWh for meters in the North region

SELECT m._component_name, formatDate(_time) AS 'time', m.kwh(range(sd=-1M)), c.region
FROM meter m JOIN customer c ON m.customerUid = c._component_uid
WHERE c.region = 'North'

LEFT JOIN — all meters, even those without a linked customer

SELECT m._component_name AS Meter, m.kwh, c._component_name AS Customer
FROM meter m LEFT JOIN customer c ON m.customerUid = c._component_uid

Subquery within TEQ — find sensors whose temperature exceeds the average

SELECT _component_name, temperature FROM sensor
WHERE temperature > (SELECT AVG(temperature) FROM sensor)

Component count per folder

SELECT _folder_path, COUNT(_component_uid) AS component_count FROM sensor GROUP BY _folder_path

Chained FK JOIN across three template tables

SELECT m._component_name AS Meter, c._component_name AS Customer, r._component_name AS Region, m.kwh
FROM meter m
JOIN customer c ON m.customerUid = c._component_uid
JOIN region r ON c.regionUid = r._component_uid

CREATE TABLE — define a template with stream columns and FK relationships

CREATE TABLE meter (
    kwh DOUBLE INTERVAL BASE_CYCLE 'hour' ROLLUP_METHOD 'SUM',
    voltage DOUBLE REGULAR,
    customerUid STRING REFERENCES customer
) WITH (CNAME = 'Energy Meter', TIMEZONE_ID = 'America/New_York')

ALTER TABLE — add a column with stream options

ALTER TABLE meter ADD COLUMN demand DOUBLE INTERVAL BASE_CYCLE 'hour' ROLLUP_METHOD 'MAX'

CREATE TABLE with derived column — auto-compute heat index from siblings

CREATE TABLE sensor (
    temperature DOUBLE,
    humidity DOUBLE,
    heat_index DOUBLE DERIVED AS '-42.379 + 2.049*t + 10.143*h'
        DEPENDS ON (t FROM temperature, h FROM humidity)
)

CREATE TABLE with derived column via FK — pull rate from a related customer template

CREATE TABLE meter (
    kwh DOUBLE,
    customerUid STRING REFERENCES customer,
    billed DOUBLE DERIVED AS 'kwh * rate'
        DEPENDS ON (kwh FROM kwh, rate VIA customerUid.customer.energy_rate)
)

-- VIA deps default to FILL_FORWARD. To disable it explicitly:
-- rate VIA customerUid.customer.energy_rate (FILL_FORWARD is on by default)
-- For a sibling dep that needs fill-forward, add it explicitly:
-- price FROM base_price FILL_FORWARD

DROP TABLE — remove a template and all linked components

DROP TABLE IF EXISTS sensor CASCADE

CREATE TABLE with aggregation column — sum kWh across a stream group

CREATE TABLE energy_totals (
    total_kwh DOUBLE INTERVAL BASE_CYCLE 'hour' ROLLUP_CALENDAR 'energy_rc'
        AGGREGATION SUM STREAM GROUP 'all_meters'
        CALC_RANGE 2 DAY
)

CREATE MATERIALIZED VIEW — cached energy report

CREATE MATERIALIZED VIEW energy_summary
AS SELECT _component_id, _component_name, kwh, cost FROM meter WHERE kwh > 0
WITH (FOLDER_PATH = '/reports', NAME = 'Energy Summary')

REFRESH MATERIALIZED VIEW — update the cached results

REFRESH MATERIALIZED VIEW energy_summary

CREATE VIEW — live view that re-executes on every access

CREATE VIEW active_meters
AS SELECT _component_id, _component_name, kwh FROM meter WHERE kwh IS NOT NULL
WITH (NAME = 'Active Meters', FOLDER_PATH = '/views')

Query a live view — use it in a FROM clause like a table

SELECT * FROM active_meters WHERE kwh > 100