Function reference#

SQL queries may only call functions on this allowlist. Anything else is rejected before execution with Function not allowed: <name>.

The live list is served by the REST API, so it is the authoritative answer if this page and your deployment disagree:

curl --request GET '{API_URL}/sql/v1/catalog' \
  --header 'Authorization: Bearer <your-api-key>'

Where each function works#

Queries route to one of two backends depending on which tables they read: the telemetry tables (points_double, points_int, points_string, points_struct, logs, channels) or the metadata tables (assets, runs, run_assets, datasets, events, event_assets).

Most functions work on both. Some work only on the telemetry backend, and calling one against metadata tables fails with Function <NAME> is not supported for metadata-only queries. Those are marked Telemetry only below.

One function, CURRENT_TIME, is the reverse: it works only on the metadata backend, and calling it over telemetry tables fails with Function CURRENT_TIME is not supported for telemetry queries. It is marked Metadata only below.

The check applies per subtree, not per query, so in a query spanning both backends a telemetry-only function is fine over the telemetry side.

Aggregate functions#

Function

Notes

AVG(numeric)

COUNT(*), COUNT(expr)

MAX(expr)

MIN(expr)

SUM(numeric)

STDDEV_POP(numeric)

Population standard deviation.

STDDEV_SAMP(numeric)

Sample standard deviation.

STDDEV(numeric)

Synonym for STDDEV_SAMP.

VAR_POP(numeric)

Population variance.

VAR_SAMP(numeric)

Sample variance.

RMS(numeric)

Root mean square.

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY numeric)

Continuous percentile. fraction is a number from 0 to 1, so PERCENTILE_CONT(0.5) is the median.

MIN_BY(value, key)

Telemetry only. Returns value from the row with the smallest key.

MAX_BY(value, key)

Telemetry only. Returns value from the row with the largest key.

STRING_AGG(value, separator)

Concatenates value across the group, joined by separator. The separator is required, and ORDER BY inside the call is rejected.

ARRAY_AGG(value)

Telemetry only. Collects value across the group into an array. ORDER BY inside the call is rejected.

MIN_BY and MAX_BY answer “what was the reading when something else was at its highest or lowest,” which plain MIN and MAX cannot express:

SELECT MAX_BY(value, ts) AS last_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'

PERCENTILE_CONT takes its column in a WITHIN GROUP clause rather than as an argument:

SELECT channel,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
GROUP BY channel

Numeric functions#

Function

Notes

ABS(numeric)

CEIL(numeric)

CEILING is accepted as a synonym.

FLOOR(numeric)

LN(numeric)

LOG10(numeric)

MOD(numeric, numeric)

POWER(numeric, numeric)

ROUND(numeric [, scale])

Rounds to the nearest integer, or to scale decimal places when specified.

SQRT(numeric)

IS_NAN(numeric)

Returns whether the value is NaN.

UNHEX(string)

Telemetry only. Parses a hexadecimal string and returns BIGINT.

SIN(numeric)

COS(numeric)

TAN(numeric)

ASIN(numeric)

ACOS(numeric)

ATAN(numeric)

ATAN2(numeric, numeric)

RADIANS(numeric)

DEGREES(numeric)

String functions#

Function

Notes

CHAR_LENGTH(string)

CHARACTER_LENGTH(string)

Synonym for CHAR_LENGTH.

HEX(string | numeric)

Telemetry only. Returns the hexadecimal representation. Numeric arguments are cast to a 64-bit integer first, so a value that doesn’t fit returns NULL.

INITCAP(string)

LOWER(string)

MAP_KEYS(map)

Returns the keys of a map column as an array of strings.

OVERLAY(string PLACING string FROM int [FOR int])

POSITION(substring IN string)

REGEXP_EXTRACT(string, pattern [, group])

Telemetry only. Returns the first match of the regular expression pattern in string, or its capture group group. With no group, returns capture group 1 if the pattern has one. Returns an empty string when there is no match.

REGEXP_LIKE(string, pattern)

