Welcome to GS SQL!

TEQ Guide	Grammar - DDL	Grammar	Tables	Examples	Conventions
Data Types	Date and Time	Data Type Conversions	In-Line Views or Subqueries	Sorting
API	Time Zones	Key Words	Indexing	Security
Not Supported	Query Plans	Performance	Memory Limits	Relational Mapping

Overview

The complexity of querying time-series data with standard SQL can be overwhelming and come with performance bottlenecks. That's why we designed our own structured query language, based on standard SQL, but with extensions, to give you the tools to accomplish your tasks easily and quickly. If your organization uses component templates, we recommend starting with the TEQ Guide — it lets you query using familiar table-and-column syntax with template IDs as tables and stream IDs as columns.

GS SQL™ Query Modes (TDQ & TEQ)

These are the query layers of the platform. The ingestion layer, TW (Temporal Wire), handles data input via HTTP and MQTT. GS SQL supports two query modes that share the same grammar, functions, and operators:

• Temporal Entity Query (TEQ™) — Recommended for most users. Query using component template IDs as table names and stream IDs as column names. Components become rows. Supports FK JOINs between template tables. This is the simplest way to query when your organization uses component templates. See the TEQ Guide for full documentation.
• Temporal Deep Query (TDQ™) — Query system tables directly (system.stream, system.active_event, etc.). Use stream metadata columns and the _sample column for time-series data. Supports INSERT OR REPLACE, UPDATE, and DELETE on the system.sample table. Use TDQ when you need system table access, don't have templates, or need to modify sample data at specific timestamps.

Which Should I Use?

	TEQ (Template Tables)	TDQ (System Tables)
SELECT	Templates as tables, streams as columns, FK JOINs. Temporal parameters.	system.stream table directly + system tables. Temporal parameters.
INSERT	Create components, set initial values at NOW	Insert or replace sample data at specific timestamps
UPDATE	Append new values at NOW (no history edit)	Modify a sample value at a specific timestamp
DELETE	Remove components (cascading delete)	Remove sample data only (stream preserved)
DDL	CREATE TABLE, ALTER TABLE, DROP TABLE for template management	—

Details and Examples

Operation	TEQ (Template Tables)	TDQ (System Tables)
SELECT	Query using template IDs as tables, stream IDs as columns. One row per component. FK JOINs between templates. Per-column temporal parameters. Best for templated orgs and AI agents.	Query the system.stream table directly with metadata columns (_component_uid, _uid, _name, _sample, etc.). Also query system.active_event, system.user_notification, system.system_notification, system.job_notification. Best for system-level access or orgs without templates.
INSERT	INSERT INTO template — Creates new components from the template. Stream column values are appended as samples at NOW. INSERT INTO sensor (_component_name, temperature) VALUES ('S1', 42.0)	INSERT OR REPLACE INTO system.sample — Inserts or replaces sample data at specific timestamps. "OR REPLACE" is mandatory. INSERT OR REPLACE INTO system.sample (_COMPONENT_ID, _ID, _TIME, _SAMPLE) VALUES ('gen1', 'kW', 1736488800000, 2.0)
UPDATE	UPDATE template SET ... — Appends new stream values at NOW for matching components. Does not modify historical data. UPDATE sensor SET temperature = 100 WHERE _component_name = 'S1'	UPDATE system.sample SET ... — Modifies a sample value at a specific timestamp (INSERT OR REPLACE semantics). UPDATE system.sample SET _sample = 99.0 WHERE _uid = '...' AND _time = 1736488800000
DELETE	DELETE FROM template — Removes components and all their streams, samples, and index entries (cascading delete). DELETE FROM sensor WHERE _component_name = 'S1'	DELETE FROM system.sample — Deletes sample data from a stream by UID, with optional time range. Stream metadata is preserved. DELETE FROM system.sample WHERE _uid = '...'
DDL	CREATE TABLE, ALTER TABLE, DROP TABLE — Manage component templates and their stream definitions. See the Grammar - DDL page for full details. CREATE TABLE sensor (temperature DOUBLE, humidity DOUBLE)	—

system.stream, system.active_event, and notification tables are read-only — DML operations on these tables are blocked.

Automatic rollup and derivation on every change: All data modifications — INSERT, UPDATE, and DELETE (including appending, replacing, and deleting historical samples) — automatically trigger rollup recalculation and derived stream recomputation for any affected streams. If a derived stream depends on a related entity via a temporal foreign key, historical relationship changes are also detected and the calculation is re-evaluated using the correct target entity for each time period. Changes propagate up the entire dependency tree. No batch jobs, no manual reconciliation — correct a value from last month and every dependent rollup, derivation, and FK-resolved calculation updates itself.

