GroveStreams Help Center
Grammar

TEQ Syntax	Grammar - DDL
Temporal Parameters	Date Functions
Control Functions	Joins	String Functions	Other Functions
Predicates	Numeric Functions	Constants	JSON Functions
Operators	Aggregate Functions	Table Functions	Compound Operators

Term Description Example

Query

SELECT Selects data from one or more tables. SELECT column1, column2, ... FROM table_name

DISTINCT Used to return only distinct (different) values. SELECT DISTINCT column1, column2, ... FROM table_name

FROM Selects data from one or more tables. Accepts system table names (system.stream, system.active_event, etc.) for TDQ queries and component template IDs for TEQ queries. Select _component_name, _name From system.stream
Select _component_name, temperature From sensor

WHERE Used to filter records by extracting only those records that fulfill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition;

GROUP BY The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of components in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Aliases from SELECT are supported in GROUP BY. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

SELECT year(_time) as y, COUNT(*) FROM system.stream GROUP BY y;

HAVING A HAVING clause specifies that a SELECT statement must only return rows where aggregate values meet the specified conditions. HAVING and WHERE are often confused by beginners, but they serve different purposes. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

SELECT year(_time) as y, COUNT(*) FROM system.stream GROUP BY y;

ORDER BY Used to sort the result-set in ascending or descending order. Ascending is the default.

Sort columns can be column names, column indexes, or expressions that resolve to a name or index. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s), ... ASC|DESC;

LIMIT OFFSET Use the LIMIT and OFFSET clauses to retrieve a portion of rows returned. row_count determines the number of rows that will be returned. The optional OFFSET clause skips the offset rows before beginning to return the rows. SELECT column1, column2, ... FROM table_name LIMIT row_count OFFSET offset;

Data Modification (INSERT, UPDATE, DELETE)

Organization-Level Operation Settings
Organization administrators can enable or disable each operation type at the organization level via the Organization Information window (Query tab) or the REST API. These settings act as a kill switch independent of user-level RBAC permissions. Settings are split into two groups:

Data Permissions — controls operations on sample data (INSERT OR REPLACE INTO system.sample, UPDATE system.sample, DELETE FROM system.sample):

Setting	Default	Description
gsqlAllowInsert	true	Insert or replace sample data
gsqlAllowUpdate	true	Update sample data at specific timestamps
gsqlAllowDelete	false	Delete sample data

Component & Template Permissions — controls operations that create, modify, or remove components and templates:

Setting	Default	Description
gsqlAllowInsertEntity	true	Create components and their streams
gsqlAllowUpdateEntity	true	Update component stream values
gsqlAllowDeleteEntity	false	Delete components and their streams
gsqlAllowCreateTable	true	Create templates (CREATE TABLE)
gsqlAllowAlterTable	true	Modify templates (ALTER TABLE)
gsqlAllowDropTable	false	Delete templates (DROP TABLE)
gsqlAllowDropTableCascade	false	Allow DROP TABLE CASCADE (deletes all linked components)
gsqlAllowCreateOtherTools	true	Create resource entities (CREATE CYCLE, ROLLUP CALENDAR, STREAM GROUP, RUNNABLE, AGENT)
gsqlAllowAlterOtherTools	true	Modify resource entities (ALTER CYCLE, ROLLUP CALENDAR, STREAM GROUP, RUNNABLE, AGENT)
gsqlAllowDropOtherTools	false	Delete resource entities (DROP CYCLE, ROLLUP CALENDAR, STREAM GROUP, RUNNABLE, AGENT)
gsqlAllowRunOtherTools	true	Execute runnables and agents (RUN RUNNABLE, RUN AGENT)
gsqlAllowCreateMaterializedView	false	Create materialized views (CREATE MATERIALIZED VIEW)
gsqlAllowAlterMaterializedView	false	Modify materialized views (ALTER MATERIALIZED VIEW)
gsqlAllowDropMaterializedView	false	Delete materialized views (DROP MATERIALIZED VIEW)
gsqlAllowRefreshMaterializedView	false	Refresh materialized views (REFRESH MATERIALIZED VIEW)
gsqlAllowCreateView	false	Create live views (CREATE VIEW)
gsqlAllowAlterView	false	Modify live views (ALTER VIEW)
gsqlAllowDropView	false	Delete live views (DROP VIEW)

When an operation is disabled, any attempt to execute it returns an error: "GS SQL [operation] operations are disabled for this organization. Contact your org administrator."

INSERT
(system.sample table)

INSERT OR REPLACE INTO system.sample [WITH (options)]
(columns) VALUES ( { NULL | expression } ), ...
| SELECT select_criteria

Inserts or replaces sample data in the system.sample table. "OR REPLACE" is mandatory for system.sample inserts.

Required columns:

(_COMPONENT_UID and _UID) or (_COMPONENT_ID and _ID): Use either pair to identify the stream being updated. Inserts are slightly faster when UIDs are used.
_TIME, _TIME_SD, _TIME_ED: The sample time. Not required for point streams. Required for regular and interval streams. Use _TIME if the time is the start datetime for interval streams. Use _TIME_ED if the time is the end of the interval.
_SAMPLE: The value being saved for the included _TIME.

WITH options (optional — option names are case-insensitive and accept both underscore and camelCase forms, e.g. COMP_TMPL_ID or compTmplId):

COMP_TMPL_ID = 'id': Uses a component template when the insert is creating a new component/stream and IDs are used as identifiers.
FOLDER_PATH = 'path': Folder to place newly created components into. Must start with '/Components'. Defaults to '/Components'. Created if it does not exist. Also accepts FOLDER.
CNAME = 'name': Display name for auto-created components. Defaults to the _COMPONENT_ID value.
DESCRIPTION = 'text': Description for auto-created components.
TIMEZONE_ID = 'timezone': Timezone for auto-created components (e.g., 'America/New_York'). Defaults to the template's timezone.
DERIVE_MAX_STREAMS = n: Maximum number of streams to derive after insert. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional: both WITH (COMP_TMPL_ID = 'x') and WITH COMP_TMPL_ID = 'x' are valid. A component and stream will be created if they do not exist when IDs are used.

INSERT OR REPLACE INTO system.sample(_COMPONENT_UID, _UID, _TIME, _SAMPLE) VALUES('a1dbf630-72bc-3b52-b0b5-49f33c2f1d81', '2e75e358-2924-3ec2-b763-fba82abc648f', 1736488800000, 2.0 ),('a1dbf630-72bc-3b52-b0b5-49f33c2f1d81', '2e75e358-2924-3ec2-b763-fba82abc648f', 1736488801000, 3.0 );

-- Auto-create component from template with metadata:
INSERT OR REPLACE INTO system.sample WITH ( COMP_TMPL_ID = 'accountTemplate', CNAME = 'Acme Corp', FOLDER_PATH = '/Components/Clients', DESCRIPTION = 'Enterprise client account', TIMEZONE_ID = 'America/New_York') (_COMPONENT_ID, _ID, _TIME, _SAMPLE) VALUES('acme_corp', 'monthly_revenue', 1736488800000, 50000.00);

-- Bulk from CSV_TABLE:
INSERT OR REPLACE INTO system.sample WITH (COMP_TMPL_ID = 'productTemplate') (_COMPONENT_ID, _ID, _TIME, _SAMPLE) SELECT sku, 'units_sold', sale_date, quantity FROM CSV_TABLE(@HTTP_BODY) WITH (1 sku STRING, 2 sale_date LONG, 3 quantity DOUBLE);

INSERT
(Template tables)

INSERT INTO template_table [WITH (options)] (columns) VALUES (values)
| SELECT select_criteria

Creates new components from a template table (TEQ). "OR REPLACE" is not used for template table inserts.

Columns:

_component_id (mandatory): The component ID — the primary key for the new component.
Stream ID columns: Any stream ID defined in the template. Values are appended as samples at the current time (NOW).

WITH options (optional, apply to all inserted components — option names are case-insensitive and accept both underscore and camelCase forms):

CNAME = 'name': Component display name. Defaults to the _component_id value.
FOLDER_PATH = 'path': Folder placement. Defaults to '/Components'. Created if it does not exist. Also accepts FOLDER.
DESCRIPTION = 'text': Sets the component description.
TIMEZONE_ID = 'timezone': Sets the component timezone (e.g., 'America/New_York'). Defaults to the template's timezone.
DERIVE_MAX_STREAMS = n: Maximum streams to derive after insert. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional. The component is created from the template and placed in the specified folder. If stream columns are included, their values are inserted as samples at NOW after the component is created.

Returns a single row with the number of rows affected.

-- Create a component (_component_name defaults to _component_id):
INSERT INTO account (_component_id) VALUES ('acct_10042');

-- Create with initial stream values:
INSERT INTO account (_component_id, balance, credit_limit) VALUES ('acct_10042', 15000.00, 50000.00);

-- Create with metadata:
INSERT INTO account WITH ( CNAME = 'Acme Corp Operating', FOLDER_PATH = '/Components/Enterprise', DESCRIPTION = 'Primary operating account', TIMEZONE_ID = 'America/New_York') (_component_id, balance) VALUES ('acct_10042', 15000.00);

-- Bulk insert:
INSERT INTO product (_component_id, price, quantity_on_hand) VALUES ('SKU-001', 29.99, 500), ('SKU-002', 49.99, 200), ('SKU-003', 14.99, 1000);

-- Insert from a query:
INSERT INTO product (_component_id, price) SELECT sku, msrp FROM CSV_TABLE('...');

UPDATE
(Template tables)

UPDATE template_table [WITH (options)] SET column = value [, ...] [WHERE condition]

Appends new sample values at the current time (NOW) for components matching the WHERE clause.

The SET clause specifies stream ID columns and their new values. Only stream columns defined in the template are allowed — system columns (_component_id, _component_name, _component_uid, etc.) cannot be updated.

The WHERE clause filters which components are affected. It supports the same predicates as TEQ SELECT queries (e.g., filtering by _component_id, _component_name, _component_uid, or stream values). If omitted, all components of the template are updated.

WITH options (optional, applied to all matched components — option names are case-insensitive and accept both underscore and camelCase forms):

DESCRIPTION = 'text': Sets the component description on matched components.
TIMEZONE_ID = 'timezone': Sets the component timezone (e.g., 'Asia/Tokyo').
DERIVE_MAX_STREAMS = n: Maximum streams to derive after update. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional. Returns a single row with the number of rows affected.

-- Update balance for a specific account:
UPDATE account SET balance = 12000.00 WHERE _component_id = 'acct_10042';

-- Update multiple streams:
UPDATE product SET price = 24.99, quantity_on_hand = 450 WHERE _component_name = 'Widget A';

-- Update with description and timezone:
UPDATE account WITH (DESCRIPTION = 'Flagged for review', TIMEZONE_ID = 'Asia/Tokyo') SET credit_limit = 0 WHERE balance < 0;

UPDATE
(system.sample table)

UPDATE system.sample [WITH (options)] SET _sample = value WHERE _uid = 'stream_uid' AND _time = epoch_millis

Updates a sample value at a specific timestamp. Uses INSERT OR REPLACE semantics internally.

The WHERE clause must specify the stream UID and the exact time of the sample to update.

WITH options (optional — option names are case-insensitive and accept both underscore and camelCase forms):

DERIVE_MAX_STREAMS = n: Maximum number of streams to derive after update. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional.

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

DELETE
(Template tables) DELETE FROM template_table WHERE condition

Deletes components (and all their streams and sample data) that match the WHERE clause.

The WHERE clause filters which components are removed. It supports the same predicates as TEQ SELECT queries. This is a cascading delete — the component, all its streams, all sample data, and all index entries are removed.

Returns a single row with the number of rows affected. -- Delete a specific component:
DELETE FROM sensor WHERE _component_name = 'Sensor_B';

-- Delete components matching a condition:
DELETE FROM sensor WHERE temperature < 0;