Telemetry only. Returns whether the regular expression pattern matches anywhere in string.

SUBSTRING(string FROM int [FOR int])

TRIM([LEADING | TRAILING | BOTH] [chars FROM] string)

UPPER(string)

Date and time functions#

Function

Notes

CURRENT_DATE

CURRENT_TIME

Metadata only. Not available over telemetry tables.

CURRENT_TIMESTAMP

EXTRACT(field FROM datetime)

DATE_BIN(stride, source, origin)

Telemetry only. Truncates source down to a multiple of stride measured from origin. Returns the type of source.

DATE_TRUNC(unit, datetime)

Truncates datetime down to unit. unit must be a string literal: millisecond, microsecond, second, minute, hour, day, week, month, quarter, or year.

TIMESTAMPDIFF(unit, start, end)

Returns the whole number of unit intervals from start to end as BIGINT. unit is a keyword, such as TIMESTAMPDIFF(MINUTE, start_time, end_time): NANOSECOND, MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, or WEEK.

DATE_BIN takes the interval first, which is easy to get backwards:

SELECT DATE_BIN(INTERVAL '1' MINUTE, ts, TIMESTAMP '1970-01-01 00:00:00') AS bucket,
       AVG(value) AS mean_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
GROUP BY bucket
ORDER BY bucket

Use TIMESTAMPDIFF for the time between two timestamps. Subtracting one timestamp from another is rejected.

Conditional functions#

Function

Notes

NULLIF(expr, expr)

COALESCE(expr [, expr]...)

Returns the first non-null argument.

GREATEST(expr [, expr]...)

Returns the largest argument, ignoring nulls.

LEAST(expr [, expr]...)

Returns the smallest argument, ignoring nulls.

Window functions#

These are called with an OVER clause, and every one of them requires an ORDER BY inside it.

Function

Notes

LAG(value [, offset [, default]])

Value from a preceding row in the window.

LEAD(value [, offset [, default]])

Value from a following row in the window.

FIRST_VALUE(value)

Value from the first row in the window.

LAST_VALUE(value)

Value from the last row in the window.

ROW_NUMBER()

Sequential number within the window.

DERIVATIVE(numeric)

Telemetry only. Rate of change of the value per second.

INTEGRAL(numeric [, method])

Telemetry only. Accumulated area under the value over time. method is an unquoted keyword: TRAPEZOID (default), L_RIEMANN, or R_RIEMANN.

DERIVATIVE and INTEGRAL compute over the ordered window rather than a single row. Their ORDER BY must be a single timestamp column:

SELECT ts,
       DERIVATIVE(value) OVER (ORDER BY ts) AS rate
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
ORDER BY ts

INTEGRAL defaults to trapezoidal integration. Pass a method keyword to select a different rule:

SELECT ts,
       INTEGRAL(value) OVER (ORDER BY ts) AS area_trapezoid,
       INTEGRAL(value, L_RIEMANN) OVER (ORDER BY ts) AS area_left,
       INTEGRAL(value, R_RIEMANN) OVER (ORDER BY ts) AS area_right
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
ORDER BY ts

Bitwise functions#

All bitwise functions are telemetry only, take integer-domain operands, and return BIGINT.

Function

Notes

BIT_AND(value, operand)

BIT_OR(value, operand)

BIT_XOR(value, operand)

BIT_SHIFT_LEFT(value, positions)

positions is from 0 to 63.

BIT_SHIFT_RIGHT(value, positions)

positions is from 0 to 63.

BIT_TEST(value, index)

Tests the bit at index, from 0 to 63.

These are two-operand scalar functions, not aggregates. That differs from the SQL standard, where BIT_AND and BIT_OR aggregate over a group. To combine bits across rows, aggregate the result yourself.

Operators#

Standard SQL operators and clauses are unaffected by the function allowlist: comparison and arithmetic operators, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL, CASE, and CAST are all available.

On the telemetry tables, ~ and !~ test a string against a regular expression. Write casts as CAST(expr AS type), since Postgres-style :: casts are rejected.

Next steps#