Conventions

GroveStreams SQL is an ANSI SQL / PostgreSQL-flavored dialect with temporal extensions. Most ANSI and PostgreSQL syntax works as you would expect — standard operators (||, ::, AND/OR/NOT), CAST and EXTRACT, PostgreSQL type names (int8, bigint, text, varchar, numeric, timestamp, etc.), and standard SQL functions (character_length, date_trunc, position, concat, etc.) all behave per spec. The GS-specific additions are the temporal parameters on stream columns (e.g. kwh(range(last=100))), the TEQ template-as-table query model, epoch-millisecond Long datetimes, and the system.stream / system.sample tables. GS SQL is not a PostgreSQL drop-in — the wire-protocol adapter speaks PostgreSQL on the network so BI tools can connect with any PG driver, but the query engine, storage, and temporal semantics are GroveStreams.

• Most items are case in-sensitive including table names, column names, functions and other reserved words.
• Comments are supported within SQL statements:
  • To comment out a single line use --
  • To comment out multiple lines use /* */
• String concatenation can be done with the + operator OR with the standard || operator (e.g., first_name || ' ' || last_name) or the CONCAT(a, b, c, ...) function. As of the May 2026 release, || is the standard ANSI / PostgreSQL string-concatenation operator (it used to be logical OR; saved queries were migrated automatically).
• Use the AND and OR keywords in WHERE conditions. The && operator is no longer accepted as logical AND — use AND. Saved queries that used && or || as logical operators were migrated automatically in the May 2026 release.
• LIKE is case-sensitive (ANSI / PostgreSQL semantics). Use ILIKE for case-insensitive pattern matching. Both accept % / * (any chars) and _ / ? (single char) wildcards. Prior to the May 2026 release LIKE was case-insensitive — saved queries were auto-rewritten to ILIKE so existing results are preserved.
• PostgreSQL-style cast operator :: is supported in addition to CAST(value AS type). Examples: '42'::LONG, price::DOUBLE.
• EXTRACT keyword form EXTRACT(YEAR FROM date) is supported in addition to function-call forms like YEAR(date). Supported fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH. Note: EXTRACT(EPOCH FROM ...) returns milliseconds in GS (PostgreSQL returns seconds — divide by 1000 for PG-compatible behavior).
• The public. schema prefix is silently stripped from table references — SELECT * FROM public.customers works the same as SELECT * FROM customers. This makes BI-tool-generated SQL portable.
• PostgreSQL-flavored function aliases: character_length (alias for LENGTH), strpos / position (alias for INSTR), date_trunc (alias for DATETRUNC), date_part (alias for DATEPART), to_timestamp (alias for FROM_UNIXTIME), concat(a, b, ...), pg_typeof(expr), to_date(string, format) (parse a date string to epoch millis), regexp_match(str, pattern) (returns first match or NULL), regexp_replace(str, pattern, replacement) (Note: GS SQL string literals process backslash escapes — double the backslash for regex shorthand classes, e.g. '\\\\d+' for \d+, or use character classes like '[0-9]+' instead.), now() call form.
• Datetimes are represented by epoch millisecond Longs. See Date and Time below
• Use Temporal parameters to make time-series requests simpler and faster:
  • SELECT _Sample(Range(sd=-1d, ed=now))
  • SELECT _Sample(Range(sd=-1M, ed=streamEd))
  • SELECT _Sample(Range(last=12))
  • SELECT _Sample(cycleId='day', stat='max', timeFilterId='weekends', Range(currentCycle='year'))
  • SELECT _Sample(lag=1, Range(last=100)) — window operations
  • SELECT _Sample(running='sum', Range(currentCycle='month')) — running totals
  • See the Grammar page for more Sample details and examples.
• TEQ queries use component template IDs as table names and stream IDs as column names. See the TEQ Guide.
• NULL
  • NULL represents a missing or unknown value.
  • A String can be Blank or NULL: (string1 == "") is not the same as (string1 IS NULL)
  • Interval streams can have interval samples with a value of NULL. These are considered gap intervals.
  • It is not possible to test for NULL values with comparison operators, such as =, ==, < , or <>. Use the IS NULL and IS NOT NULL operators instead. Use COALESCE or IFNULL to provide default values for NULLs.
  • Rows will be filtered out whenever one side of an expression is a NULL value. Use IS NULL or IS NOT NULL to avoid this scenario.

Security

GS SQL enforces security automatically at query time for both TDQ and TEQ queries. Every row returned is checked against the calling user's folder permissions — rows the user cannot access are silently excluded from results. No additional configuration is required; security is always on.