DELETE
(system.sample table) DELETE FROM system.sample WHERE _uid = 'stream_uid' [AND _time >= start AND _time <= end]

Deletes sample data from a stream. The WHERE clause must specify the stream UID. Optionally, include _time conditions to delete only samples within a time range.

Without _time conditions, all sample data for the stream is deleted. The stream itself (metadata) is preserved. -- Delete all sample data from a stream:
DELETE FROM system.sample WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f';

-- Delete samples in a time range:
DELETE FROM system.sample WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f' AND _time >= 1736488800000 AND _time <= 1736575200000;

Data Definition (DDL)

DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE, Resource DDL, View DDL, and Aggregation columns) have moved to the Grammar - DDL page.

Template DDL — CREATE TABLE, ALTER TABLE, DROP TABLE
Resource DDL — Cycles, Rollup Calendars, Stream Groups, Runnables
INSERT INTO — Component creation
Derived Columns — Expressions — auto-computed stream formulas
Derived Columns — Aggregation — stream group aggregations
Views — Materialized and live views

Common Table Expressions (CTEs)

WITH [RECURSIVE]

WITH [RECURSIVE] cte_name [(column_name [, column_name ...])] AS (subquery)
    -- SELECT or UPDATE using cte_name

The WITH clause in GS SQL allows you to define one or more named subqueries, known as Common Table Expressions (CTEs), which can be used in the main query. This is particularly useful for simplifying complex queries by breaking them down into simpler parts, or for reusing the same subquery multiple times within a single SQL statement.

Syntax:
WITH cte_name AS (subquery) SELECT ... FROM cte_name ...;

Recursive CTE Syntax:
WITH RECURSIVE cte_name [(column_name [, ...])] AS (
   anchor_select
   UNION ALL
   recursive_select -- references cte_name
)
SELECT ... FROM cte_name ...;

The RECURSIVE keyword enables a CTE to reference itself, allowing hierarchical or iterative queries. The CTE body must contain an anchor member (a SELECT that does not reference the CTE) and a recursive member (a SELECT that references the CTE), connected by UNION ALL. Execution starts with the anchor, then repeatedly executes the recursive member using only the rows produced in the previous iteration, until no new rows are generated (fixed point) or the maximum recursion depth is reached.

The default maximum recursion depth is 100 iterations. If the recursive member does not have a proper termination condition (e.g., a WHERE clause that eventually produces no rows), the query will fail with an error at the maximum depth.

Functionality:

You can define multiple CTEs by separating them with commas.
Each CTE can be used in subsequent CTEs or in the main query, but not in previous CTEs.
CTEs do not persist after the statement ends; they are temporary constructs for the duration of the query execution.
Recursive CTEs must use UNION ALL between anchor and recursive members. UNION, INTERSECT, and EXCEPT are not supported in recursive CTEs.

Limitations:

GS SQL only supports using the WITH statement within SELECT and INSERT SQL statements.
Recursive CTEs only support UNION ALL (not UNION, INTERSECT, or EXCEPT) between anchor and recursive members.

WITH filtered_names AS (SELECT _component_name, _name FROM system.stream WHERE _name LIKE '*kWh*')
SELECT _component_name, _name FROM filtered_names;

WITH filtered_names (compName, streamName) AS (SELECT _component_name, _name FROM system.stream WHERE _name LIKE '*kWh*')
SELECT compName as 'Meter Name', streamName as 'channel' FROM filtered_names;

WITH count_per_cname AS ( SELECT _component_name, COUNT(_name) AS count FROM system.stream GROUP BY _component_name )
SELECT s._component_name, s._name, c.count FROM system.stream s JOIN count_per_cname c ON s._component_name = c._component_name;

WITH filtered_streams AS ( SELECT _component_name, _name FROM system.stream WHERE _component_name = 'c1' ),
   count_per_cname AS ( SELECT _component_name, COUNT(_name) AS name_count FROM filtered_streams GROUP BY _component_name )
SELECT fs._component_name, fs._name, c.name_count FROM filtered_streams fs JOIN count_per_cname c ON fs._component_name = c._component_name ORDER BY c.name_count DESC;

WITH filtered_streams (_component_name, _name) AS ( SELECT _component_name, _name FROM system.stream WHERE _name LIKE '*kWh*' ),
   high_kWh_streams (_component_name, name_count) AS ( SELECT _component_name, COUNT(_name) AS name_count FROM filtered_streams GROUP BY _component_name HAVING COUNT(_name) > 1 )
SELECT _component_name, name_count FROM high_kWh_streams;

WITH items_to_insert (_component_id, _id, _time, _sample) AS (SELECT 'component1', 'stream1', NOW, 10)
INSERT OR REPLACE INTO system.sample (_component_id, _id, _time, _sample) SELECT _component_id, _id, _time, _sample FROM items_to_insert

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

Recursive CTE — numeric sequence:
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

Recursive CTE — multiple columns:
WITH RECURSIVE powers(n, val) AS (
  SELECT 1, 2
  UNION ALL
  SELECT n + 1, val * 2 FROM powers WHERE n < 8
)
SELECT n, val FROM powers;

User-Defined Variables

SET SET @var_name = expr
SET @var_name = (subquery)

A value can be stored in a user-defined variable by one statement and be referred to later in another statement. This enables a value to be passed from one statement to another. The same variable can be reassigned a different value, using the SET statment again, within the same session. User-defined variables are session specific. User variable names are not case-sensitive. Names have a maximum length of 64 characters.

An exception will be raised if a variable is referred to and has not been declared and initialized using the SET statement.

The WITH clause in GS SQL allows you to define one or more named subqueries, known as Common Table Expressions (CTEs), which can be used in the main query. This is particularly useful for simplifying complex queries by breaking them down into simpler parts, or for reusing the same subquery multiple times within a single SQL statement. SET @XXX = 4;
Select @XXX; --> 4

SET @A1 = 'meter1';
SELECT DISTINCT _component_name FROM system.stream WHERE _component_id = @A1; -->'Meter 1';

SET @VAR1 = NULL;
Select @VAR1; --> NULL

SET @VAR1 = CAST(4 AS DOUBLE);
SELECT @VAR1; --> 4 (it's a DOUBLE)

SET @VAR1 = CAST(4 AS STRING);
SELECT @VAR1; --> '4'

SET @VAR1 = (SELECT _last_value from system.stream WHERE _component_id='meter1' AND _id='kwh');
SELECT @VAR1; --> 3.43

SET @AA = 1;
SET @BB = MAX(@AA, 0);
SET @AA = 2;
SELECT @AA + @BB; --> 3

Temporal Parameters

Range Range specifies a time range for samples or indicates to return the last number of samples. SELECT _sample( range(sd=-1h, ed=streamEd)) FROM system.stream

TimeFilterId The TimeFilterId can be assigned the ID of a GroveStreams TimeFilter and will filter returned samples based on the time filter. SELECT _sample(timeFilterId='FallSeasonWeekends') FROM system.stream

CycleId
Stat The CycleId can be assigned the ID of a GroveStreams cycle and will aggregate samples into intervals that match the cycle size. Stat, optional, indicates the statistic to use for aggregation. SELECT _sample(cycleId='day', stat='max')) FROM system.stream

GapFill The GapFill parameter is used to fill gaps in interval streams. SELECT _sample(gapFill(head='nextVal', middle='avg', tail='previousVal')) FROM system.stream

Window Parameters
(lag, lead, slide, running) Window parameters perform positional and sliding-window operations on sample data. Use these instead of SQL WINDOW/OVER() syntax for time-series analytics. SELECT _sample(lag=1, range(last=100)) FROM system.stream
SELECT _sample(slide=3, slidestat='avg', range(sd=-1d)) FROM system.stream

Joins

INNER JOIN Selects records that have matching values in both tables. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

CROSS JOIN The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join. The ON clause with a CROSS JOIN is ignored. SELECT column_name(s) FROM table1 CROSS JOIN table2

LEFT OUTER JOIN The LEFT OUTER JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match. SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

RIGHT OUTER JOIN The RIGHT OUTER JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match. SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;

FULL OUTER JOIN The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. Rows from both sides that have no match are included with NULL values for the missing side. SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Compound Operators

EXCEPT The EXCEPT operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Just as with the UNION operator, the same rules apply when using the EXCEPT operator. SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2

INTERSECT The INTERSECT operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

Just as with the UNION operator, the same rules apply when using the INTERSECT operator. SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. Only distinct rows are returned.

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

UNION ALL The UNION ALL operator is the same as UNION except duplicate rows are returned. SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

Operators

+, - (unary) Makes operand positive or negative. SELECT +3, -4 FROM table

/, *, %, +, - Divide, Multiply, Modulus, Plus, Subtract SELECT (3/4*2) + 1 - 5 FROM table

<, <=, >, >=,
=, ==,
!=, <> Less than: <
Less than or equal: <=
Greater than: >
Greater than or equal: >=
Equal to: =
Equal to: ==
Not equal: !=
Not equal: <> SELECT column FROM table WHERE x==10

&, |, <<, >>, ~ Bitwise:
&: If both bits are 1, it gives 1, else it gives 0
|: If either of the bits is 1, it gives 1, else it gives 0.
<<: Shifts the bits of the number to the right and fills 0 on voids left as a result.
>>: Shifts the bits of the number to the right and fills the voids left with the sign bit.
~: It makes every 0 to 1, and every 1 to 0. SELECT 5 < 7 FROM table
SELECT ~7 FROM table

String Operators The following operators work on Strings: <, <=, >, >=, =, ==, !=, <>, +, ||.
|| is the standard ANSI / PostgreSQL string-concatenation operator (May 2026 release). Equivalent to CONCAT(a, b). Pre-release, || meant logical OR — saved queries were migrated automatically. SELECT 'hello' + ' ' + 'there!' FROM table
SELECT 'hello' || ' ' || 'there!' FROM table
SELECT CONCAT('hello', ' ', 'there!') FROM table

Cast Operator :: PG-style postfix cast. Equivalent to CAST(value AS type). Added May 2026 release. SELECT '42'::LONG, price::DOUBLE FROM products

EXTRACT keyword form ANSI / PG keyword form of date-part extraction. Lowers to YEAR/MONTH/DAY/etc functions. Added May 2026 release.
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). SELECT EXTRACT(YEAR FROM order_date) FROM orders
SELECT EXTRACT(EPOCH FROM ts) FROM events

Schema prefix stripping The public. schema prefix on table references is silently stripped (May 2026 release). PG drivers prefix every table reference with public. by default; GS resolves them to the unqualified table name. SELECT * FROM public.customers
— resolves to: SELECT * FROM customers

Predicates - An expression that evaluates to TRUE, FALSE, or UNKNOWN.

NOT,
AND,
OR NOT: Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.
AND: Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.
OR: Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.

May 2026 release: && is no longer accepted as a logical operator — use the AND keyword. || is now the standard ANSI / PostgreSQL string-concatenation operator; use the OR keyword for logical OR. Saved queries that previously used && or || as logical operators were migrated automatically. SELECT column FROM table WHERE x=10 AND (y!=2 OR y!=5) AND NOT z!=8

BETWEEN,
NOT BETWEEN The BETWEEN operator selects values within a given range. The values can be numbers or text. The BETWEEN operator is inclusive: begin and end values are included. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT column_name(s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

EXISTS,
NOT EXISTS The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records. SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

IN,
NOT IN The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2, ...);

LIKE,
NOT LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. LIKE is case-sensitive (ANSI / PostgreSQL semantics). For case-insensitive matching, use ILIKE. There are four wildcards often used in conjunction with the LIKE operator:

The star sign (*) or the percentage sign (%) represent zero, one, or multiple characters
The question mark sign (?) or the underscore sign (_) represent one, single character

Note: Prior to the May 2026 release, LIKE was case-insensitive. Saved queries are auto-rewritten to ILIKE during the upgrade so existing results are preserved.