The security model is deliberately simple: folders and permissions. Complex security systems with many configuration options tend to be misconfigured in practice — the majority of data breaches in cloud platforms trace back to configuration errors, not sophisticated attacks. GS takes a different approach: fewer moving parts, secure defaults, and a permission model that is easy to understand, audit, and get right.

How it works:

• Component security (row-level) — Each component lives in a folder. During query execution, every component is checked for Read rights before it appears in results. Components the user cannot read are silently ignored by the query engine. For example, only component names the user has Read access to will be returned for queries such as: SELECT DISTINCT _component_name FROM system.stream
• Template table security (TEQ) — Component templates live in folders within the component content repository. A user must have Read rights on a template's folder (or inherit them from a parent folder) to query that template table.
• Write and delete security — UPDATE statements require Write rights on the target components. DELETE statements require Write rights. If the user lacks the required rights on any targeted component, the statement fails.
• Saved query security — Saved GS SQL queries are stored in the Tools folder tree. Execute rights are required to run a saved query, and Read rights to view its results. The folder tree filters which saved queries a user can see based on Traverse rights.

Authentication tokens:

Only Session and OAuth tokens can be used for API calls that execute queries, since query result filtering is done using the calling user's content rights. api_key tokens are not allowed for these types of calls, except for saved query results (which OData utilizes).

Defined Query object results can be retrieved using api_key tokens via the GET query/{queryUid}/results API call. Defined Query objects can be found under the Tools tab within Observation Studio.

Folder permission model:

GS uses a hierarchical folder permission model similar to Windows NTFS or Linux filesystem ACLs. Six rights control access:

Right	Description
Read (R)	View folder contents and read component data. Required for SELECT queries.
Write (W)	Create or modify components. Required for INSERT and UPDATE statements.
Execute (E)	Run operations. Required to execute saved GS SQL queries.
Manage (M)	View and modify folder permissions.
Traverse (T)	Navigate through a folder to reach child folders. Required on all ancestor folders to access any item within them.
Delete (D)	Remove components or folders.

Key behaviors:

• Inheritance — Child folders inherit permissions from their parent by default. Inheritance can be broken at any folder to set independent permissions.
• Deny overrides allow — An explicit Deny on a right always wins over an Allow, regardless of where it appears in the hierarchy.
• Traverse is implicit — Every rights check also verifies Traverse on the entire ancestor chain. A user with Read on a deep folder but no Traverse on an ancestor cannot access anything in that folder.
• Users and groups — Permissions can be assigned to individual users or to groups. An "Everyone" group applies to all users in the organization.
• Owner bypass — The organization owner bypasses all permission checks.

GS folder security vs traditional RDBMS security:

Capability	Traditional RDBMS	GS Folder Security
Security model	SQL GRANT/REVOKE on tables, columns, and schemas. Row-Level Security (RLS) via CREATE POLICY.	Hierarchical folder ACLs with inheritance, similar to Windows NTFS or Linux filesystem permissions.
Table-level	GRANT SELECT/INSERT/UPDATE/DELETE on each table. Must be configured per table by a DBA.	Component templates live in folders. Folder permissions control who can query each template table.
Row-level	Requires explicit RLS policies written in SQL. Not enabled by default — rows are visible to anyone with table access until a policy is created.	Automatic. Every component lives in a folder and inherits that folder's permissions. No additional configuration needed — row-level security is always on.
Column-level	GRANT SELECT (col1, col2) restricts which columns a user can read.	Access is per-component (row), not per-column. This keeps the permission model simple and reduces the risk of misconfiguration.
New data default	Varies. New tables and rows are often accessible by default until a DBA locks them down. RLS policies must be written for each new table.	Secure by default. Every component must live in a folder, so it always has a security context. New components inherit the folder's permissions automatically.
Inheritance	Limited. Schema-level grants exist but there is no automatic parent-child inheritance for row security.	Full parent-to-child inheritance. Set permissions on a parent folder and all children inherit them. Break inheritance at any level for independent control.
Deny semantics	No explicit deny — security is additive. Revoke a grant to remove access.	Explicit deny that overrides allow at any level, similar to NTFS deny ACEs.
Configuration	Requires a DBA writing SQL GRANT/REVOKE statements and RLS policies. Misconfiguration is common.	Configured through the folder tree UI. Anyone who understands folder permissions can manage security — no SQL or DBA skills required.

Data Types

The number of data types supported have been limited to keep things simple. Supported Data types (canonical names) and their PostgreSQL / ANSI aliases:

• DOUBLE: A double-precision 64-bit IEEE 754 floating point. It generally represents decimal numbers and is based on the Java Double data type.
  Aliases (May 2026): FLOAT8, FLOAT4, REAL