SELECT column_name(s) FROM table_name WHERE columnN LIKE pattern;
SELECT column_name(s) FROM table_name WHERE columnN NOT LIKE pattern;
SELECT fruit FROM table_name WHERE fruit LIKE '?pple';;
SELECT fruit FROM table_name WHERE fruit LIKE '_pple';;
SELECT fruit FROM table_name WHERE fruit LIKE 'A%le';;
SELECT fruit FROM table_name WHERE fruit LIKE 'A*le';

ILIKE,
NOT ILIKE Case-insensitive variant of LIKE (PostgreSQL semantics). Same wildcards as LIKE: % / * match zero or more characters, _ / ? match exactly one character. SELECT column_name(s) FROM table_name WHERE columnN ILIKE pattern;
SELECT column_name(s) FROM table_name WHERE columnN NOT ILIKE pattern;
SELECT fruit FROM table_name WHERE fruit ILIKE 'a%LE';

GLOB,
NOT GLOB Same as the LIKE predicate. SELECT column_name(s) FROM table_name WHERE column GLOB pattern;

REGEXP,
NOT REGEXP REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. If the pattern finds a match in the expression, the function returns TRUE, else it returns FALSE. Based off of Java java.util.regex.pattern. SELECT column_name(s) FROM table_name WHERE column REGEXP pattern;
SELECT column_name(s) FROM table_name WHERE column NOT REGEXP pattern;

IS NOT NULL,
IS NULL It is not possible to test for NULL values with comparison operators, such as =, ==, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead. SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_name(s) FROM table_name WHERE column IS NOT NULL;

Date Functions

FORMATDATE FORMATDATE(timeAsEpochMillis [,pattern] [,timeZone])

Formats an epoch millis datetime into a readable string. This method maps to Java’s DateFormatter.

timeAsEpochMillis: A Long datatype that represents a date in epoch milliseconds.
pattern: Optional. The format pattern. The default pattern is "yyyy-MM-dd'T'HH:mm:ss".
timeZone: Optional. The Time Zone is used for conversion and is optional. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

List of Patterns
List of Time Zones SELECT FormatDate(_time, pattern, timeZone) FROM table
SELECT FORMATDATE(now) → ‘2021-04-17T20:27:09’
SELECT FORMATDATE(_start_date, ‘dd/MM/yyyy, hh:mm:ss’) → ‘17/04/2021, 08:28:35’
SELECT FORMATDATE(_time_ed, ‘dd/MM/yyyy, hh:mm:ss’, ‘US/Central’) → ‘17/04/2021, 08:29:15’
SELECT FORMATDATE(now, 'MMM dd, yyyy (E), h:mm a', 'US/Central') → ‘Apr 17, 2021 (Sat), 8:41 PM’

TOEPOCHMILLIS TOEPOCHMILLIS(timeAsString[,pattern] [,timeZone])

Parses a String datetime into an epoch millisecond Long. This method maps to Java’s DateFormatter.

timeAsString: A String datatype that represents a date and time.
pattern: Optional. The format pattern used or parsing. The default pattern is "yyyy-MM-dd'T'HH:mm:ss".
timeZone: Optional. The Time Zone is used for conversion and is optional. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

List of Patterns
List of Time Zones SELECT TOEPOCHMILLIS('01/02/2021, 00:00:00', 'dd/MM/yyyy, HH:mm:ss', 'US/Central') → 1612159200000
SELECT TOEPOCHMILLIS('01/02/2021, 00:00:00', 'dd/MM/yyyy, HH:mm:ss') → 1612159200000
SELECT TOEPOCHMILLIS('2021-04-01T06:00:00.000', 'yyyy-MM-dd''T''HH:mm:ss.SSS') → 1617274800000; Escape T's ' with double apostrophes
SELECT TOEPOCHMILLIS('2021-02-01T00:00:00') → 1612159200000

DATEADD

DATEADD(epochMillis, spanType, spanAmount [,timeZone])

Adds time spans to an existing epoch millis date. Returns epoch millis date as Long data type.

epochMillis: Datetime to be added to. Epoch Milliseconds Long data type.
spanType: One of the following (case sensitive):

s: Second
m: Minute
h: Hour
d: Day
w: Week
M: Month
Y: Year

spanAmount: The number of spanTypes to add or subtract.
timeZone: Optional. The Time Zone is used for calculating the new datetime. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT DATEADD(epochMillis, 'd', 2 'US/Central') → Adds 2 days to the epochMillis
SELECT DATEADD(epochMillis, 'd', -2 'US/Central') → Subtracts 2 days to the epochMillis
SELECT DATEADD(epochMillis, 'y', 1 'US/Central') → Adds 1 year to the epochMillis

DATE_ADD DATE_ADD(epochMillis, INTERVAL amount unit)

Standard SQL syntax for adding time spans. Equivalent to DATEADD. Provided for compatibility with MySQL/PostgreSQL-style queries.

epochMillis: Datetime to be added to. Epoch Milliseconds Long data type.
amount: The number of units to add.
unit: One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR (case insensitive). SELECT DATE_ADD(epochMillis, INTERVAL 2 DAY) → Adds 2 days
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR) → 1 hour from now

DATE_SUB DATE_SUB(epochMillis, INTERVAL amount unit)

Standard SQL syntax for subtracting time spans. Provided for compatibility with MySQL/PostgreSQL-style queries.

epochMillis: Datetime to be subtracted from. Epoch Milliseconds Long data type.
amount: The number of units to subtract.
unit: One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR (case insensitive). SELECT DATE_SUB(NOW(), INTERVAL 30 DAY) → 30 days ago
SELECT DATE_SUB(epochMillis, INTERVAL 1 YEAR) → 1 year before epochMillis
SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR) → 2 hours ago

FROM_UNIXTIME FROM_UNIXTIME(epochMillis)

No-op passthrough for standard SQL compatibility. Since GroveStreams already stores all datetimes as epoch milliseconds, this function simply returns its argument unchanged. Provided so that LLM-generated or MySQL-style queries using FROM_UNIXTIME() work without modification.

epochMillis: An epoch millisecond Long value (returned as-is). SELECT FROM_UNIXTIME(1618885675191) → 1618885675191
SELECT FROM_UNIXTIME(NOW) → same as NOW

UNIX_TIMESTAMP UNIX_TIMESTAMP()

Returns the current time as epoch milliseconds. Equivalent to NOW. Provided for standard SQL compatibility so that MySQL-style queries work without modification. SELECT UNIX_TIMESTAMP() → 1618885675191

DAYOFWEEK DAYOFWEEK(epochMillis [,timeZone])

The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).
Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

epochMillis: Datetime to find the Day of Week for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT DAYOFWEEK(now, 'US/Central') → 7
SELECT DAYOFWEEK(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT DAYOFWEEK(TOEPOCHMILLIS('2021-02-06T00:00:00')) → 7

DAY DAY(epochMillis [,timeZone])

The DAY() function returns the day number of the month. 1 is returned for the 1st day. Return data type is Long.

epochMillis: Datetime to find the day for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT DAY(now, 'US/Central') → 25
SELECT DAY(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 1
SELECT DAY(TOEPOCHMILLIS('2021-02-06T00:00:00')) → 6

MONTH MONTH(epochMillis [,timeZone])

The MONTH() function returns the month number of the datetime. 1 is returned for the 1st month of the year. Return data type is Long.

epochMillis: Datetime to find the month for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT MONTH(now, 'US/Central') → 4
SELECT MONTH(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT MONTH(TOEPOCHMILLIS('2021-06-06T00:00:00')) → 6

YEAR YEAR(epochMillis [,timeZone])

The YEAR() function returns the year number of the datetime. Return data type is Long.

epochMillis: Datetime to find the Day for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT YEAR(now, 'US/Central') → 4
SELECT YEAR(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT YEAR(TOEPOCHMILLIS('2021-06-06T00:00:00')) → 6

DATEDIFF

DATEDIFF(datePart, startDate, endDate)

Returns, as a Long data type, the requested elapsed time amount. This calculation is based on milliseconds ellapsed and does not take into consideration items like daylight savings or leap years. The result will be the floor for fractional results.

Millisecond difference isn't supported below, but can be calculated by subtracting the two datetimes: endDate-startDate

datePart: The units in which DATEDIFF reports the difference between the startdate and enddate. One of the following (case sensitive):

s: Second
m: Minute
h: Hour
d: Day

startDate: Epoch milliseconds Long data type.
endDate: Epoch milliseconds Long data type.

SELECT DATEDIFF('s', now, now + 1000) → 1
SELECT DATEDIFF('h', TOEPOCHMILLIS('2021-02-01T00:00:00'), TOEPOCHMILLIS('2021-02-01T05:30:00')) → 5
SELECT DATEDIFF('d', TOEPOCHMILLIS('2021-02-01T00:00:00'), TOEPOCHMILLIS('2021-02-02T05:00:00')) → 1

DATEPART

DATEPART(part, date [, timeZone])

Returns a specified part of a date as a Long value.

part: A string specifying the date part to extract. Supported values (case insensitive):

year: Year
quarter: Quarter (1-4)
month: Month (1-12)
day: Day of month (1-31)
dayofyear: Day of year (1-366)
week: ISO week of year
weekday: Day of week (Sunday=0, Monday=1, ..., Saturday=6)
hour: Hour (0-23)
minute: Minute (0-59)
second: Second (0-59)

date: Epoch milliseconds Long data type.
timeZone: Optional. The timezone to use for the calculation (e.g., 'US/Eastern', 'UTC'). Defaults to the organization timezone.

SELECT DATEPART('hour', TOEPOCHMILLIS('2021-06-15T10:30:45')) → 10
SELECT DATEPART('quarter', TOEPOCHMILLIS('2021-06-15T00:00:00')) → 2
SELECT DATEPART('day', TOEPOCHMILLIS('2021-06-15T23:30:00'), 'Asia/Tokyo') → 16

DATETRUNC

DATETRUNC(part, date [, timeZone])

Truncates a date to the specified precision, returning epoch milliseconds.

part: A string specifying the truncation precision. Supported values (case insensitive):

year: Truncate to January 1st
quarter: Truncate to 1st day of the quarter
month: Truncate to 1st day of the month
week: Truncate to Monday (ISO week start)
day: Truncate to start of day
hour: Truncate to start of hour
minute: Truncate to start of minute
second: Truncate to start of second

date: Epoch milliseconds Long data type.
timeZone: Optional. The timezone to use for the truncation (e.g., 'US/Eastern', 'UTC'). Defaults to the organization timezone. Important for day/month/year truncation in non-UTC timezones.

SELECT DATETRUNC('day', TOEPOCHMILLIS('2021-06-15T10:30:45')) → epoch for 2021-06-15T00:00:00
SELECT DATETRUNC('month', TOEPOCHMILLIS('2021-06-15T10:30:45')) → epoch for 2021-06-01T00:00:00
SELECT DATETRUNC('hour', TOEPOCHMILLIS('2021-06-15T10:30:45')) → epoch for 2021-06-15T10:00:00

String Functions

INSTR INSTR(string1, string2)

Returns the position of the first occurrence of a string in another string. 1 being the 1st position. This function performs a case-insensitive search.

string1: The string to be searched.
string2: The string to search for in string1. If string2 is not found, this function returns 0 SELECT INSTR('Apple Tree', 'Tree') → 7
SELECT INSTR('Apple Tree', 'apple') → 1
SELECT INSTR('Peach Tree', 'Apple')→ 0

LEFT LEFT(string1, count)

Returns the left part of a character string with the specified number of characters.

string1: The string to be trimmed.
count: The number of characters to extract. If the number exceeds the number of characters in string, it returns string SELECT LEFT('Grizzly bear', 7) → ‘Grizzly
SELECT LEFT('Grizzly bear', 100) → ‘Grizzly bear’

RIGHT RIGHT(string1, count)

Returns the right part of a character string with the specified number of characters.

string1: The string to be trimmed.
count: The number of characters to extract. If the number exceeds the number of characters in string, it returns string SELECT RIGHT('Grizzly bear', 7) → ‘Grizzly'
SELECT RIGHT('Grizzly bear', 100) → ‘Grizzly bear’

SUBSTR / SUBSTRING

SUBSTR(string1, start [,length])
SUBSTRING(string1, start [,length])

Returns a substring from a string starting at a specified position with a predefined length.

string1: The string to be used to extract the substring.
start: The start argument is a long that specifies the starting position of the returned substring. The start argument can be a positive or negative integer:

If start is a positive integer, the substr() function returns a substring starting from the beginning of the string. The first character has an index of 1.
If the start is a negative integer, the returned substring consists of the length number of characters starting from the end of the string. The last character has an index of -1.

length: Optional. The length argument determines the length of the substring. The length argument is optional. If it is omitted, it is assumed to be the maximum positive integer.

If any argument is NULL, the substr() function will return NULL.

SELECT SUBSTR('GS SQL substr', 1, 6) → ‘GS SQL'
SELECT SUBSTR('GS SQL substr', -6, 6) → ‘substr'
SELECT SUBSTR('GS SQL substr', 1, 6) → ‘GS SQL'
SELECT SUBSTR('GS SQL substr', 1, 6) → ‘substr'

LENGTH / LEN / CHAR_LENGTH LENGTH(string1)
LEN(string1)
CHAR_LENGTH(string1)

Return the number of characters (or length) of a specified string.

string1: The string to find the length of. SELECT LENGTH(‘Rocket’) → 6

LOWER / LCASE LOWER(string1)
LCASE(string1)

Return a string with all characters converted to lowercase.

string1: The string to lower case. SELECT LOWER(‘Rocket’) → ‘rocket’

UPPER / UCASE UPPER(string1)
UCASE(string1)

Return a string with all characters converted to uppercase.

string1: The string to upper case. SELECT UPPER(‘Rocket’) → ‘ROCKET’

TRIM TRIM(string1 [,chars])

Removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove. SELECT TRIM(‘ CAT ‘) → ‘ CAT’
SELECT TRIM(‘??CAT?’, ‘?‘) → ‘CAT’

LTRIM LTRIM(string1 [,chars])

Remove spaces or other specified characters in a set from the left end of a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove. SELECT LTRIM(‘ CAT ‘) → ‘CAT ‘
SELECT LTRIM(‘??CAT?’, ‘?‘) → ‘CAT?’

RTRIM RTRIM(string1 [,chars])

Remove all spaces or specified characters in a set from the right end of a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove. SELECT RTRIM(‘ CAT ‘) → ‘ CAT’
SELECT RTRIM(‘??CAT?’, ‘?‘) → ‘??CAT’

STARTSWITH STARTSWITH(string1, chars)

Used for checking prefix of a String. It returns a boolean value true or false based on whether the given string begins with the specified letter or word. Case insensitive.

string1: The string to test.
chars: The characters to test for at the start of the string. SELECT STARTSWITH(‘blue sky‘, ‘blue’) → true
SELECT STARTSWITH(‘blue sky‘, ‘red’) → false

REPLACE REPLACE(string1, substring, replacement)

Replace all occurrences of a substring by another substring in a string.

string1: The string to test.
substring: The characters to be replaced.
replacement: The characters to be inserted. SELECT REPLACE(‘Up the Hill’, ‘the’, ‘a’) → ‘Up a Hill’’

REVERSE REVERSE(string1)

Returns the reverse of a string value.

string1: The string to reverse. Must be a string data type. SELECT REVERSE(‘hello’) → ‘olleh’
SELECT REVERSE(‘abc’) → ‘cba’
SELECT REVERSE(‘’) → ‘’

PARSECSV PARSECSV(string1, index [, separator [, stringDelimiter]])

Parses a delimited string as if it were a line in a CSV import file and returns the segment at the passed in index. It does not not have to end with a \n character.

string1: The string to split into substrings.
index: The index of the parsed substring to return. 1 being the 1st position.
separator: Optional. Defaults to comma. It is the character that separates string segments
stringDelimiter: Optional. Defaults to double quote. It is the character that delimits text segments. SELECT PARSECSV(‘a,b,c‘, 1) → ‘a’
SELECT PARSECSV(‘a,b,c‘, 2) → ‘b’
SELECT PARSECSV(‘1,2,3‘, 3) → ‘3’
SELECT PARSECSV(‘a,b,c‘, 4) → throws index out of bounds exception
SELECT PARSECSV(‘"Hello world",1‘, 1) → 'Hello world'
SELECT PARSECSV(‘a|b|c‘, 1, '|') → 'a'
SELECT PARSECSV(‘a|b|"Hello world"‘, 3, '|', '"') → 'Hello world'

Numeric Functions

ROUND ROUND(number [,amount])

Returns a numeric value, rounded to the specified length or precision. Rounds half up. The function returns the same data type as the numeric data type of the number passed in.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded SELECT ROUND(5.1) → 5
SELECT ROUND(5.9) → 6
SELECT ROUND(5.5) → 6
SELECT ROUND(5.123456, 3) → 5.123
SELECT ROUND(5.123456, 10) → 5.123456

MAX MAX(number [,number2] [...numberN])

The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded SELECT MAX(5.1, 5.2) → 5.2
SELECT MAX(1, 3, 2) → 3
SELECT MAX(1, NULL, 2) → NULL

MIN MIN(number [,number2] [...numberN])

The multi-argument max() function returns the argument with the minimum value, or return NULL if any argument is NULL.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded SELECT MIN(5.1, 5.2) → 5.1
SELECT MIN(1, 3, 2) → 1
SELECT MIN(1, NULL, 2) → NULL

FLOOR FLOOR(number)

Returns the largest integer less than or equal to the specified numeric expression. The function returns the same data type as the numeric data type of the argument.

number: The number to calculate the floor for. SELECT FLOOR(5.7) → 5

CEIL / CEILING CEIL(number)
CEILING(number)

Return the smallest integer value that is greater than or equal to a number. The function returns the same data type as the numeric data type of the argument.

number: The number to calculate the ceiling for. SELECT CEIL(5.1) → 6

TRUNC / TRUNCATE TRUNC(number [,amount])
TRUNCATE(number [,amount])

The TRUNC(n1,n2) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. The function returns the same data type as the numeric data type of the argument.

number: The number to truncate.
amount: Optional. The number of decimal places to truncate. SELECT TRUNC(5.1) → 5
SELECT TRUNC(5.7) → 5
SELECT TRUNC(5.68,1) → 5.6
SELECT TRUNC(55.68,-1) → 50

PI PI()

The double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter. Utilizes the Java Math.PI constant. SELECT PI() → 3.141592653589793

RADIANS RADIANS()

Converts a number from degrees into radians. SELECT RADIANS(45) → 0.7853981633974483

RANDOM / RAND RANDOM()
RAND()

Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0. Returned values are chosen pseudorandomly with (approximately) uniform distribution from that range. Utilizes Java Random() function. SELECT RANDOM() → 0.48882270224969493

SIGN SIGN(number)

Returns the signum function of the given numeric expression as a Long. The return value is -1 if the given numeric expression is negative; 0 if the given numeric expression is zero; and 1 if the given numeric expression is positive.

number: The number to signum. SELECT SIGN(-10) → -1
SELECT SIGN(5) → 1
SELECT SIGN(0) → 0

ABS ABS(number)

Return the absolute value of a number. The return type will be the type of the argument.

number: The number to process. SELECT ABS(-10) → 10
SELECT ABS(0) → 0
SELECT ABS(99) → 99

SQRT SQRT(number)

Returns the square root of the specified value. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT SQRT(2) → 1.4142135623730951

EXP EXP(number)

Returns the exponential value of the specified numeric expression.

The constant e (2.718281...), is the base of natural logarithms.

The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067.

The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT EXP(2) → 7.38905609893065

POWER / POW POWER(number, power)
POW(number, power)

Returns the value of the specified expression to the specified power. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. power: The power to which to raise the number. SELECT POWER(2.5, 4) → 39.0625

LN(number)

Returns the natural logarithm (base e) of a double, long, or big decimal value. Special cases:

If the argument is NaN or less than zero, then the result is NaN.
If the argument is positive infinity, then the result is positive infinity.
If the argument is positive zero or negative zero, then the result is negative infinity.

Returns a Double type.

number: The number to process.

SELECT LN(10) → 2.302585092994046

LOG LOG(number [,base])

Returns the logarithm of the first argument computed at the base of the second argument. Returns a Double type.

number: The number to process. base: Optional. Defaults to base 10. SELECT LOG(3, 2) → 1.5849625007211563
SELECT LOG(3) → 0.47712125471966244

DEGREES DEGREES(number)

Converts a number from radians into degrees. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT DEGREES(.33) → 18.90760723931717

SIN SIN(number)

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT SIN(45) → 0.8509035245341184

SINH SINH(number)

Returns the hyperbolic sine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT SINH(.5) → 0.5210953054937474

ASIN ASIN(number)

Returns the arcsine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT ASIN(0.5) →0.5235987755982989

ASINH ASINH(number)

Returns the hyperbolic arcsine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT ASINH(0.5) → 0.48121182505960347

COS COS(number)

A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT COS(90) → -0.4480736161291702

COSH COSH(number)

A mathematical function that returns the hyperbolic cosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT COSH(0.5) → 1.1276259652063807

ACOS ACOS(number)

A mathematical function that returns the arccosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT ACOS(.5) → 1.0471975511

ACOSH ACOSH(number)

A mathematical function that returns the hyperbolic arccosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT ACOSH(10) → 2.993222846126381

TAN TAN(number)

Returns the trigonometric tangent of the specified angle, in radians, and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT TAN(33) →-75.31301480008509

TANH TANH(number)

Returns the hyperbolic tangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT TANH(33) →-75.31301480008509

ATAN ATAN(number)

Returns the arctangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT ATAN(33) → 1.54050257

ATANH ATANH(number)

Returns the hyperbolic arctangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT ATANH(0.5) → 0.5493061443340548

MOD MOD(a, b)

Returns the remainder of a divided by b. Equivalent to the % operator. The return type follows type precedence: BigDecimal > Double > Long.

a: The dividend (numeric).
b: The divisor (numeric). SELECT MOD(10, 3) → 1
SELECT MOD(10.5, 3.0) → 1.5
SELECT MOD(17, 5) → 2

GREATEST GREATEST(expr1, expr2 [, ...])

Returns the greatest (maximum) value from a list of expressions. If any argument is NULL, returns NULL. Supports numeric and string comparisons.

GREATEST requires at least two arguments. It accepts any number of additional arguments.

expr1: The first expression to compare.
expr2: The second expression to compare.
...: Optional. Additional expressions to compare. SELECT GREATEST(1, 5, 3) → 5
SELECT GREATEST('apple', 'cherry', 'banana') → 'cherry'
SELECT GREATEST(1, NULL, 3) → NULL

LEAST LEAST(expr1, expr2 [, ...])

Returns the least (minimum) value from a list of expressions. If any argument is NULL, returns NULL. Supports numeric and string comparisons.

LEAST requires at least two arguments. It accepts any number of additional arguments.

expr1: The first expression to compare.
expr2: The second expression to compare.
...: Optional. Additional expressions to compare. SELECT LEAST(5, 1, 3) → 1
SELECT LEAST('apple', 'cherry', 'banana') → 'apple'
SELECT LEAST(1, NULL, 3) → NULL

JSON Functions

ISJSON

ISJSON(jsonString [,typeConstraint])

Determine whether a given string is valid JSON.

jsonString: A String data type that will be tested as a JSON string.
typeConstraint: Optional. Defaults to VALUE. One of the following:

VALUE: Tests for a valid JSON value. This can be a JSON object, array, number, string, false, true
SCALAR: Tests for a valid JSON scalar – number, string, false, true)
OBJECT: Tests for a valid JSON object
ARRAY: Tests for a valid JSON Array

Specifying the typeConstraint argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned.

SELECT ISJSON(1) -> throws exception - parameter is not a string
SELECT ISJSON('1') -> true
SELECT ISJSON('"1"') -> true
SELECT ISJSON('x') -> false
SELECT ISJSON('"x"') -> true per RFC 8259.
SELECT ISJSON('[]') -> true
SELECT ISJSON('{}') -> true
SELECT ISJSON(NULL) -> false
SELECT ISJSON('NULL') -> true
SELECT ISJSON(1, SCALAR) -> throws exception - parameter is not a string
SELECT ISJSON('1', SCALAR) -> true
SELECT ISJSON('"1"', SCALAR) -> true
SELECT ISJSON('1', ARRAY) -> false
SELECT ISJSON('1', OBJECT) -> false
SELECT ISJSON('{}', SCALAR) -> false
SELECT ISJSON('{}', ARRAY) -> false
SELECT ISJSON('{}', OBJECT) -> true
SELECT ISJSON('[]', ARRAY) -> true

JSON_EXISTS JSON_EXISTS(jsonString, jsonPath)

Determines whether a JSON string satisfies a given path search criterion.

jsonString: A String data type that will be tested as a JSON string.
jsonPath: A valid SQL/JSON path to test in the input. SELECT JSON_EXISTS('{"info":{"address":[{"town":"LA"},{"town":"Atlanta"}]}}', '$.info.address') -> true
SELECT JSON_EXISTS('{"info":{"address":[{"town":"LA"},{"town":"Atlanta"}]}}', '$.info.addresses') -> false
SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}', '$.a[0].b') -> true
SELECT JSON_EXISTS('{"a": true}', $.[]b" -> Throws path parsing exception
SELECT JSON_EXISTS('{\"user\":{\"name\":null}}', '$.user.name') -> false

JSON_OBJECT JSON_OBJECT([keyName, keyValue [,...keyName, keyValue]])

Builds a JSON object string from a list of zero or more key-value pairs.

Returns the JSON object as a JSON string.

Key names must be non-null non-empty strings.

String values that are detected as JSON_ARRAY or JSON_OBJECT are inserted without escaping quotes. This allows building nested JSON structures. JSON_OBJECT converts the SQL NULL value into a JSON null value when generating the keyValue of the element. It does not remove NULL elements from the resulting object. SELECT JSON_OBJECT() -> '{}'
SELECT JSON_OBJECT('a', 1, 'b', 2) -> '{"a":1,"b":2}'
SELECT JSON_OBJECT('a', 1.0, 'b', 2.0) -> '{"a":1.0,"b":2.0}'
SELECT JSON_OBJECT('a', '[]') -> '{"a":[]}'
SELECT JSON_OBJECT('a', NULL, 'b', JSON_ARRAY(1,2)) -> '{"a":null,"b":[1.0,2.0]}'
SELECT JSON_OBJECT('a',NULL,'b',JSON_ARRAY('c','xxx')) -> '{"a":null,"b":{"c":"xxx"}}'
SELECT JSON_OBJECT('a') -> throws exception. Invalid number of arguments'
SELECT JSON_OBJECT(1, 1) -> throws exception. Parameter 1 is not a string'

JSON_ARRAY JSON_ARRAY(arrayValue [,...n])

Constructs JSON array text from zero or more expressions.

Returns the JSON array as a JSON string.

String values that are detected as JSON_ARRAY or JSON_OBJECT are inserted without escaping quotes. This allows building nested JSON structures.

JSON_ARRAY converts the SQL NULL value into a JSON null value when generating the value of the element in the JSON array. It does not remove NULL elements from the resulting array. SELECT JSON_ARRAY() -> '[]'
SELECT JSON_ARRAY('') -> '[""]'
SELECT JSON_ARRAY(1, '2') -> '[1,"2"]'
SELECT JSON_ARRAY(1.0) -> '[1.0]'
SELECT JSON_ARRAY(1,NULL) -> '[1,null]'
SELECT JSON_ARRAY(JSON_ARRAY(1)) -> '[[1]]]'
SELECT JSON_ARRAY(1, JSON_OBJECT('a', 1, 'b', 'hello')) -> '[1,{"a":[1,"b":"hello"]}]'

JSON_VALUE JSON_VALUE(jsonString, jsonPath [,defaultValue])

Determine whether a given string is valid JSON.

jsonString: A String data type that will be tested as a JSON string.
jsonPath: A valid SQL/JSON path to test in the input.

defaultValue: Optional. The default value will be returned if the path does not exist. The default value can be a scalar value. String default values are not delimited before returned.

Extracts a scalar value from a JSON string. An exception is thrown if there are no search results and a defaultValue was not included. An exception is thrown if a JSON Object or JSON Array is attempting to be returned.

Use JSON_QUERY to return objects and arrays. SELECT JSON_VALUE('{"a": true}', '$.a') -> true
SELECT JSON_VALUE('{"a": "xxx"}', '$.a') -> 'xxx'
SELECT JSON_VALUE('{"a": 1}', '$.a') -> 1.0
SELECT JSON_VALUE('{"a": 1.0}', '$.a') -> 1.0
SELECT JSON_VALUE('{"a": null}', '$.a') -> null
SELECT JSON_VALUE('{"a": 'null'}', '$.a') -> 'null'
SELECT JSON_VALUE('{"a": true}', '$.b') -> Throws exception - path doesn't exist and no default value
SELECT JSON_VALUE('{"a": { "b": "hello" } }', '$.a.b') -> Throws Exception - not returning a scalar
SELECT JSON_VALUE('{"a": { "b": [] } }', '$.a.b') -> Throws Exception - not returning a scalar
With default values:
SELECT JSON_VALUE('{"a": null}', '$.a', 'xx') -> null
SELECT JSON_VALUE('{"a": null}', '$.b', true) -> true

JSON_QUERY JSON_QUERY(jsonString, jsonPath [,defaultValue])

Extracts JSON values from a JSON string. The result is always returned as a STRING or null.

jsonString: The JSON string to query.
jsonPath: A valid SQL/JSON path.

defaultValue: Optional. defaultValue must be a STRING. The default will be returned if the path does not exist. The default value is not delimited before returned.

Use JSON_VALUE to return scalar values. SELECT JSON_QUERY('{"a": true}', '$.a') -> 'true'
SELECT JSON_QUERY('{"a": 1}', '$.a') -> '1'
SELECT JSON_QUERY('{"a": 1.0}', '$.a') -> '1.0'
SELECT JSON_QUERY('{"a": null}', '$.a') -> null
SELECT JSON_QUERY('{"a": "null"}', '$.a') -> '"null"'
SELECT JSON_QUERY('{"a": true}', '$.b') -> throws exception
SELECT JSON_QUERY('{"a": { "b": "hello" } }', $.a) -> '{"b":"hello"}'
SELECT JSON_QUERY('{"a": null}', '$.a', 'xx') -> null
SELECT JSON_QUERY('{"a": null}', '$.b', 'true') -> '"true"'
SELECT JSON_QUERY('{"a": 1}', '$.b', null) -> null
SELECT JSON_QUERY(JSON_OBJECT('a', JSON_ARRAY('c','d')), '$.a[1]' -> '"d"'

JSON_MODIFY JSON_MODIFY(jsonString, jsonPath ,newValue)

Updates the value of a property in a JSON string and returns the updated JSON string. The result is always returned as a STRING.

jsonString: The JSON string to search and modify.
jsonPath: A valid SQL/JSON path.

newValue: newValue can be a scalar value (number, boolean, string). JSON_MODIFY deletes the specified key if newValue is NULL.

If the property isn't present, JSON_MODIFY tries to insert the new value on the specified path. Insertion can fail if the property can't be inserted on the path.

Nested arrays are supported.

JSON_MODIFY escapes all special characters in the new value if the type of the value is STRING. A STRING value isn't escaped if it's properly formatted JSON produced by FOR JSON, JSON_QUERY, or JSON_MODIFY. SELECT JSON_MODIFY('{"a": true}', '$.a', false) -> '{"a": false}'
SELECT JSON_MODIFY('{"a": 1}', '$.a', '1') -> '{"a": "1"}'
SELECT JSON_MODIFY('{"a": 1.0}', '$.a', 1) -> '{"a": 1.0}'
SELECT JSON_MODIFY('{"a": null}', '$.a', NULL) -> '{}' - key is deleted
SELECT JSON_MODIFY('[0]', '$[1]', 1) -> '[0,1]'
SELECT JSON_MODIFY('[0]', '$[1][0][1]', 1) -> '[0,[[null,1]]]'
SELECT JSON_MODIFY('{\"a\":{\"b\":{\"c\":[\"xxx\"]}}}', '$.a.b.c[0]', 'yyy') ->
'{\"a\":{\"b\":{\"c\":[\"yyy\"]}}}'
SELECT JSON_MODIFY('{\"name\":\"John\",\"skills\":[\"C#\",[\"SQL\",\"Java\"]]}', '$.skills[1][4].b', 'xxx') ->
'{\"name\":\"John\",\"skills\":[\"C#\",[\"SQL\",\"Java\",null,null,{\"b\":\"xxx\"}]]}'

Aggregate Functions

COUNT

COUNT([DISTINCT] expression)

The COUNT function is an aggregate function that returns the number of rows returned by a query as a Long data type.

The result of the COUNT function depends on the argument that you pass to it:

By default, the COUNT function uses the ALL keyword whether you specify it or not. The ALL keyword means that all items in the group are considered including the duplicate values. For example, if you have a group (1, 2, 3, 3, 4, 4) and apply the COUNT function, the result is 6
If you specify the DISTINCT keyword explicitly, only unique non-null values are considered. The COUNT function returns 4 if you apply it to the group (1,2,3,3,4,4).

The COUNT(*) function returns the number of rows in a table including the rows that contain the NULL values.

DISTINCT: Optional. Indicates whether to include duplicate values. .
expression: *, column name or an expression.

SELECT COUNT(*) FROM system.stream → 10233
SELECT COUNT(_name) FROM system.stream → 10233
SELECT _component_name, COUNT(_name) AS streamCount FROM system.stream WHERE GROUP BY _component_name, COUNT(_name) ORDER BY _component_name, streamCount → Stream Count Per Component

SUM SUM([DISTINCT] expression)

The SUM function is an aggregate function that returns the sum of all non-null.

The SUM function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the SUM function performs the calculation.

If you want to calculate the sum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the SUM function is NULL if and only if all input values are NULL. DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression. SELECT SUM(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 10233
SELECT _component_name, _name, SUM(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream Totals Per Component Stream for the last 12 hours

TOTAL TOTAL([DISTINCT] expression)

The same as the SUM function except the result of the TOTAL function is zero, not NULL, if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name, or an expression. SELECT TOTAL(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 300
SELECT _component_name, _name, TOTAL(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream Totals Per Component Stream for the last 12 hours

MIN MIN([DISTINCT] expression)

The MIN function is an aggregate function that returns the mimimum of all non-null rows.

The MIN function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the MIN function performs the comparison.

If you want to calculate the minimum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the MIN function is NULL if and only if all input values are NULL. SELECT MIN(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 0
SELECT _component_name, _name, MIN(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream mimimums per Component Stream for the last 12 hours

MAX MAX([DISTINCT] expression)

The MAX function is an aggregate function that returns the maximum of all non-null rows.

The MAX function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the MAX function performs the comparison.

If you want to calculate the maximum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the MAX function is NULL if and only if all input values are NULL. SELECT MAX(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 24
SELECT _component_name, _name, MAX(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream maximums per Component Stream for the last 12 hours

AVG AVG([DISTINCT] expression)

The AVG function is an aggregate function that returns the average of all non-null rows.

The AVG function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the AVG function performs the comparison.

If you want to calculate the average of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the AVG function is NULL if and only if all input values are NULL. SELECT AVG(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 12.5
SELECT _component_name, _name, AVG(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

VAR VAR([DISTINCT] expression)

Returns the statistical variance of all values in the specified expression when the group of numbers being evaluated are only a partial sampling of the whole population.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the VAR function is NULL if and only if all input values are NULL or the set has only one value. SELECT VAR(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, VAR(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

VARP VARP([DISTINCT] expression)

Returns the statistical variance for the population for all values in the specified expression when the group of numbers being evaluated is complete - it's the entire population of values.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the VARP function is NULL if and only if all input values are NULL or the set has only one value. SELECT VARP(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, VARP(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

STDEV STDEV([DISTINCT] expression)

STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the STDEV function is NULL if and only if all input values are NULL or the set has only one value. SELECT STDEV(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, STDEV(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

STDEVP STDEVP([DISTINCT] expression)

STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the STDEVP function is NULL if and only if all input values are NULL or the set has only one value. SELECT STDEVP(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, STDEVP(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

PERCENTILEINC PERCENTILEINC([DISTINCT] expression , percent)

Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the PERCENTILEINC function is NULL if and only if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
percentile: The percentile numeric value greater than or equal to zero and less than or equal to one. SELECT PERCENTILEINC(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, PERCENTILEINC(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

PERCENTILEEXC PERCENTILEEXC([DISTINCT] expression , percent)

The PERCENTILE.EXC function returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the PERCENTILEEXC function is NULL if and only if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
percentile: The percentile numeric value greater than zero and less than one. SELECT PERCENTILEEXC(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, PERCENTILEEXC(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

STRING_AGG STRING_AGG([DISTINCT] expression, delimiter)

Concatenates the values of string expressions and places delimiter values between them. The delimiter is not added at the end of the string. NULL values are skipped.

The result of the STRING_AGG function is NULL if the group is empty (no non-NULL values).

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: Column name or an expression. Values are converted to strings before concatenation.
delimiter: A string expression used as separator between concatenated values. SELECT STRING_AGG(val, ', ') FROM JSON_TABLE('[{"val":"a"},{"val":"b"},{"val":"c"}]') WITH (val STRING) → 'a, b, c'
SELECT STRING_AGG(val, ',') FROM JSON_TABLE('[{"val":"a"},{"val":null},{"val":"c"}]') WITH (val STRING) → 'a,c'

Control Functions

IF / IIF IIF(condition, value_if_true, value_if_false)
IF(condition, value_if_true, value_if_false)

Both IF and IFF are supported and function the same.

It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false.

Returns the data type of the value_if_true or value_if_false.

condition: The condition expression.
value_if_true: Optional. Value to return if condition is true
value_if_false: Optional. Value to return if condition is false IIF(condition, value_if_true, value_if_false)
SELECT IIF( kWh > 50, cost*0.5, cost)
SELECT IF( 100 > 50,'High', 'Low') → 'High'

CASE CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

input_expression: Optional. The condition base expression to be compared with each when_expression.
when_expression: A boolean expression.
result_expression: The result to use if the preceding when_expression is false.
else_result_expression: Optional. The result to use if no conditions were true or the base expression was null. Evaluates a list of conditions and returns one of multiple possible result expressions.

The optional input_expression that occurs in between the CASE keyword and the first WHEN keyword is called the "base" expression. There are two fundamental forms of the CASE expression: those with a base expression and those without.

In a CASE without a base expression, each WHEN expression is evaluated and the result treated as a boolean, starting with the leftmost and continuing to the right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression that evaluates to true. Or, if none of the WHEN expressions evaluate to true, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions are true, then the overall result is NULL.

A NULL result is considered untrue when evaluating WHEN terms.

In a CASE with a base expression, the base expression is evaluated just once and the result is compared against the evaluation of each WHEN expression from left to right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression for which the comparison is true. Or, if none of the WHEN expressions evaluate to a value equal to the base expression, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions produce a result equal to the base expression, the overall result is NULL.

When comparing a base expression against a WHEN expression, the same collating sequence, affinity, and NULL-handling rules apply as if the base expression and WHEN expression are respectively the left- and right-hand operands of an = operator.

If the base expression is NULL then the result of the CASE is always the result of evaluating the ELSE expression if it exists, or NULL if it does not. SELECT _component_id, _id,
CASE
WHEN _sample > 100 THEN 'High'
WHEN _sample BETWEEN 50 AND 100 THEN 'Medium'
ELSE 'Low'
END AS sample_category
FROM system.stream;

SELECT _component_id, _id, _sample,
CASE _sample
WHEN 'H' THEN 'High'
WHEN 'M' THEN 'Medium'
ELSE 'Low'
END AS sample_category
FROM system.stream;

COALESCE / IFNULL / NVL COALESCE(expr1, expr2 [, ...])
IFNULL(expr1, expr2 [, ...])
NVL(expr1, expr2 [, ...])

Returns the first non-NULL argument. If all arguments are NULL, returns NULL.

COALESCE, IFNULL, and NVL are supported and function the same.

COALESCE requires at least two arguments. It accepts any number of additional arguments.

The return data type is determined by the first non-NULL argument.

expr1: The first expression to evaluate.
expr2: The second expression to evaluate.
...: Optional. Additional expressions to evaluate. SELECT COALESCE(NULL, 'b') → 'b'
SELECT COALESCE('a', 'b') → 'a'
SELECT COALESCE(NULL, NULL, 'c') → 'c'
SELECT COALESCE(NULL, NULL) → NULL

NULLIF NULLIF(expr1, expr2)

Returns NULL if expr1 equals expr2; otherwise returns expr1.

Commonly used to prevent division-by-zero errors by converting a zero denominator to NULL, which causes the division to return NULL instead of raising an error.

The return data type is the data type of the first argument.

expr1: The expression to return if not equal to expr2.
expr2: The expression to compare against expr1. SELECT NULLIF('a', 'a') → NULL
SELECT NULLIF('a', 'b') → 'a'
SELECT 100 / NULLIF(0, 0) → NULL

ISNULL ISNULL(expr, default)

Returns expr if it is not NULL; otherwise returns default. Functionally equivalent to COALESCE(expr, default).

The return data type is determined by the first non-NULL argument.

expr: The expression to evaluate.
default: The value to return if expr is NULL. SELECT ISNULL(5, 10) → 5
SELECT ISNULL(NULL, 10) → 10
SELECT ISNULL(NULL, 'default') → 'default'

Other Functions

CAST

CAST(value AS typename([precision][,scale]))

The CAST() function converts a value (of any type) into a specified data type. Also look at the CONVERT() function.

Cast Type Names (case insensitive):

STRING (aliases: TEXT, VARCHAR)
BOOLEAN (alias: BOOL)
LONG (aliases: INT8, BIGINT, INTEGER, INT4, SMALLINT, INT2, TIMESTAMP, TIMESTAMPTZ)
DOUBLE (aliases: FLOAT8, FLOAT4, REAL)
BIGDECIMAL (aliases: NUMERIC, DECIMAL)

PostgreSQL / ANSI aliases were added in the May 2026 release. They map to the canonical type on the same line and are accepted in CAST, the postfix :: cast operator, CREATE TABLE column declarations, and JSON_TABLE / CSV_TABLE column declarations.

value: The number to be cast.
typename: The type to cast to (see list above).
precision: Optional. Precision of BigDecimal casts.
scale: Optional. Scale of BigDecimal casts.

SELECT CAST(25.65 AS LONG) → 25
SELECT CAST('25.65' AS LONG) → 25
SELECT CAST('25.65' AS DOUBLE) → 25.65
SELECT CAST('25.6534' AS BIGDECIMAL) → 25.6534
SELECT CAST('25.6534' AS BIGDECIMAL(10,2)) → 25.65
SELECT CAST(25.65 AS STRING) + 'gallons' → '25.65 gallons'
SELECT CAST(1 AS BOOLEAN) → true
SELECT CAST(2 AS BOOLEAN) → true
SELECT CAST(0 AS BOOLEAN) → false
SELECT CAST('off' AS BOOLEAN) → false
SELECT CAST('no' AS BOOLEAN) → false
SELECT CAST('abc' AS BOOLEAN) → true

CONVERT

CONVERT(typename, expression)

Converts a value to a specified data type. Similar to CAST but uses function syntax. Also look at the CAST expression.

Type Names (case insensitive):

STRING
BOOLEAN
LONG
DOUBLE
BIGDECIMAL

typename: A string specifying the target type (see list above).
expression: The value to convert.

SELECT CONVERT('LONG', '25.65') → 25
SELECT CONVERT('STRING', 42) → '42'
SELECT CONVERT('DOUBLE', '3.14') → 3.14
SELECT CONVERT('BOOLEAN', 1) → true

Table Functions

JSON_TABLE

JSON_TABLE( jsonSource { sourceType, jsonPath } ) [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_json_path ] } [ ,...n ] )

JSON_TABLE is a table-valued function that parses JSON text and returns objects and properties from the jsonSource as rows and columns. In other words, JSON_TABLE provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns. Since JSON_TABLE returns a set of rows, you can use JSON_TABLE in the FROM clause of a GS SQL statement.

jsonSource: The JSON source can be either a string or a URL pointing to a component stream file that contains JSON. This JSON can be either an object or an array. URLs will be URL Decoded by the SQL engine. URL Examples:

http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/{epochMillis time}/{fileName}?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/generator1/stream/amperes/feed/file/1735922429463/jsonTester.txt?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/{fileName} <-- Returns the latest file for the stream in the current organization

sourceType: Optional. Default is STRING. Options are STRING or URL. Only URLs to component file streams within the organization are allowed (at least for today)
jsonPath: Optional. A valid JSON path string. jsonSource can contain nested sub-objects. If you want to parse a sub-object from within jsonSource, you can specify a jsonPath parameter for the JSON sub-object.

By default, the JSON_TABLE table-valued function returns a {key:value} pair found in jsonSource as three columns: the key name as STRING, the value as STRING, and the type as LONG. As an alternative, you can explicitly specify the schema of the result set that JSON_TABLE returns by providing with_clause.

with_clause: Optional. with_clause contains a list of columns with their types for JSON_TABLE to return. By default, JSON_TABLE matches keys in jsonSource with the column names in with_clause (in this case, matches keys implies that it is case sensitive). If a column name does not match a key name, you can provide an optional column_json_path, which is a JSON Path string that references a key within the jsonSource.

with_clause colName types (case insensitive):

STRING
BOOLEAN
LONG
DOUBLE
BIGDECIMAL

JSON_TABLE responds four different ways depending on whether a JSON object or a JSON array is passed in and whether a with_clause is included.

Example 1: JSON Object Without with_clause:

One row for each property. key as STRING, value as STRING and type as LONG.

SELECT key,
       value,
       type
FROM JSON_TABLE('
{
  "name": "Bob",
  "age": 32,
  "height": 1.82,
  "married": true,
  "city": null,
  "cars": ["Ford","Tesla"],
  "job":{"name":"IT","Start Date":1735403955000}
}
')

Results:

key	value	type
name	Bob	1
age	32	2
height	1.82	2
married	true	3
city	null	0
cars	["Ford","Tesla"]	4
job	{"name":"IT","Start Date":1735403955000}	5

Example 2: JSON Object With with_clause:

One row. Each property is a column.

SELECT name,
       age,
       married,
       city
FROM JSON_TABLE('
{
  "name": "Bob",
  "age": 32,
  "height": 1.82,
  "married": true,
  "city": null,
  "cars": ["Ford","Tesla"],
  "job":{"name":"IT","Start Date":1735403955000}
}
') WITH (name    STRING,
         age     LONG,
         married BOOLEAN,
         city    STRING)

Results:

name	age	married	city
Bob	32	true	null

Example 3: JSON Array Without with_clause:

One row. Each property is a column. key as STRING, value as STRING and type as LONG.

Results:

key	value	type
0	Ford	1
1	Tesla	1

Example 4: JSON Array With with_clause:

Each array item is a row. Each property is a column.

SELECT name,
       age
FROM JSON_TABLE('[
   {"name":"Bob","age":1},{"name":"Joe","age":22},{"name":"Tim","age":15},
   {"name":"Tom","age":24},{"name":"Carl","age":56},{"name":"Igor","age":42}]' )
WITH (name STRING ,
      age LONG) AS workers

Results:

name	age
Bob	1
Joe	22
Tim	15
Tom	24
Carl	56
Igor	42

Example 5: JSON Object With with_clause:

Combining nested objects while using column_json_path.

SELECT name,
       age,
       car2,
       jobName
FROM JSON_TABLE('
{
  "name": "Bob",
  "age": 32,
  "height": 1.82,
  "married": true,
  "city": null,
  "cars": ["Ford","Tesla"],
  "job":{"name":"IT","Start Date":1735403955000}
}
') WITH (name     STRING '$.name',
         age      LONG   '$.age',
         car2     STRING '$.cars[1]',
         jobName  STRING   '$.job.name' )

Results:

name	age	car2	jobName
Bob	32	Tesla	IT

Example 6: jsonSource as a file stream file:

One row. Each property is a column. Source is a GroveStreams organization file stream file. The file contains the JSON from Example 2.

SELECT name,
       age,
       married,
       city
FROM JSON_TABLE('
https://grovestreams.com/api/comp/fcomp/stream/jsonfile/feed/file/1735922429463/jsonTester.txt
',
   SOURCETYPE = URL
) WITH (name    STRING,
         age     LONG,
         married BOOLEAN,
         city    STRING)

Results:

name	age	married	city
Bob	32	true	null

CSV_TABLE

CSV_TABLE( csvSource, { 'firstrow', 'separator', 'quotechar', 'escapechar', 'strictquotes', 'ignoreleadingwhitespace, maxerrors' }
<with_clause> ::= WITH ( { colNum colName type } [ ,...n ] )

CSV_TABLE is a table-valued function that parses comma-separated values (CSV) text, returning the data from csvSource as rows and columns. Essentially, CSV_TABLE offers a rowset view of a CSV document. You can explicitly define which columns to include in the rowset and how to populate them. Since CSV_TABLE returns a set of rows, it can be used in the FROM clause of a GS SQL statement.

CSV_TABLE parses multiple lines, where each line ends with "\n".

csvSource: The CSV expression string, or a URL of a file stream within the organization, is parsed into columns and rows. URLs will be URL-decoded by the SQL engine. URL examples:

http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/{epochMillis time}/{fileName}?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/generator1/stream/amperes/feed/file/1735922429463/csvTester.txt?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/{fileName} <-- Returns the latest file for the stream in the current organization

sourceType: Optional. Default is STRING. Options are STRING or URL. Only URLs to component file streams within the organization are allowed (at least for today)
firstrow: Optional. Default is 1. The row to start parsing from. 1 is the first row. Allows header rows to be ignored.
separator: Optional. Default is ',' (comma). The delimiter to use for separating entries.
quotechar: Optional. Default is '"' (double quote). The character to use for quoted elements).
escapechar: Optional. Default is '\' (backslash). The character to use for escaping a separator or quote.
strictquotes: Optional. Default is FALSE. When TRUE, characters outside quotes are ignored.
ignoreleadingwhitespace: Optional. Default is TRUE. If TRUE, leading whitespace before a quoted field is ignored.
maxerrors: Optional. Default is 0. Specifies the maximum number of syntax errors or non-conforming parsed rows which can occur before CSV_TABLE throws an exception. Each non-conforming parsed row segment exception is included in the count for calls without WITH statements.

By default, the CSV_TABLE table-valued function returns three columns: row, column, and value (types LONG, LONG, STRING, respectively). Alternatively, you can explicitly define the result set's schema by using the with_clause.

with_clause: Optional. The with_clause specifies a list of column numbers, names, and value types that CSV_TABLE will return. CSV_TABLE matches columns in csvSource with the colNum from with_clause (where the first column is 1). Values are converted from strings to the specified type.

with_clause column types (case insensitive):

STRING
BOOLEAN
LONG
DOUBLE
BIGDECIMAL

CSV_TABLE behaves in two distinct ways depending on the presence of a with_clause:

Example 1: Without with_clause:

One row for each line's delimited values in the CSV string. row and column are LONG type. value is STRING type. New lines (\n) exist at the end of each csvSource line. They're not visible here.

SELECT row,
column,
value
FROM CSV_TABLE(
'"2021-04-01T05:00:00.000",24
"2021-04-01T06:00:00.000",23
"2021-04-01T07:00:00.000",22')

Results:

row	column	value
1	1	2021-04-01T05:00:00.000
1	2	24
2	1	2021-04-01T06:00:00.000
2	2	23
3	1	2021-04-01T07:00:00.000
3	2	22

Example 2: With with_clause:

All possible options are being used. Four header lines are ignored with 'FIRSTROW' set to five. New lines (\n) exist at the end of each csvSource line. They're not visible here.

SELECT time,
       pressure
FROM CSV_TABLE(
'|"Component 1"
|"component1"
|"Pressure"
|"pressure"
xx"2021-04-01T05:00:00.000"yy|"24"
"2021-04-01T06:00:00.000" |  "23"
"2021-04-01T07:00:00.000" | "22"',
   SOURCETYPE = STRING,
   FIRSTROW = 5,
   QUOTECHAR = '"',
   SEPARATOR = '\|',
   ESCAPECHAR = '\\',
   STRICTQUOTES = TRUE,
   IGNORELEADINGWHITESPACE = TRUE,
   MAXERRORS = 1
) WITH (
   1 time STRING,
   2 pressure LONG)
WHERE (
  pressure > 22)

Results:

time	pressure
2021-04-01T05:00:00.000	24
2021-04-01T06:00:00.000	23

Example 3: csvSource is a a file stream

Same data as Example 1. URL is shortened and returns the latest file for the stream.

SELECT row,
       column,
       value
FROM CSV_TABLE(
'api/comp/fcomp/stream/csvfile/feed/file/csvTester.csv',
   SOURCETYPE = URL
)

Results:

row	column	value
1	1	2021-04-01T05:00:00.000
1	2	24
2	1	2021-04-01T06:00:00.000
2	2	23
3	1	2021-04-01T07:00:00.000
3	2	22

EXTERNAL_QUERY

EXTERNAL_QUERY( connectionId, remoteSql )

EXTERNAL_QUERY is a table-valued function that executes SQL on a remote JDBC database and returns the result as a table. The remote SQL is written in the remote database's native dialect (MySQL, PostgreSQL, SQL Server, Oracle, etc.), not GS SQL.

Since EXTERNAL_QUERY returns a set of rows, it can be used in the FROM clause of a GS SQL statement, including JOINs with other GS tables, views, template tables, JSON_TABLE, CSV_TABLE, or other EXTERNAL_QUERY calls.

connectionId: A string literal specifying the ID of a JDBC connection defined in Connectors. This is the user-facing ID (e.g., 'mysql_employees'), not the internal UID.
remoteSql: A string literal containing the SQL to execute on the remote database. This SQL is passed directly to the remote database — use the remote database's syntax, functions, and table names.

Cross-database queries: The connection's Default Database sets the unqualified table context. To query tables in other databases on the same server, use qualified names in your remote SQL (e.g., 'other_db.table_name' for MySQL, 'other_db.dbo.table_name' for SQL Server).

Column types: Column names and types are automatically discovered from the remote database's result set metadata. JDBC types are mapped to GS SQL types: VARCHAR/CHAR → STRING, INTEGER/BIGINT → LONG, DOUBLE/FLOAT → DOUBLE, DECIMAL/NUMERIC → BIGDECIMAL, BOOLEAN → BOOLEAN, TIMESTAMP/DATE/TIME → LONG (epoch milliseconds).

Security: The user must have Tools rights on the JDBC connector to execute EXTERNAL_QUERY against it.

Example 1: Simple query

SELECT *
FROM EXTERNAL_QUERY('mysql_employees', 'SELECT dept_no, dept_name FROM departments') d

Example 2: Aggregation on remote side

SELECT d.dept_name, d.cnt
FROM EXTERNAL_QUERY('mysql_employees',
'SELECT dept_name, count(*) as cnt FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no GROUP BY dept_name') d

Example 3: Join remote data with local GS template table

SELECT eq.emp_name, s._component_name, s.temperature
FROM EXTERNAL_QUERY('mysql_conn', 'SELECT emp_name, sensor_id FROM employees WHERE dept = 5') eq
JOIN sensor s ON s._component_id = eq.sensor_id

Example 4: Cross-database query

SELECT *
FROM EXTERNAL_QUERY('mysql_conn', 'SELECT * FROM sales_db.orders WHERE order_date > ''2025-01-01''') o

Literals - Constants

NOW The current datetime in Long epoch milliseconds. The NOW literal is set at the start of the SQL process and used by all steps of the query so that the NOW time is the same for all operations.

Both NOW (no parentheses) and NOW() (with parentheses) are accepted for standard SQL compatibility. SELECT NOW → 1618885675191
SELECT NOW() → 1618885675191
SELECT FormatDate(NOW) → '2021-04-19T21:28:37'

CURRENT_TIMESTAMP Same as NOW. See above. SELECT CURRENT_TIMESTAMP → 1618885675191
SELECT FormatDate(CURRENT_TIMESTAMP) → '2021-04-19T21:28:37'

CURRENT_DATE Returns Now formatted as a string using the format "yyyy-MM-dd". SELECT CURRENT_DATE → '2021-04-19'

CURRENT_TIME Returns Now formatted as a string using the format "HH:mm:ss". SELECT CURRENT_TIME → '21:28:37'

TEQ™ Syntax

Temporal Entity Query (TEQ) allows you to use component template IDs as table names and stream IDs as column names. This is the recommended query style for organizations that use component templates. See the TEQ Guide for full documentation and examples.

Template Table: Use a component template ID in the FROM clause. The engine matches the name (case-insensitive) against your organization's templates.

SELECT _component_name, temperature FROM sensor	Template ID as table name; stream IDs as columns
SELECT s._component_name, s.temperature FROM sensor s	With table alias

Per-Column Temporal Parameters: In TEQ, temporal parameters are applied to individual stream columns by name, rather than to the _sample column.

SELECT _component_name, kwh(range(last=100)) FROM meter	Per-column temporal parameters
SELECT _component_name, kwh(range(sd=-1d)), voltage FROM meter	Mixed temporal and _last_value columns
SELECT _component_name, kwh(cycleId='day', stat='avg') FROM meter	Cycle aggregation on a stream column
SELECT _component_name, kwh(lag=1, range(last=100)) FROM meter	Previous value (window parameter)
SELECT _component_name, kwh(running='sum', range(currentCycle='month')) FROM meter	Running total (window parameter)

Component System Columns: Always available in template table queries: _component_name (String), _component_uid (String), _component_id (String), _component_created_date (Long), _folder_path (String).

FK JOINs: Join template tables using foreign key streams.

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

FK JOIN between template tables

Temporal Parameters

The Sample result column, within a Select statement, allows for optional parameters to filter and aggregate the list of samples returned. Each parameter may contain other parameters such as the Range () parameter.

Sample Range Parameter:

Example: Select _Sample( Range(sd=-1d, ed=now) ) From system.stream

The range parameter is used to filter samples by time. A Range has a start datetime (inclusive) and end datetime (exclusive) and is represented by sd and ed parameters.

There are four ways to specify a Range:

1. No range specified: The stream's first and last sample's datetime are used. All samples will be returned.
2. sd and ed parameters are used
3. currentCycle or previousCycle are used
4. last is used and returns the last number of samples from ed (exclusive)

sd and ed can be set by using the following:

ISO8601 formatted string → sd='2021-03-01T00:00:00-05:00
Epoch Millis Long number → sd=1617253200000
now: The current time → ed=now
streamSd: The stream's earliest sample's datetime → sd=streamSd
streamEd: The stream's latest sample's datetime → ed=streamEd
currentCycle: The ID of a cycle used. The current datetime will be used to determine the cycle boundaries for sd and ed → currentCycle='day'
previousCycle: The ID of a cycle to be used. The current datetime will be used to determine the current cycle boundaries and then the previous cycle will be used for sd and ed → previousCycle='day'
if sd is missing, it is assigned to streamSd
if ed is missing, it is assigned to streamEd
if sd is used with a modifier, the modifier is applied to the ed's datetime.

Case-sensitive datetime Modifiers can be used to add or subtract time spans while setting the sd or ed parameters:

s: Second
m: Minute
h: Hour
d: Day
w: Week
M: Month
Y: Year

Range Examples:

SELECT Sample FROM system.stream	Return all samples
SELECT _Sample( Range(sd='2021-03-01T00:00:00-05:00', ed='2021-04-01T00:00:00-05:00') ) FROM system.stream	Return fixed range using ISO8601
SELECT _Sample( Range(sd=1614578400000, ed=1617253200000) ) FROM system.stream	Return fixed range using epoch millis
SELECT _Sample( Range(sd=-1d, ed=now) ) FROM system.stream	Return last day from current datetime
SELECT _Sample( Range(sd=now, ed=now+1y) ) FROM system.stream	Return from now to one year into the future
SELECT _Sample( Range(sd=-1d, ed=streamEd) ) FROM system.stream	Return last day from stream's last sample datetime
SELECT _Sample( Range(sd=-1hr ed=streamEd-1hr) ) FROM system.stream	Return last hour from stream's last sample datetime
SELECT _Sample( Range(currentCycle='month') ) FROM system.stream	Return current month
SELECT _Sample( Range(previousCycle='month') ) FROM system.stream	Return previous month
SELECT _Sample( Range(last=100, ed=streamEd) ) FROM system.stream	Return the last 100 samples
SELECT _Sample( Range(last=100, ed=now-1w) ) FROM system.stream	Return the last 100 samples from one week ago

Sample TimeFilterId Parameter:

Example: Select _Sample( TimeFilterId='weekdayOnly' ) From system.stream

The TimeFilterId property will apply the GroveStreams Time Filter, with the assigned ID, used to filter samples based on the Time Filter definition and sample times.

Time filtering will be applied to intervals after they are rolled-up (aggregated) when the CycleId parameter is present.

TimeFilter Examples:

SELECT _Sample(TimeFilterId='weekends') FROM system.stream	Return all samples with Saturday or Sunday times.
SELECT _Sample(TimeFilterId='weekends', Range(sd=-4w)) FROM system.stream	TimeFilterId with a Range. Return all samples with Saturday or Sunday times over the last 4 weeks.

Sample CycleId and Stat Parameters:

Example: Select _Sample( cycleId='day', stat='max' ) as maximum, FormatDate(_Sample( cycleId='day', stat='MAXOCCURRENCE' )) as 'maximum time' From system.stream

The CycleId is the ID of a GroveStreams cycle defined in your Organization. Samples returned will be aggregated into Intervals to match the cycle using the default rollup method in the stream definition or by using the statistic passed in.

Most of the statistics below are calculated for each cycle in a stream's rollup calendar as samples arrive an are saved. The timezone of the cycle or the component is used to determine each cycle range. The query engine will identify if the requested stat has been precalculated and return that result quickly. The query engine will calculate cycle statistics if they have not already been calculated and saved. Configure stream rollup calendars to improve the retrieval of interval statistics.

Stat options (case insensitive):

FIRST
LAST
MIN
MAX
AVG
SUM
MINOCCURRENCE - Epoch Millis datetime of the minimum
MAXOCCURRENCE - Epoch Millis datetime of the maximum
GAPCOUNT
NONGAPCOUNT
INTVLCOUNT
MILLISECCOUNT
NONGAPMILLISECCOUNT
STDDEVP
STDDEVA
SAMPLECOUNT
SUMSIN
SUMCOS

CycleId and Stat Examples:

SELECT _Sample(cycleId='month') FROM system.stream	Return all samples aggregated into month intervals using the default stream rollup method
SELECT _Sample(cycleId='month', stat='sum') FROM system.stream	Return all samples aggregated into month intervals. Samples are aggregated using SUM.
SELECT _Sample(cycleId='month', stat='sum', Range(currentCycle='year')) FROM system.stream	Return 12 month intervals for the current year. Samples are aggregated into each month using SUM.
Select _Sample( cycleId='day', stat='max' ) as maximum, FormatDate(_Sample( cycleId='day', stat='MAXOCCURRENCE' )) as 'maximum time' From system.stream	Return 12 month intervals each with the maximum sample value for each month and the datetime it occurred.

Sample GapFill Parameter:

Example: Select _Sample( cycleId='day', GapFill(head=0 , middle='avg', tail='previousval'), Range(currentCycle='year') ) from system.stream Where _component_id='mainSubstation' and _id='kw'

The GapFill parameter allows gaps, NULL values, to be replaced for interval streams. GapFilling takes place after all other temporal parameter logic has been applied and prior to other SQL logic such as WHERE filtering and aggregation functions. GapFilling respects the TimeFilter parameter when it is included. TimeFiltered intervals will remain as gaps (NULLS) during gap filling.

Gap filling will take place after intervals are rolled-up (aggregated) when the CycleId parameter is present.

GapFilling will only use values within the time range selected for calculating gap replacement values. For example, it will not attempt to find a 'previousVal' from the store prior to the time range being processed by the SQL statement.

GapFill options (case insensitive):

HEAD : How to fill gaps at the start of the request time range :

NEXTVAL: Use the first non-gap value encountered after gap sequence. Must be in single quotes: head='NEXTVAL'
Value: A value to assign head gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

MIDDLE : How to fill gaps at the middle of the request time range:

PREVIOUSVAL: Use the non-gap value encountered prior to the gap sequence. Must be in single quotes: head='PREVIOUSVAL'
NEXTVAL: Use the first non-gap value encountered after gap sequence. Must be in single quotes: head='NEXTVAL'
MIN: Fill with minimum of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='MIN'
MAX: Fill with maximum of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='MAX'
AVG: Fill with average of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='AVG'
SPLINE: Fill with Apache's SplineInterpolator for cubic spline creation. Interpolates missing values within the valid time range of PREVIOUSVAL and NEXTVAL. Needs at least 4 valid data points for interpolation. Must be in single quotes: middle='SPLINE'
Value: A value to assign middle gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

TAIL : How to fill gaps at the end of the request time range:

PREVIOUSVAL: Use the non-gap value encountered prior to the gap sequence. Must be in single quotes: tail='PREVIOUSVAL'
Value: A value to assign tail gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

Process order is HEAD, TAIL, MIDDLE If all three options are used. Use head=value to fill an entire result set with a value if a requested result is all gaps.

GapFill Examples:

SELECT _Sample( GapFill(middle=0.0) ) FROM system.stream WHERE _id='xx'	Return all interval samples with gaps set to zero.
SELECT _Sample( GapFill(middle='off') ) FROM system.stream WHERE _id='xx'	Return all interval samples with gaps set to the string 'off'.
SELECT _Sample( cycleId='hour', stat='sum', timeFilterId='weekendAfternoon', GapFill(head='nextval', middle='avg', tail=0), Range(currentCycle='year') ) FROM system.stream WHERE _id='xx'	Return all hourly interval samples with weekend afternoon hour gaps filled for the current year.

Sample Window Parameters:

Example: Select _Sample( lag=1, Range(last=100) ) From system.stream Where _component_id='mainSubstation' and _id='kw'

Window parameters perform positional and sliding-window operations on sample data, providing the same capabilities as standard SQL window functions (LAG(), LEAD(), sliding aggregates, running totals) but as temporal parameters. This approach is faster than SQL WINDOW/OVER() syntax because it operates directly on the sample data arrays before tuple emission.

Window parameters are applied after GapFill (if present) and before WHERE filtering and aggregation functions.

Window Parameters (case insensitive):

Positional Parameters:

lag=N : Returns the sample value N positions before the current one, skipping time-filtered positions. N must be a positive integer. Out-of-bounds positions return NULL.
lead=N : Returns the sample value N positions after the current one, skipping time-filtered positions. N must be a positive integer. Out-of-bounds positions return NULL.
lagdefault=val : Optional default value when lag goes out of bounds (instead of NULL). Numbers, strings (in quotes), or booleans.
leaddefault=val : Optional default value when lead goes out of bounds (instead of NULL). Numbers, strings (in quotes), or booleans.

Aggregate Window Parameters:

slide=N : Sliding window of N positions. N must be an integer greater than 1. Must be used with slidestat. Computes the aggregate over a trailing window of N samples ending at the current position.
slidestat='fn' : Required when slide is used. The aggregate function for the sliding window. Supported values (in single quotes): 'sum', 'avg', 'min', 'max', 'first', 'last', 'count'.
running='fn' : Cumulative (running) aggregate from the first sample to each position. Supported values (in single quotes): 'sum', 'avg', 'min', 'max', 'first', 'last', 'count'.

Rules:

lag and lead are mutually exclusive — cannot be used on the same column.
slide and running are mutually exclusive — cannot be used on the same column.
Positional parameters (lag/lead) cannot be combined with aggregate parameters (slide/running) on the same column.
slide and running require numeric sample types (DOUBLE, LONG, INTEGER, etc.). They do not support STRING types.
lag and lead work with any sample type including STRING.

Time Filter Behavior:
Window parameters respect both the stream's reference time filter and the query-time TimeFilterId parameter. Samples that are excluded by either time filter are skipped by window functions:

lag and lead skip filtered positions when counting N. For example, lag=1 returns the previous non-filtered sample, not simply the adjacent position.
slide and running exclude filtered positions from their aggregate computations (sums, averages, etc.).
Filtered positions themselves produce NULL output.
When both a stream reference time filter and a TimeFilterId parameter are present, a sample must pass both filters to be included.

Window Parameter Examples:

SELECT _Sample( lag=1, Range(last=100) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Previous sample value (lag by 1). First position is NULL.
SELECT _Sample( lead=1, Range(last=100) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Next sample value (lead by 1). Last position is NULL.
SELECT _Sample( lag=1, lagdefault=0, Range(last=100) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Previous sample with default of 0 instead of NULL at the boundary.
SELECT _Sample( slide=3, slidestat='avg', Range(sd=-1d) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	3-sample sliding average.
SELECT _Sample( slide=5, slidestat='max', Range(sd=-1d) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	5-sample sliding maximum.
SELECT _Sample( running='sum', Range(currentCycle='month') ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Running (cumulative) sum from start to each position.
SELECT _Sample( running='avg', Range(currentCycle='month') ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Running average from start to each position.
SELECT _sample - _Sample( lag=1, Range(last=100) ) AS 'delta' FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Difference between current and previous sample (delta calculation).
SELECT _Sample( lag=1, timeFilterId='businessHours', Range(sd=-1d) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Previous sample, skipping samples outside business hours. Filtered positions return NULL.
SELECT _Sample( slide=3, slidestat='avg', timeFilterId='weekdays', Range(sd=-1M) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	3-sample sliding average over weekday samples only.

TEQ Examples: Window parameters work on TEQ stream columns too:

SELECT _component_name, kwh(lag=1, range(last=100)) FROM meter	Previous kWh value per component.
SELECT _component_name, temperature(slide=5, slidestat='avg', range(sd=-1d)) FROM sensor	5-sample sliding average temperature.
SELECT _component_name, kwh(running='sum', range(currentCycle='month')) FROM meter	Cumulative kWh total for the current month.