• LONG: A non-decimal signed number with a range of -9,223,372,036,854,775,808 ... 9,223,372,036,854,775,807 and is based on the Java Long data type.
  Aliases (May 2026): INT8, BIGINT, INTEGER, INT4, SMALLINT, INT2, TIMESTAMP, TIMESTAMPTZ (date/time values are stored as epoch milliseconds in a LONG)
• STRING: A set of zero or more characters and is based on the Java String data type.
  Aliases (May 2026): TEXT, VARCHAR
• BOOLEAN: Used to store only two possible values, either true or false and is based on the Java Boolean data type.
  Aliases (May 2026): BOOL
• BIGDECIMAL: Arbitrary-precision signed decimal numbers and is based on the Java BigDecimal data type.
  Aliases (May 2026): NUMERIC, DECIMAL

All aliases are accepted in CAST, the postfix :: cast operator, CREATE TABLE column type declarations, and JSON_TABLE / CSV_TABLE column type declarations. They were added in the May 2026 release for PostgreSQL / BI-tool compatibility — pre-existing GS SQL using only the canonical names continues to work unchanged.

Examples:

CAST('42' AS INT8)
price::NUMERIC
CREATE TABLE sensor (id INT8, temperature DOUBLE, location TEXT, active BOOL);

Date and Time

Using epoch millis as a datetime representation has become common practice in most modern applications as it greatly simplifies datetime management. GS SQL uses the same practice. That is, it doesn't have a DATE or TIME data type. Date and time are stored and managed as single Long data types which are epoch milliseconds. GroveStreams SQL provides functions to convert Long epoch millis to readable dates for your locale. Datetimes can be stored as Strings, but GroveStreams will not "know" it is a date and manage it as a String.

Search the web for more information on "epoch milliseconds" to learn more.

Data Type Conversions

• Sample Float types will be converted to Doubles
• Sample Short and Integer types will be converted to Longs
• If more than one Sample value data type is being returned in a single column, the following logic is applied in this order:
  • If one is a String, they all are converted to Strings
  • If one is a Big Decimal, they all are converted to BigDecimal
  • If one is a Double, they all are converted to Double
  • If one is a Long, they all are converted to Long
  • All Boolean
• The above logic applies to _LAST_VALUE columns too
• If an expression has a String on one side and a number on the other, an attempt will be made to convert the String to a number.

GS SQL Keywords

Keywords may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object.

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in GS SQL:

'keyword'		A keyword in single quotes is a string literal.
"keyword"		A keyword in double-quotes is an identifier.
[keyword]		A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in GS SQL for compatibility.
`keyword`		A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in GS SQL for compatibility.

In-Line Views or Subqueries

In-line views, sometimes known as subqueries, are supported. They must be surrounded with parentheses.

SELECT _component_name, _last_value FROM system.stream INNER JOIN
(SELECT _component_uid FROM system.stream WHERE _id='squareFeet' AND _sample > 10000) AS table2 ON _component_uid == table2._component_uid
WHERE _id='region' AND _sample IN ('west', 'east')

Correlated subqueries are supported and are evaluated once for each row processed by the parent statement.

SELECT DISTINCT _component_name, _id, (SELECT MAX(_sample) FROM system.stream ss WHERE s1._id == 'kWh') FROM system.stream s1 WHERE _id == 'kWh' ORDER BY _component_name

Sorting

Sample times will automatically be sorted for most queries. Use ORDER BY when they are not.

Indexing

The system.stream table has secondary indexes that speed up queries with WHERE clause filters. When a query filters on an indexed column, the engine automatically selects the best available index instead of scanning the entire table.

Indexed columns: _component_name, _component_uid, _component_id, _name, _uid, _folder_path. A composite index on (_component_name, _name) is available for queries that filter on both columns.

For best performance, use AND-connected equality filters on indexed columns in your WHERE clauses.

See system.stream Table Indexes for the full list of available indexes.

TEQ queries automatically filter by _template_uid, which leverages the templateUid index.

TEQ column last-value indexes. Template columns marked PRIMARY_KEY true or INDEXED true in CREATE TABLE maintain a compound last-value index (internally silv). A TEQ equality predicate on an indexed column (WHERE meter_id = 'M-123') resolves via a single row-key prefix scan in sub-second time regardless of table size. PK columns are auto-indexed; INDEXED is for non-PK status/category columns you filter on frequently. Write cost is one extra Delete + Put per indexed stream per value change — index selectively. See PRIMARY_KEY and INDEXED column qualifiers.

Currently Not Supported

The following standard SQL features are not currently supported in GS SQL. Items marked with * are on the roadmap.

Transactions	BEGIN, COMMIT, ROLLBACK are not yet supported. Each statement executes independently. Transaction support is coming soon.
Window Functions	SQL WINDOW/OVER() syntax is not supported. Window operations are available as temporal parameters: lag, lead, slide/slidestat, and running. These provide LAG(), LEAD(), sliding aggregates, and running totals directly on sample data.
Stored Procedures	Not supported. Use derived streams or the AI Assistant for multi-step logic.
Views (CREATE VIEW)	Standard SQL views are not supported. Use CTEs (WITH clauses) for reusable subqueries within a statement. Note: External views and materialized views backed by JDBC connections are supported via DDL.
CREATE INDEX DDL	Standalone CREATE INDEX statements are not supported. Column-level last-value indexes on template tables are declared in CREATE TABLE using PRIMARY_KEY true or INDEXED true — see DDL. The system also maintains its own Stream table secondary indexes automatically.
TRUNCATE, MERGE	Not supported.

Reading Query Plans

When executing GS SQL via the API or command line with dumpTrees=true, three trees are printed before results:

1. AST Tree — The parsed abstract syntax tree of your query.
2. Pushdown Tree — After optimization passes (predicate pushdown, column combining).
3. Physical Plan Tree — The execution operators that will run.

Key operators to look for in the physical plan:

• IndexScannerOp — A secondary index is being used. This is fast.
• TableScannerOp — Full table scan. Consider adding a WHERE filter on an indexed column.
• TableScannerSampleOp — Scanning sample data with temporal parameters applied.
• ProjectionOp — Evaluating SELECT expressions.
• FilterOp — Applying WHERE predicates.
• SortOp — ORDER BY sorting.
• GroupByOp — GROUP BY aggregation.
• JoinOp — JOIN processing.

Performance tip: If you see TableScannerOp where you expected IndexScannerOp, check that your WHERE clause uses equality filters (=) on indexed columns (_component_name, _component_id, _component_uid, _name, _uid, _folder_path). The engine selects the index with the longest matching prefix.

Performance Guidance

• Filter on indexed columns. The system.stream table has secondary indexes on _component_name, _component_id, _component_uid, _name, _uid, and _folder_path. Queries that filter on these columns avoid full table scans.
• Index the TEQ columns you filter on. Mark template columns PRIMARY_KEY true or INDEXED true in CREATE TABLE so equality predicates like WHERE meter_id = 'M-123' or WHERE status = 'active' resolve via the compound last-value index instead of scanning every component of the template. See TEQ column last-value indexes.
• Use TEQ when possible. TEQ queries automatically filter by _template_uid, which leverages the templateUid index. This is faster than TDQ queries that scan all streams.
• Limit sample ranges. Use temporal parameters (Range(sd=..., ed=...) or Range(last=N)) to fetch only the data you need. Unbounded sample queries on high-frequency streams can be expensive.
• Use CycleId/Stat for aggregation. Pre-computed rollups (CycleId='hour', Stat='avg') are faster than querying raw samples and aggregating with GROUP BY.
• Prefer DISTINCT for component-level queries. When querying component metadata (names, IDs, _last_value), use SELECT DISTINCT to avoid duplicating rows across streams.

Sample Limit per Request

Each query request is limited to approximately 550,000 sample intervals. This limit applies to the total number of raw sample data points loaded across all streams in a single query. It does not apply to component metadata queries (e.g., SELECT DISTINCT _component_name FROM system.stream) or rollup queries using CycleId/Stat (which access pre-computed aggregates, not raw samples).

If your query exceeds this limit, the results will be truncated. To work around it:

• Narrow the time range. Use Range(sd=..., ed=...) to request a smaller window of data per query. For example, query one month at a time instead of an entire year.
• Paginate with time windows. Use successive queries with non-overlapping sd/ed values to page through large time ranges:
  -- Page 1: SELECT _sample(range(sd=0, ed=1704067199999)) FROM system.stream WHERE _uid = '...'
  -- Page 2: SELECT _sample(range(sd=1704067200000, ed=1706745599999)) FROM system.stream WHERE _uid = '...'
• Use Range(last=N). If you only need the most recent data, last=N retrieves exactly N samples without loading the full range.
• Use rollups instead of raw samples. Querying with CycleId='hour', Stat='avg' returns one data point per hour from pre-computed aggregates, which are not subject to the raw sample limit.

Memory Limits

GS SQL enforces per-clause memory limits to prevent any single query from consuming excessive server resources. Each clause that buffers rows in memory has its own limit:

GROUP BY	~1,000 MB	Rows buffered for grouping and aggregate computation.
ORDER BY	~1,000 MB	Rows buffered for sorting.
DISTINCT	~10,000 MB	Rows buffered for duplicate elimination.
Recursive CTE	~1,000 MB	Rows accumulated during recursive WITH queries.

These limits are shared across concurrent queries via a global memory pool (default ~6 GB). If your query exceeds a clause limit or there is not enough global memory available, the query will fail with an error indicating which clause exceeded its allowance.

To stay within limits: use LIMIT to cap result sizes before sorting or grouping, narrow your WHERE predicates to reduce the working set, and break large aggregations into smaller queries over subsets of the data.

Metadata and TEQ queries (e.g., SELECT DISTINCT _component_name FROM system.stream or SELECT _component_name, temperature FROM sensor) are governed by the same memory limits — there is no separate row count cap. Memory-based limits are preferred over hard row count limits because the actual resource cost depends on row width, not just row count.

Derivation Timeout

When INSERT or UPDATE statements trigger stream derivations (computed streams that depend on the modified data), the derivation graph has a default timeout of 10 seconds. This can be overridden per statement using the DERIVE_TIMEOUT option (in milliseconds):

INSERT INTO system.sample ... WITH (DERIVE_TIMEOUT=30000)

Mapping Relational Patterns to GS SQL

For engineers coming from PostgreSQL, MySQL, or other relational databases:

Tables	Component templates. Each template defines a "table" with stream IDs as columns. Query with TEQ.
Rows	Components. Each component linked to a template is a row in the TEQ table.
Columns	Streams. Each stream on a template is a column — but every cell holds full temporal history.
Foreign Keys	Streams that store UIDs pointing to other components. TEQ supports FK JOINs between template tables.
History Tables + Triggers	Not needed. Every stream is a history table by default. No triggers to maintain.
Materialized Views	Real-time rollups. Configure aggregation hierarchies and they compute automatically as data arrives.
Batch Roll-up Jobs	Not needed. Rollups are real-time and built into the stream infrastructure.
Junction Tables	Link streams. A stream that holds UIDs tracks the relationship over time.
Window Functions	Use window temporal parameters: lag, lead, slide/slidestat, running for positional access, sliding aggregates, and running totals. Also CycleId/Stat for interval aggregation.
Stored Procedures	Derived streams (formula engine) and the AI Assistant handle multi-step logic.

Examples

TDQ Examples (System Table Queries)

Support for Expressions with or without columns

SELECT ((3*2)+3/10

Current time

SELECT formatDate(now)

Return a sorted list of all components

SELECT DISTINCT _component_name AS 'Component' FROM system.stream ORDER BY Component

Return a list of all components and their streams

SELECT _component_name AS 'Component', _name AS 'Stream' FROM system.stream ORDER BY Component, Stream

Return a list of Components that belong to a specific folder

SELECT DISTINCT _component_name AS 'Component' FROM system.stream WHERE _folder_path == '/Components'

Return a list of Components that belong to a specific folder and all of its subfolders

SELECT DISTINCT _component_name AS 'Component' FROM system.stream WHERE startsWith(_folder_path, '/Components')

Return the last sample and time for a specific stream

SELECT _last_value, formatDate(_end_date) AS 'time' FROM system.stream WHERE _component_id='generator22' AND _id='kw'

Return the last sample and time for a collection of streams with IDs that start with 'energy'

SELECT _last_value, formatDate(_end_date) AS 'time' FROM system.stream WHERE _id LIKE 'energy*'

Return the last 100 samples and times for a specific stream

SELECT formatDate(_time) AS 'time', _sample( range(last=100) ) FROM system.stream WHERE _component_id='generator22' AND _id='kw'

Return the last 100 samples and times for a specific stream with values greater than 100

SELECT formatDate(_time) AS 'time', _sample( range(last=100) ) FROM system.stream WHERE _component_id='generator22' AND _id='kw' AND _sample > 100

Return a list of components that have a point stream with ID='region' and a value of either 'west' or 'east'

SELECT DISTINCT _component_name AS 'Station Name', _last_value FROM system.stream WHERE _id='region' AND _sample IN ('west', 'east')

Return a list of components with two point stream conditions (in-line view must be in parentheses and have an Alias)

SELECT DISTINCT _component_name AS 'Station Name', _last_value FROM system.stream INNER JOIN
(SELECT _component_uid FROM system.stream WHERE _id='squareFeet' AND _sample > 10000) AS table2 ON _component_uid == table2._component_uid
WHERE _id='region' AND _sample IN ('west', 'east')

Return a list of components with three point stream conditions

SELECT DISTINCT _component_name AS 'Station Name', _last_value FROM system.stream INNER JOIN
(SELECT _component_uid FROM system.stream WHERE _id='squareFeet' AND _sample > 10000) AS table2 ON _component_uid == table2._component_uid INNER JOIN
(SELECT _component_uid FROM system.stream WHERE _id='managed' AND _sample == true) AS table3 ON table2._component_uid == table3._component_uid
WHERE _id='region' AND _sample IN ('west', 'east')

Return a list of components with three regular stream last sample value conditions

SELECT DISTINCT _component_name AS 'Station Name', _last_value FROM system.stream INNER JOIN
(SELECT _component_uid FROM system.stream WHERE _id='squareFeet' AND _last_value > 10000) AS table2 ON _component_uid == table2._component_uid INNER JOIN
(SELECT _component_uid FROM system.stream WHERE _id='managed' AND _last_value == true) AS table3 ON table2._component_uid == table3._component_uid
WHERE _id='region' AND _last_value IN ('west', 'east')

Return hourly maximum afternoon temperatures for the last 24 hours (or 1 day)

SELECT _component_name AS 'Weather Station', _name AS 'Temperature', _time, _sample(cycleId='hour', stat='max', range(sd=-1d, ed=now), TimeFilterId='afternoon' ) AS 'Afternoon Hourly Highs' FROM Stream WHERE _id LIKE 'temp*'

Multiple _sample() columns

Two or more _sample() calls in one SELECT that share the same range, CycleId, TimeFilterId, gap-fill, and offsets merge into a single row per time-bucket — for example, retrieving hourly average and hourly peak side-by-side:

SELECT _component_name, _name, _time,
    _sample(range(sd=-1d, ed=now), CycleId='hour', Stat='avg') AS hourly_avg_kw,
    _sample(range(sd=-1d, ed=now), CycleId='hour', Stat='max') AS hourly_peak_kw
FROM system.stream WHERE _component_name='Inverter 1' AND _name='AC Output (kW)'

_sample() calls with different cycles or different ranges produce separate row sets (one row group per distinct time-bucket layout) — joining or aggregating those is up to the caller.

Merging is controlled by the org-level gsqlMergeSampleCols setting. New orgs default to ON (standard SQL behavior); orgs that existed before this feature default to OFF to preserve their pre-merge query semantics. Toggle with ALTER ORG SET gsqlMergeSampleCols = TRUE (requires org owner or admin) or via the org settings UI/REST API. When OFF, each _sample() call emits its own tuple set with NULLs in the other sample columns — wrap the query in a subquery with MAX() over each column to collapse the NULLs.

Query for this month's samples which are located in east or west regions (as indicated by point streams)

SELECT _component_name, _name, _time_sd, _time_ed, _sample( Range( currentCycle='month') ) FROM system.stream AS stream INNER JOIN
(SELECT _sample, _component_uid FROM system.stream WHERE _id = 'region' AND _sample IN ('east', 'west')) AS region on stream._component_uid = region._component_uid
WHERE stream._id='kWh' Order By _component_name

RIGHT OUTER JOIN: Return all streams from the right table with any matching streams from the left table. Unmatched right rows have NULL left columns.

SELECT t1._component_name, t1._name, t2._component_name, t2._name FROM system.stream AS t1 RIGHT OUTER JOIN
Stream AS t2 ON t1._name = t2._name
WHERE t1._component_id = 'siteA' AND t2._component_id = 'siteB'

FULL OUTER JOIN: Return all streams from both tables. Unmatched rows from either side have NULL values for the missing side.

SELECT t1._component_name, t1._name, t2._component_name, t2._name FROM system.stream AS t1 FULL OUTER JOIN
Stream AS t2 ON t1._name = t2._name
WHERE t1._component_id = 'siteA' AND t2._component_id = 'siteB'

Return the Spring and Summer samples for the previous year; ordered by time

SELECT formatDate(_time) AS 'time', _sample( timeFilterId='spring', range(previousCycle='year') ) FROM system.stream WHERE _component_id='generator22'
UNION ALL
SELECT formatDate(_time) AS 'time', _sample( timeFilterId='summer', range(previousCycle='year') ) FROM system.stream WHERE _component_id='generator22'
ORDER BY time

Return the number of components created by month

SELECT COUNT(DISTINCT _component_uid) AS 'New Components', FORMATDATE(_component_created_date, 'yyyy/MM') AS Month from system.stream WHERE _component_created_date IS NOT NULL
GROUP BY FORMATDATE(_component_created_date, 'yyyy/MM') ORDER BY 2

Return the number of components created today

SELECT COUNT(DISTINCT _component_uid) AS 'New Components Today' FROM system.stream WHERE YEAR(_component_created_date) = YEAR(NOW) AND MONTH(_component_created_date) = MONTH(NOW) AND DAY(_component_created_date) = DAY(NOW)

Return the number of streams created by week of year

SELECT FORMATDATE(_component_created_date, 'yyyy/MM') AS Date, FORMATDATE(_component_created_date, 'yyyy/ww') AS 'Week of Year', COUNT(_uid) AS 'New Streams' from system.stream WHERE _component_created_date IS NOT NULL AND YEAR(_component_created_date) = 2024 GROUP BY FORMATDATE(_component_created_date, 'yyyy/w') ORDER BY 2

Copy a component while using a stream template and include all stream data. 'c1_copy' will be the new component name.

WITH comp_to_copy AS (SELECT _component_id, _id, _time, _sample FROM system.stream WHERE _component_id = 'c1') INSERT OR REPLACE INTO system.sample WITH COMP_TMPL_ID = ComponentTemplate1 (_component_id, _id, _time, _sample) SELECT 'c1_copy', _id, _time, _sample FROM comp_to_copy

Update a sample value at a specific timestamp. Rollups and derived streams that depend on this stream are automatically recalculated.

UPDATE system.sample SET _sample = 99.0 WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f' AND _time = 1736488800000

Delete all sample data from a stream (stream metadata is preserved). Affected rollups and derivations are recalculated.

DELETE FROM system.sample WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f'

Delete samples within a time range. Rollups and derivations covering the affected range are automatically recalculated.

DELETE FROM system.sample WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f' AND _time >= 1736488800000 AND _time <= 1736575200000

Return a list of active events.

SELECT eventName AS 'Event Name', componentName AS 'Component', streamName AS 'Stream', eventCategory AS 'Event Category', sampleValue AS 'Stream Value at Event', formatDate(sampleTime) AS 'Event Date' FROM system.active_event

Return a list of active events along with the component's folder location and the last 5 samples of each event's stream.

SELECT _folder_path + '/' + _component_name AS Component, _name AS Stream, _sample(range(last=5, ed=streamEd)) AS Current_Values, formatDate(lastUpdated) AS Last_Updated, system.active_event.eventName AS Event_Name, system.active_event.eventCategory AS Event_Category, system.active_event.sampleValue AS Stream_Value_at_Event, formatDate(system.active_event.sampleTime) AS Event_Date
FROM system.active_event INNER JOIN system.stream
ON system.active_event._stream_uid = system.stream._uid;

TEQ Examples (Template Table Queries)

The following examples use component templates as table names. See the TEQ Guide for full details.

List all sensor components with their latest temperature

SELECT _component_name, temperature FROM sensor

Filter sensors by temperature

SELECT _component_name, temperature FROM sensor WHERE temperature > 30

Per-column temporal query — last 100 kWh samples

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

Mixed temporal and _last_value columns

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

FK JOIN — meters with their customer names

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

Aggregate with FK JOIN — average kWh by 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 North region meters

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

Create a new component from a template with an initial stream value

INSERT INTO sensor (_component_name, temperature) VALUES ('NewSensor_1', 42.0)

Update a stream value for a component (appends at NOW)

UPDATE sensor SET temperature = 100.0 WHERE _component_name = 'Sensor_A'

Delete a component and all its data

DELETE FROM sensor WHERE _component_name = 'Sensor_B'

File Import

---

GS SQL supports importing and executing a file containing multiple semicolon-delimited statements. This is useful for batch operations such as creating multiple component templates, inserting data, or running setup scripts.

File Format

• UTF-8 encoded text file
• Statements separated by semicolons (;)
• Trailing semicolon on the last statement is optional
• Single-line comments (--) and block comments (/* ... */) are supported
• Semicolons inside single-quoted strings are handled correctly
• Maximum file size: 1 MB

Execution Behavior

• Statements are executed sequentially, one at a time
• Variables set with SET @var = expr persist across statements within the same file
• By default, execution stops on the first error. Use the continueOnError parameter to continue executing remaining statements after a failure
• Transaction support is coming soon — currently, statements that succeeded before an error are not rolled back
• A system notification is created when the import completes, summarizing the results

Example File

-- Setup sensor infrastructure CREATE TABLE sensor (temperature DOUBLE, humidity DOUBLE); -- Insert initial components INSERT INTO sensor (_component_name, temperature) VALUES ('Sensor_A', 22.5); INSERT INTO sensor (_component_name, temperature) VALUES ('Sensor_B', 19.8); -- Verify SELECT _component_name, temperature FROM sensor;

See the GQL Resource API documentation for the REST endpoint details.