Expressions - Kinetica Docs

Kinetica has native API support for a variety of expressions, which are used as inputs while querying data (for supported SQL expressions, see Queries (SQL) ). These native API expressions can involve one or more constants (both numeric and string) and table columns. The expressions follow certain constraints based on where they are used, but all the expressions should follow the basic guidelines outlined below.

Use parentheses liberally to ensure correct order-of-operations.

Constants

Type	Details
String	String constants must be enclosed in single quotes or double quotes, e.g., * ‘hello’ * “world”
Numerical	Numerical constants can be expressed as: * decimal integers (`31242`) * hex integers (`0x3420123a`) * doubles (`3.41e5`) * floats (`3.1415F`)
Date/Time	Supported date formats have optional leading zeros for months & days, while datetime optionally supports UTC format. Examples of each date/time data type: * date (`2000-1-2`, `2020-01-02`) * time (`1:23:45`, `17:17:17`) * datetime (`2000-1-2 1:23:45.678`, `2020-01-02T17:17:17Z`)

Operators

When these operators are applied to numeric columns, they will interpret non-zero values as true and zero values as false, returning 1 for true and 0 for false.

Type	Details
Bitwise	`&` `\|` `<<` `>>` `~` `^`
Comparison	`>` `<` `>=` `<=` `==` `=` `!=` `<>` `in`
Logical	`and`: Evaluates to true only if both sides of the expression are true; see Short-Circuiting for error-checking details `or`: Evaluates to true if either side of the expression is true; see Short-Circuiting for error-checking details `xor`: Evaluates to true only if one side of the expression is true and the other false `not`: Evaluates to true if the expression is false, and vice versa `!`: Synonym for `not`
Mathematical	`+` `-` `*` `/`

Functions

Array Functions

Scalar Functions

ARRAY_APPEND(array, value)

Returns an array consisting of array with value appended to the end.

ARRAY_CONCAT(arr1, arr2)

Returns an array consisting of all the elements of array arr1 followed by all of the elements of array arr2.

ARRAY_CONTAINS(array, value)

Returns true if value is present in array.

ARRAY_CONTAINS_ALL(arr1, arr2)

Returns true if every value in array arr2 is present in array arr1; otherwise returns false.

ARRAY_CONTAINS_ANY(arr1, arr2)

Returns true if any value in array arr2 is present in array arr; otherwise returns false.

ARRAY_DISTINCT(array)

Returns an array consisting of array values with duplicates removed.

ARRAY_EMPTY(array)

Returns true if array is empty; otherwise returns false.

ARRAY_EXCEPT(arr1, arr2)

Returns an array consisting of all of the elements of array arr1 with the ones that also appear in array arr2 removed.

ARRAY_INTERSECT(arr1, arr2)

Returns an array consisting of all of the elements of array arr1 that also appear in array arr2.

ARRAY_ITEM(array, pos)

Returns the item in array at the given 1-based pos position.See examples.

ARRAY_LENGTH(array)

Returns the number of items in the given array at the 1st dimension.See examples.

ARRAY_LOWER(array, dim)

Returns the lowest index of array in the given dim dimension; since all arrays are 1-dimensional, only specifying a dimension of 1 will return a value, and since array indices are 1-based, that value will always be 1.

ARRAY_NDIMS(array)

Returns the number of dimensions in the given array; since all arrays are 1-dimensional, that number will always be 1.

ARRAY_NOT_EMPTY(array)

Returns true if array is not empty; otherwise returns false.

ARRAY_SLICE(array, from, to)

Returns an array consisting of the values from array between the from index (inclusive) up to the to index (exclusive) using 0-based indexing. Negative indexes are treated as offsets from the end of the array.

ARRAY_TO_STRING(array, delim)

Converts the items of array into a string delimited by delim.See examples.

ARRAY_UPPER(array, dim)

Returns the highest index of array in the given dim dimension; since all arrays are 1-dimensional, only specifying a dimension of 1 will return a value, and since array indices are 1-based, that value will always be equivalent to ARRAY_LENGTH.

MAKE_ARRAY(value)

Returns an array consisting of the single value element.

STRING_TO_ARRAY(str, delim)

Converts the values in str, delimited by delim, into an array.See examples.

Aggregation/Transposition Functions

The following functions can be used on array columns within aggregations. These functions can be used to convert primitive column values into arrays of those values (aggregation) or to convert array columns into columns of primitive values (unnest).

ARRAY_AGG(expr)

Combines column values within a group into a single array of those values.See examples.

ARRAY_AGG_DISTINCT(expr)

Combines column values within a group into a single array of those values, removing any duplicates.See examples.

UNNEST_JSON_ARRAY

Transposes array values into columnar data.See UNNEST_JSON_ARRAY.

Conditional Functions

CASE(expr, {<matches>}, {<values>}, value_if_no_match)

Evaluates expr: returns the first value from the values list whose corresponding match from the matches list is equal to expr; returns value_if_no_match if expr is not equal to any of the matches in the matches list

A null cannot be used for either a match or value, but value_if_no_match can be null.

Parameter	Description
`expr`	any expression to match against the list of `matches`
`matches`	a comma-delimited list of constants to match `expr` against; needs to be of the same data type as `expr` and have the same number of list items as the `values` list
`values`	a comma-delimited list of constants, one of which will be returned in the event that `expr` matches an item in the `matches` list; needs to be of the same data type as `value_if_no_match` and have the same number of list items as the `matches` list
`value_if_no match`	any value to return in the event of a match; needs to be of the same data type as the list items in `values`

Examples:

Function Call	Result
`CASE(0, {1, 2, 3}, {‘A’, ‘B’, ‘C’}, ‘D’)`	`D`
`CASE(1, {1, 2, 3}, {‘A’, ‘B’, ‘C’}, ‘D’)`	`A`
`CASE(2, {1, 2, 3}, {‘A’, ‘B’, ‘C’}, ‘D’)`	`B`
`CASE(3, {1, 2, 3}, {‘A’, ‘B’, ‘C’}, ‘D’)`	`C`
`CASE(4, {1, 2, 3}, {‘A’, ‘B’, ‘C’}, ‘D’)`	`D`

IF(expr, value_if_true, value_if_false)

Evaluates expr: if true, returns value_if_true; otherwise, if false or null, returns value_if_false; see Short-Circuiting for error-checking details

Parameter	Description
`expr`	any true/false condition When an integer column is used directly, this function will interpret non-zero values as true and zero values as false.
`value_if_true`	any type; must be the same type as `value_if_false`
`value_if_false`	any type; must be the same type as `value_if_true`

Conversion Functions

For the CAST() and CONVERT() functions, valid destination types are:

Numeric	Text	Date/Time	Geometry
* bool * int8 * int16 * int * long * ulong * float * double * decimal	* string * char1 * char2 * char4 * char8 * char16 * char32 * char64 * char128 * char256	* timestamp * date * time * datetime	* geometry

CAST (original value, destination type)

Returns the equivalent of original value converted to the destination type; useful for converting strings to numbers and numbers to strings

CHAR(expr)

Returns the character associated with the ASCII code in expr

CHAR1(expr)

Converts the given expr to char1 type

CHAR2(expr)

Converts the given expr to char2 type

CHAR4(expr)

Converts the given expr to char4 type

CHAR8(expr)

Converts the given expr to char8 type

CHAR16(expr)

Converts the given expr to char16 type

CHAR32(expr)

Converts the given expr to char32 type

CHAR64(expr)

Converts the given expr to char64 type

CHAR128(expr)

Converts the given expr to char128 type

CHAR256(expr)

Converts the given expr to char256 type

CONVERT (original value, destination type, style)

Returns the equivalent of original value converted to the destination type. The style parameter is currently only applicable when the destination type is string and the original value is of timestamp or datetime type.Valid style codes include:

Style	Format
`0`	`mon dd yyyy hh:mi<AM\|PM>`
`21` `121`	`yyyy-mm-dd hh:mi:ss.mmm`

Examples:

Function Call	Result
`CONVERT(‘0.1’, double)`	`0.1`
`CONVERT(DATETIME(‘2000-10-10 12:34:56’), string, 0)`	`Oct 10 2000 12:34PM`
`CONVERT(DATETIME(971181296789), string, 21)`	`2000-10-10 12:34:56.789`

DATE(expr)

Converts expr to date (YYYY-MM-DD) format

DATETIME(expr)

Converts expr to datetime (YYYY-MM-DD HH24:MI:SS.mmm) format

DECIMAL(expr)

Converts the given expr to decimal type

DOUBLE(expr)

Converts the given expr to double type

FLOAT(expr)

Converts the given expr to float type

INT(expr)

Converts the given expr to int type

LONG(expr)

Converts the given expr to long type

STRING(expr)

Converts expr to a string format appropriate for the expr type

TIME(expr)

Converts expr to time (HH:MI:SS.mmm) format

TIMESTAMP(expr)

Converts expr to the number of milliseconds since the epoch

TO_CHAR(expr, format)

Converts the given date/time expr to a string matching the given format. The returned string will be truncated at 32 characters. See Date/Time Conversion Codes for the list of format codes.Example:

Function Call	`TO_CHAR(DATETIME(971181296000), ‘“Last login: “YYYY-MM-DD’)`
Return	`Last login: 2000-10-10`

TO_DATE(string, format)

Converts the given string to a date type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_DATE('2017-06-15', 'YYYY-MM-DD')

TO_DATETIME(string, format)

Converts the given string to a datetime type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_DATETIME('2017-06-15 10:37:30', 'YYYY-MM-DD HH:MI:SS')

TO_TIME(string, format)

Converts the given string to a time type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_TIME('10:37:30', 'HH:MI:SS')

TO_TIMESTAMP(string, format)

Converts the given string to a timestamp type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_TIMESTAMP('2017-06-15 10:37:30', 'YYYY-MM-DD HH:MI:SS')

ULONG(expr)

Converts the given expr to ulong type

VECTOR(expr, len)

Converts the given stringified array of len number of float values in expr to VECTOR type.Example:

VECTOR('[1.1, 2.2, 3.3]', 3)

Date/Time Conversion Codes

The following formatting codes can be used to convert date/time strings to native date/time objects (with TO_DATE, TO_DATETIME, TO_TIME and TO_TIMESTAMP) and for formatting date/time objects as strings (with TO_CHAR). These characters are interpreted literally: - / , . ; : Other characters need to be quoted in order to be interpreted literally. For example, MM-DD-YYYY can be used to convert 01-02-2022 to a date, while "Today is "MM-DD-YYYY is needed to convert Today is 01-02-2022. In the following table, Opt in the Input column indicates that the field may be empty when being read, and still be considered a valid input. This can be useful when reading data from a source that, for example, sometimes includes milliseconds in their time strings and sometimes does not (e.g., the MMM code is Opt). Yes in the Input column indicates that some data must be present in the input string to be considered valid, unless it is after a $ character, in which case all input after the $ is optional.

Format Code	Input	Output	Description
`_`	Yes		Any one character
`?`	Opt		Zero or more non-digit characters
`$`	Yes		Everything after this is optional; e.g., `HH:MI$:SS` would accept `12:34` and `12:34:56`
`AD`		Yes	Era indicator without periods
`A.D.`		Yes	Era indicator with periods
`AM`	Yes	Yes	Meridian indicator without periods [`AM`, `PM`]
`A.M.`	Yes	Yes	Meridian indicator with periods [`A.M.`, `P.M.`]
`BC`		Yes	Era indicator without periods
`B.C.`		Yes	Era indicator with periods
`C`	Yes	Yes	Number of day in calendar month, with optional leading zero [`1` - `31`]
`CC`		Yes	Century (if last 2 digits of the 4-digit year are `00`, this is the first 2 digits; otherwise, this is first 2 digits + 1)
`D`	Yes	Yes	Day of week [`1` - `7`] (Sunday - Saturday)
`DAY`	Yes	Yes	Day of week [`Sunday` - `Saturday`]
`DD`	Yes	Yes	Number of day in month, with leading zero [`01` - `31`]
`DDD`	Yes	Yes	Number of day of year [`1` - `366`]
`DL`	Yes	Yes	Date long format (`DAY, MONTH C, RRRR`)
`DS`	Yes	Yes	Date short format (`M/C/RRRR`)
`DY`	Yes	Yes	Abbreviated day name (e.g., “Mon”)
`FF[1-9]`	Opt	Yes	Fractional seconds where the number of fractional second digits can be specified (default is 3 digits); when used for input, the string may contain an optional ”.” preceding the numbers
`H`	Yes	Yes	Alias for `H12`
`H12`	Yes	Yes	Hour of day in 12-hour format, with optional leading zero [`0` - `11`]
`H24`	Yes	Yes	Hour of day in 24-hour format, with optional leading zero [`0` - `23`]
`HH`	Yes	Yes	Alias for `HH12`
`HH12`	Yes	Yes	Hour of day in 12-hour format, with leading zero [`00` - `11`]
`HH24`	Yes	Yes	Hour of day in 24-hour format, with leading zero [`00` - `23`]
`I`	Yes	Yes	Last digit of ISO year
`ID`	Yes	Yes	ISO 8601 day of week [`1` - `7`] (Monday - Sunday)
`IDDD`	Yes	Yes	ISO 8601 day of year [`001` - `371`] (where `001` is the Monday of the 1st ISO week)
`IW`	Yes	Yes	ISO 8601 week of year [`01` - `53`] (where `01` contains the first Thursday of the year)
`IY`	Yes	Yes	Last 2 digits of ISO year
`IYY`	Yes	Yes	Last 3 digits of ISO year
`IYYY`	Yes	Yes	Last 4 digits of ISO year
`J`	Yes	Yes	Julian day; the number of days since January 1, 4712 BC. Number specified with `J` must be integers.
`LLL`	Opt	Yes	Whole milliseconds (often positioned after a colon); e.g., `12:34:56:78` => 78 milliseconds; when used for input, the string may contain an optional ”.” or ”:” preceding the numbers; when ”.” is found, this code will act like the `MMM` code
`M`	Yes	Yes	Number of month in year, with optional leading zero [`1` - `12`]
`MI`	Yes	Yes	Minute of hour [`00` - `59`]
`MM`	Yes	Yes	Number of month in year, with leading zero [`01` - `12`]
`MMM`	Opt	Yes	Fractions of a second up to milliseconds; e.g., `12:34:56.78` => 780 milliseconds; when used for input, the string may contain an optional ”.” or ”:” preceding the numbers; when ”:” is found, this code will act like the `LLL` code
`MON`	Yes	Yes	3-character abbreviation of month in title case [`Jan` - `Dec`]
`MONTH`	Yes	Yes	Full name of month [`January` - `December`]
`MS`	Opt	Yes	Alias for `MMM`
`OF`	Opt	Yes	Time-zone offset from UTC (`TZHTZM`)
`PM`	Yes	Yes	Alias for `AM`
`P.M.`	Yes	Yes	Alias for `A.M.`
`Q`		Yes	Quarter of the year [`1` - `4`] (January-March - October-December)
`RM`	Yes	Yes	Roman numeral month [`I` - `XII`] (January - December)
`RR`	Yes	Yes	Rounded year. Assume the given 2-digit year occurs in the present century, then: * If the assumed date is greater than 50 years into the future, declare the date to be in the previous century * If the assumed date is greater than 50 years into the past, declare the date to be in the next century
`RRRR`	Yes	Yes	Either a 2-digit or 4-digit year; if 2-digit, equivalent to `RR`
`SCC`		Yes	Same as `CC`
`SS`	Yes	Yes	Second of minute [`00` - `59`]
`SSSS`	Yes	Yes	Alias for `SSSSS`
`SSSSS`	Yes	Yes	Seconds past midnight [`0` - `86399`]
`SYEAR`		Yes	Same as `YEAR`
`SYYYY`	Yes	Yes	Same as `YYYY`
`TS`	Yes	Yes	Time short format (`H24:MI:SS.MMM`)
`TZ`	Opt	Yes	Time zone (`TZH`)
`TZH`	Opt	Yes	Time zone hour (ISO); “Z” or +/- followed by one or two digits (will always output “Z”)
`TZM`	Opt	Yes	Time zone minute (ISO); colon followed by one or two digits; for input, an optional colon is allowed at the front; if present, two digits must follow it
`US`	Opt	Yes	Microseconds; when used for input, the string may contain an optional ”.” preceding the numbers
`W`	Yes	Yes	Week of month [`1` - `5`], where week 1 starts on the first day of the month and ends on the seventh
`WW`	Yes	Yes	Week of year [`1` - `53`], where week 1 starts on the first day of the year and continues to the seventh day of the year
`X`	Yes	Yes	Local radix character (always `.`. E.g., `HH:MI:SSXFF`)
`Y`	Yes	Yes	Last 1 digit of year
`Y,YYY`	Yes	Yes	Year with a comma after the thousands place
`YEAR`		Yes	Year spelled out (e.g., “Two Thousand Twenty Five”)
`YY`	Yes	Yes	Last 2 digits of year
`YYY`	Yes	Yes	Last 3 digits of year
`YYYY`	Yes	Yes	4-digit year

Date/Time Functions

This section comprises the following functions:

Date/Time Base Functions, which can extract parts of date/time expressions, convert back and forth between data types, and return the current date/time
Date/Time Complex Conversion Functions, which can perform more complex date/type conversions

Date/Time Base Functions

CLOCK_TIMESTAMP()

Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm

CLOCK_TIMESTAMP may return different values each time it is called in the same query or SQL Procedure, which may lead to data getting out of sync across HA clusters. Use CURRENT_DATETIME to avoid this issue.

CURRENT_DATE()

Returns the date as YYYY-MM-DD

CURRENT_DATETIME()

Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm

CURRENT_DATETIME will return same values each time it is called in the same query or SQL Procedure, and should keep data in-sync across HA clusters. See CLOCK_TIMESTAMP to always get the actual time each time it is called.

CURRENT_TIME()

Returns the time as HH24:MI:SS.mmm

CURRENT_TIMESTAMP()

Returns the date & time as the number of milliseconds since the epoch

DATEADD (unit, amount, expr)

Adds the positive or negative integral amount of unit date/time intervals to the date or datetime value in exprThe following date/time intervals are supported for unit:

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.) overflow/underflow occurs
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g., Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hours are modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minutes are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Seconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`MILLISECOND`	Milliseconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs

Any of these unit types can have a SQL_TSI_ prefix prepended to them; e.g., both DAY and SQL_TSI_DAY are valid unit types for specifying a day interval. They may also be single-quoted or unquoted.

Examples:

Function Call	Result (in string format)
`DATEADD(YEAR, 1, ‘2000-10-10’)`	`2001-10-10 00:00:00.000`
`DATEADD(QUARTER, 1, ‘2000-11-30’)`	`2001-02-28 00:00:00.000`
`DATEADD(MONTH, 1, ‘2000-01-31’)`	`2000-02-29 00:00:00.000`
`DATEADD(WEEK, 53, ‘2000-01-01’)`	`2001-01-06 00:00:00.000`
`DATEADD(DAY, 1, ‘2000-12-31’)`	`2001-01-01 00:00:00.000`
`DATEADD(HOUR, 12, ‘2000-10-10 12:34:56’)`	`2000-10-11 00:34:56.000`
`DATEADD(MINUTE, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:35:56.000`
`DATEADD(SECOND, 1, ‘2000-12-31 23:59:59’)`	`2001-01-01 00:00:00.000`
`DATEADD(MILLISECOND, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:34:56.001`

DATE_BUCKET (width, ds[, offset[, base]])

Calculates the date range in which a given date ds falls, based on a set of fixed-width “buckets” with the given width, start-aligned base date, and offset from that base dateThe width is the number of days each bucket should span.The offset is the number of days after (positive offset) or number of days before (negative offset) the base date to which the buckets should be aligned. The default is no offset.The default base is 2000-01-03.For example, given the following call:

DATE_BUCKET(7, ds, -3, '2023-02-21')

The call will be processed as follows:

The given date ds will be slotted into a date bucket
The bucket will span a range of 7 days
The baseline bucket will start at 2023-02-18 (2023-02-21 offset by -3 days) and continue through 2023-02-24 (7 days, including 2023-02-18)
Buckets will extend before & after the baseline bucket in contiguous, non-overlapping fashion
The result record will show the date at the beginning of the bucket’s date range

DATEDIFF ([unit,] begin, end)

Function Call	Result
`DATEDIFF(‘2000-10-10’, ‘2000-12-31’)`	`82`
`DATEDIFF(‘2000-03-31’, ‘2000-04-30’)`	`30`
`DATEDIFF(‘2000-12-31’, ‘2000-10-10’)`	`-82`
`DATEDIFF(‘2000-10-10 12:34:56.789’, 978222896678)`	`81`
`DATEDIFF(MONTH, ‘2000-10-10’, ‘2000-12-31’)`	`2`
`DATEDIFF(MONTH, ‘2000-03-31’, ‘2000-04-30’)`	`0`
`DATEDIFF(MONTH, ‘2000-12-31’, ‘2000-10-10’)`	`-2`
`DATEDIFF(HOUR, ‘2000-10-10 12:34:56.789’, 978222896678)`	`1955`

DATEPART(part, expr)

Returns the requested part of the date/time expr. The part may be any of those specified in DATE_TRUNC(part, expr), plus the following:

Constant	Description
`SECS_SINCE_EPOCH`	Return the number of seconds since the epoch (1970-01-01).
`MSECS_SINCE_EPOCH`	Return the number of milliseconds since the epoch.

Examples:

Function Call	Result
`DATEPART(YEAR, ‘2000-12-31’)`	`2000`
`DATEPART(MONTH, ‘2000-03-31 12:34:56.000’)`	`3`
`DATEPART(DAY, ‘2000-03-31 12:34:56.000’)`	`31`
`DATEPART(SECOND, ‘2000-03-31 12:34:56.220’)`	`56`
`DATEPART(MSECS_SINCE_EPOCH, ‘2000-03-31 12:34:56.220’)`	`954506096220`

DATE_TRUNC(part, expr)

Returns the date/time expr after truncating it beyond the given date/time part. The following date/time constants are supported for part:

Constant	Description
`YEAR`	Return the first day of the year in which `expr` occurs
`QUARTER`	Return the first day of the quarter in which `expr` occurs
`MONTH`	Return the first day of the month in which `expr` occurs
`WEEK`	Return the first day of the week in which `expr` occurs
`DAY`	Return the date (at midnight) on which `expr` occurs
`HOUR`	Return the timestamp up to the hour in which `expr` occurs
`MINUTE`	Return the timestamp up to the minute in which `expr` occurs
`SECOND`	Return the timestamp up to the second in which `expr` occurs
`MILLISECOND`	Return the timestamp up to the millisecond in which `expr` occurs

Examples:

Function Call	Result
`DATE_TRUNC(YEAR, ‘2008-09-10 12:34:56.789’)`	`2008-01-01 00:00:00.000`
`DATE_TRUNC(QUARTER, ‘2008-09-10 12:34:56.789’)`	`2008-07-01 00:00:00.000`
`DATE_TRUNC(MONTH, ‘2008-09-10 12:34:56.789’)`	`2008-09-01 00:00:00.000`
`DATE_TRUNC(WEEK, ‘2008-09-10 12:34:56.789’)`	`2008-09-07 00:00:00.000`
`DATE_TRUNC(DAY, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 00:00:00.000`
`DATE_TRUNC(HOUR, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:00:00.000`
`DATE_TRUNC(MINUTE, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:34:00.000`
`DATE_TRUNC(SECOND, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:34:56.000`
`DATE_TRUNC(MILLISECOND, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:34:56.789`

DAY(expr)

Alias for DAYOFMONTH(expr)

DAYNAME(expr)

Extracts the day of the week from expr and converts it to the corresponding day name [Sunday - Saturday ]

DAYOFMONTH(expr)

Extracts the day of the month from expr [1 - 31]

DAYOFWEEK(expr)

Extracts the day of the week from expr [1 - 7]

Expression Value	Result
Date on Sunday	`1`
Date on Monday	`2`
Date on Tuesday	`3`
Date on Wednesday	`4`
Date on Thursday	`5`
Date on Friday	`6`
Date on Saturday	`7`

DAY_OF_WEEK(expr)

Alias for DAYOFWEEK(expr)

DAYOFYEAR(expr)

Extracts the day of the year from expr [1 - 366]

DAY_OF_YEAR(expr)

Alias for DAYOFYEAR(expr)

EPOCH_MSECS_TO_DATETIME(expr)

Converts expr milliseconds since the epoch to a date/timeExample:

Function Call	`EPOCH_MSECS_TO_DATETIME(971181296789)`
Return	`2000-10-10 12:34:56.789`

EPOCH_SECS_TO_DATETIME(expr)

Converts expr seconds since the epoch to a date/timeExample:

Function Call	`EPOCH_SECS_TO_DATETIME(971181296)`
Return	`2000-10-10 12:34:56.000`

HOUR(expr)

Extracts the hour of the day from expr [0 - 23]

LAST_DAY(expr)

Returns the date of the last day of the month in the given expr

MINUTE(expr)

Extracts the minute of the day from expr [0 - 59]

MONTH(expr)

Extracts the month of the year from expr [1 - 12]

MONTHNAME(expr)

Extracts the month of the year from expr and converts it to the corresponding month name [ January - December]

MSEC(expr)

Extracts the millisecond of the second from expr [0 - 999]

MSECS_SINCE_EPOCH(expr)

Converts expr date/time to the number of milliseconds since the epochExample:

Function Call	`MSECS_SINCE_EPOCH(‘2000-10-10 12:34:56.789’)`
Return	`971181296789`

NEXT_DAY(date, expr)

Returns the date of the next day of the week, provided as a day name in expr, that occurs after the given dateSome examples, given that 2000-10-10 is a Tuesday:

Function Call	Result
`NEXT_DAY(‘2000-10-10’, ‘Wednesday’)`	`2000-10-11`
`NEXT_DAY(‘2000-10-10’, ‘Friday’)`	`2000-10-13`
`NEXT_DAY(‘2000-10-10’, ‘Tuesday’)`	`2000-10-17`

NOW()

Alias for CURRENT_DATETIME()

QUARTER(expr)

Extracts the quarter of the year from expr [1 - 4]

Expression Value	Result
Date in January, February, or March	`1`
Date in April, May, or June	`2`
Date in July, August, or September	`3`
Date in October, November, or December	`4`

SEC(expr)

Alias for SECOND(expr)

SECOND(expr)

Extracts the seconds of the minute from expr [ 0 - 59 ]

SECS_SINCE_EPOCH(expr)

Converts expr date/time to the number of seconds since the epochExample:

Function Call	`SECS_SINCE_EPOCH(‘2000-10-10 12:34:56’)`
Return	`971181296`

SLEEP(expr)

Pause execution for at least expr seconds, though system load may delay the return from this call for longer than the specified amount. Use a decimal for expr to pause for less than a second; e.g., SLEEP(0.001) will pause for at least 1 millisecond. SLEEP should be invoked without a table reference in the call to avoid being called for every record in a result set; e.g., in Python:

db.get_records_by_column('', 'SLEEP(0.001)')

TIMEBOUNDARYDIFF (unit, begin, end)

Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified; more precisely, how many whole date/time intervals of type unit need to be added to (or subtracted from) begin to equal end up to the precision of the unit specified, using the unit types and rules specified in TIMESTAMPADD.For example, if unit were MONTH, only the year & month of begin and end would be used in the calculation; if unit were DAY, any time portion from begin & end would be dropped, and so on. This is unlike TIMESTAMPDIFF, which will consider the entirety of both begin & end in the calculation.

This is symmetric with TIMESTAMPADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, and the TIMEBOUNDARYDIFF in MONTH units between those two dates is 1.

Examples:

Function Call	Result
`TIMEBOUNDARYDIFF(MONTH, DATE(‘2000-10-10’), DATE(‘2000-12-31’))`	`2`
`TIMEBOUNDARYDIFF(MONTH, DATE(‘2000-03-31’), DATE(‘2000-04-30’))`	`1`
`TIMEBOUNDARYDIFF(MONTH, DATE(‘2000-12-31’), DATE(‘2000-10-10’))`	`-2`
`TIMEBOUNDARYDIFF(HOUR, 978222896000, DATETIME(‘2000-10-10 12:34:56’))`	`-1956`

This function does not work with string literal date stamps (e.g., 2000-12-31 12:34:56); to use string literals in this function, first cast them to the appropriate date/time type (e.g., DATETIME('YYYY-MM-DD HH24:MI:SS'))

TIME_BUCKET (width, ts[, offset[, base]])

Calculates the date/time range in which a given timestamp ts falls, based on a set of fixed-width “buckets” with the given width, start-aligned base date/time, and offset from that base date/timeThe width is the number of milliseconds each bucket should span.The offset is the number of milliseconds after (positive offset) or number of milliseconds before (negative offset) the base date/time to which the buckets should be aligned. The default is no offset.The default base is 2000-01-03 00:00:00.For example, given the following call:

TIME_BUCKET(5 * 60 * 1000, ts, -2.5 * 60 * 1000, '2023-02-28')

The call will be processed as follows:

The given timestamp ts will be slotted into a date/time bucket
The bucket will span a range of 5 minutes
The baseline bucket will start at 2023-02-27 23:57:30 (2023-02-28 offset by -2.5 minutes) and continue through 2023-02-28 00:02:30 (5 minutes from 2023-02-27 23:57:30)
Buckets will extend before & after the baseline bucket in contiguous, non-overlapping fashion
The result record will show the timestamp at the beginning of the bucket’s range

TIMESTAMPADD (unit, amount, expr)

Adds the positive or negative integral amount of unit date/time intervals to the date, datetime, or time value in exprThe following date/time intervals are supported for unit:

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.) overflow/underflow occurs
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g., Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hours are modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minutes are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Seconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`MILLISECOND`	Milliseconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs

Examples:

Function Call	Result (in string format)
`TIMESTAMPADD(YEAR, 1, ‘2000-10-10’)`	`2001-10-10`
`TIMESTAMPADD(QUARTER, 1, ‘2000-11-30’)`	`2001-02-28`
`TIMESTAMPADD(MONTH, 1, ‘2000-01-31’)`	`2000-02-29`
`TIMESTAMPADD(WEEK, 53, ‘2000-01-01’)`	`2001-01-06`
`TIMESTAMPADD(DAY, 1, ‘2000-12-31’)`	`2001-01-01`
`TIMESTAMPADD(HOUR, 12, ‘2000-10-10 12:34:56’)`	`2000-10-11 00:34:56.000`
`TIMESTAMPADD(MINUTE, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:35:56.000`
`TIMESTAMPADD(SECOND, 1, ‘2000-12-31 23:59:59’)`	`2001-01-01 00:00:00.000`
`TIMESTAMPADD(MILLISECOND, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:34:56.001`
`TIMESTAMPADD(SECOND, 1, TIME(‘12:34:56’))`	`12:34:57.000`

TIMESTAMPDIFF (unit, begin, end)

Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified; more precisely, how many whole date/time intervals of type unit need to be added to (or subtracted from) begin to equal end (or get as close as possible without going past it) using the unit types and and rules specified in TIMESTAMPADD.Unlike TIMEBOUNDARYDIFF, all date/time components of both begin & end will be considered in the calculation, not just those that are up to the precision of unit.

This is not symmetric with TIMESTAMPADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, but the TIMESTAMPDIFF in MONTH units between those two dates is 0.

Examples:

Function Call	Result
`TIMESTAMPDIFF(MONTH, DATETIME(‘2000-10-10 01:23:45.678’), DATETIME(‘2000-12-31 12:34:56.789’))`	`2`
`TIMESTAMPDIFF(MONTH, DATETIME(‘2000-03-31 01:23:45.678’), DATETIME(‘2000-04-30 12:34:56.789’))`	`0`
`TIMESTAMPDIFF(MONTH, DATETIME(‘2000-12-31 01:23:45.678’), DATETIME(‘2000-10-10 12:34:56.789’))`	`-2`
`TIMESTAMPDIFF(HOUR, DATETIME(‘2000-10-10 12:34:56.789’), TIMESTAMP(978222896678))`	`1955`

TIMESTAMP_TRUNC(part, expr)

Alias for DATE_TRUNC(part, expr)

UNIX_TIMESTAMP(expr)

Alias for SECS_SINCE_EPOCH(expr)

WEEK(expr)

Extracts the week of the year from expr [1 - 54]; each full week starts on Sunday (A 1 is returned for the week containing Jan 1st)

YEAR(expr)

Extracts the year from expr; 4-digit year, A.D.

Date/Time Complex Conversion Functions

DATE_TO_EPOCH_MSECS (year, month, day, hour, min, sec, msec)

Converts the full date to the number of milliseconds since the epochExample:

Function Call	`DATE_TO_EPOCH_MSECS(2017, 06, 15, 09, 22, 15, 42)`
Return	`1497518535042`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

DATE_TO_EPOCH_SECS (year, month, day, hour, min, sec)

Converts the full date to the number of seconds since the epochExample:

Function Call	`DATE_TO_EPOCH_SECS(2017, 06, 15, 09, 22, 15)`
Return	`1494926535`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

TIMESTAMP_FROM_DATE_TIME (date, time)

Converts the given date and time to a composite timestamp in milliseconds since the epochExample:

Function Call	`TIMESTAMP_FROM_DATE_TIME(‘2017-06-15’, ‘10:37:30’)`
Return	`1497523050000`

WEEK_TO_EPOCH_MSECS (year, week_number)

Converts the year and week number to the number of milliseconds since the epoch; negative values are acceptedExample:

Function Call	`WEEK_TO_EPOCH_MSECS(2017,-32)`
Return	`1463270400000`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

WEEK_TO_EPOCH_SECS (year, week_number)

Converts the year and week number to the number of seconds since the epoch. Negative values are accepted. Each new week begins Sunday at midnight.Example:

Function Call	`WEEK_TO_EPOCH_SECS(2017,-32)`
Return	`1463270400`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

Geospatial/Geometry Functions

Five types of geospatial functions are available in Kinetica:

Scalar Functions - apply a geospatial function at the record level to WKT or X/Y data
Enhanced Performance Scalar Functions - apply performance-optimized geospatial functions to X/Y data
Aggregate Functions - apply a geospatial function across groups of records to WKT or X/Y data
Track Functions - apply scalar & aggregate functions to track-based data
H3 Functions - apply H3 gridding functions to WKT or X/Y data

Use ST_ISVALID to determine if a geometry object is valid. The functions below work best with valid geometry objects.
Use the REMOVE_NULLABLE function to remove any nullable column types that could result from calculating a derived column (e.g., as in Projections) using one of the functions below.

Enhanced Performance Scalar Functions

The functions below all compare x and y coordinates to geometry objects (or vice versa), thus increasing their performance in queries. Each of these functions have a geometry-to-geometry version listed in the next section.

STXY_CONTAINS(geom, x, y)

Returns 1 (true) if geom contains the x and y coordinate, e.g. lies in the interior of geom. The coordinate cannot be on the boundary and also be contained because geom does not contain its boundary

STXY_CONTAINSPROPERLY(geom, x, y)

Returns 1 (true) if the x and y coordinate intersects the interior of geom but not the boundary (or exterior) because geom does not contain its boundary but does contain itself

STXY_COVEREDBY(x, y, geom)

Returns 1 (true) if the x and y coordinate is covered by geom

STXY_COVERS(geom, x, y)

Returns 1 (true) if geom covers the x and y coordinate

STXY_DISJOINT(x, y, geom)

Returns 1 (true) if the given x and y coordinate and the geometry geom do not spatially intersect.

STXY_DISTANCE (x, y, geom[, solution])

Calculates the minimum distance between the given x and y coordinate and geom using the specified solution type. Solution types available:

0 (default) - Euclidean; returns 2-D Euclidean distance
1 - Haversine; returns minimum sphere distance in meters
2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

If the x and y coordinate and geom intersect (verify using ST_INTERSECTS), the distance will always be 0.

STXY_DWITHIN (x, y, geom, distance[, solution])

Returns 1 (true) if the x and y coordinate is within the specified distance from geom using the specified solution type. Solution types available:

0 (default) - Euclidean; uses degrees to calculate distance
1 - Sphere; uses meters to calculate sphere distance
2 - Spheroid; uses meters to calculate spheroid distance

STXY_ENVDWITHIN (x, y, geom, distance[, solution])

Returns 1 (true) if the x and y coordinate is within the specified distance from the bounding box of geom using the specified solution type. Solution types available:

0 (default) - Euclidean; uses degrees to calculate distance
1 - Sphere; uses meters to calculate distance

STXY_ENVINTERSECTS(x, y, geom)

Returns 1 (true) if the bounding box of the given geometry geom intersects the x and y coordinate.

STXY_GEOHASH(x, y[, precision])

Returns a hash string representation of the given x and y coordinates with specified precision (the length of the resulting geohash string). The longer the precision, the more precise the hash is. By default, precision is set to 20; the max for precision is 32.See Geohash for an example.

STXY_H3(x, y, resolution)

Alias for H3_XYTOCELL; see H3 Functions.

STXY_INTERSECTION(x, y, geom)

Returns the shared portion between the x and y coordinate and the given geometry geom, i.e. the point itself.

STXY_INTERSECTS(x, y, geom)

Returns 1 (true) if the x and y coordinate and geom intersect in 2-D.

STXY_TOUCHES(x, y, geom)

Returns 1 (true) if the x and y coordinate and geometry geom have at least one point in common but their interiors do not intersect. If geom is a GEOMETRYCOLLECTION, a 0 is returned regardless if the point and geometry touch

STXY_WITHIN(x, y, geom)

Returns 1 (true) if the x and y coordinate is completely inside the geom geometry i.e., not on the boundary

Scalar Functions

DIST(x1, y1, x2, y2)

Computes the Euclidean distance (in degrees), i.e. SQRT( (x1-x2)*(x1-x2) + (y1-y2)*(y1-y2) ).

GEODIST(lon1, lat1, lon2, lat2)

Computes the geographic great-circle distance (in meters) between two lat/lon points.

GEOHASH_DECODE_LATITUDE(geohash)

Decodes a given geohash and returns the latitude value for the given hash string. Supports a maximum geohash character length of 16.

GEOHASH_DECODE_LONGITUDE(geohash)

Decodes a given geohash and returns the longitude value for the given hash string. Supports a maximum geohash character length of 16.

GEOHASH_ENCODE(lat, lon, precision)

Encodes a given coordinate pair and returns a hash string with a given precision. The maximum precision is 15.

GEOMETRY(wkt)

Alias for ST_GEOMFROMTEXT(wkt)

ST_ADDPOINT (linestring, point[, position])

Adds a given point geometry to the given linestring geometry at the specified position, which is a 0-based index. If no position is specified, the point will be added to the end.

ST_ALMOSTEQUALS (geom1, geom2[, decimal])

Returns 1 (true) if given geometries, geom1 and geom2, are almost spatially equal within the given amount of decimal scale. Note that geometries will still be considered equal if the decimal scale for the geometries is within a half order of magnitude of each other; e.g., if decimal is set to 2, then POINT(63.4 123.45) and POINT(63.4 123.454) are equal, but POINT(63.4 123.45) and POINT(63.4 123.459) are not equal. The geometry types must match to be considered equal.If no decimal scale is specified, a default scale of 6 will be applied.

ST_AREA(geom[, solution])

Returns the area of the given geometry geom if it is a POLYGON or MULTIPOLYGON using the specified solution type. Returns 0 if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING. Solution types available:

0 (default) - 2D Euclidean area
1 - curved surface area on a sphere in square meters
2 - curved surface area on a spheroid in square meters

ST_AZIMUTH(geom1, geom2)

Returns the azimuth in radians defined by the segment between two POINTs, geom1 and geom2. Returns a null if the input geometry type is MULTIPOINT, (MULTI)LINESTRING, or (MULTI)POLYGON.

ST_BOUNDARY(geom)

Returns the closure of the combinatorial boundary of a given geometry geom. Returns an empty geometry if geom is an empty geometry. Returns a null if geom is a GEOMETRYCOLLECTION

ST_BOUNDINGDIAGONAL(geom)

Returns the diagonal of the given geometry’s (geom) bounding box.

ST_BUFFER (geom, radius[, style[, solution]])

Returns a geometry that represents all points whose distance from the given geometry geom is less than or equal to the given distance radius. The radius units can be specified by the solution type (default is in degrees) and the radius is created in the provided style. The style options are specified as a list of blank-separated key-value pairs, e.g., 'quad_segs=8 endcap=round'. If an empty style list ('') is provided, the default settings will be used. The style parameter must be specified to provide a solution type.Available style options:

quad_segs - the number of segments used to approximate a quarter circle (default is 8)
endcap - the endcap style of the buffer (default is round); options are round, flat (or butt), and square
join - the join style of the buffer (default is round); options are round, mitre (or miter), and bevel
mitre_limit - the mitre ratio limit expressed as a floating-point number (miter_limit is also acceptable)

Available solution types:

0 (default) - 2D Euclidean radius distance in degrees
1 - curved surface radius distance on a sphere in meters
2 - curved surface radius distance on a spheroid in meters

To create a 5-meter buffer around geom using the default styles: ST_BUFFER(geom, 5, '', 1). To create a 5-foot (converting feet to meters) buffer around geom using the following styles: ST_BUFFER(geom, 5*0.3048,'quad_segs=4 endcap=flat', 1)

ST_BUFFERBYCOMPONENT (geom, radius[, style[, solution]])

Returns a buffered geometry similar to the output of ST_BUFFER using the same parameters. The only difference is the buffered geometry is calculated by independently buffering each individual component and then the buffered components are dissolved (i.e. unioned) together to produce the final output. This can produce very similar (but not identical) results to ST_BUFFER but will often run much faster.

ST_CENTROID(geom)

Calculates the center of the given geometry geom as a POINT. For (MULTI)POINTs, the center is calculated as the average of the input coordinates. For (MULTI)LINESTRINGs, the center is calculated as the weighted length of each given LINESTRING. For (MULTI)POLYGONs, the center is calculated as the weighted area of each given POLYGON. If geom is an empty geometry, an empty GEOMETRYCOLLECTION is returned

ST_CLIP(geom1, geom2)

Returns the geometry shared between given geometries geom1 and geom2

ST_CLOSESTPOINT (geom1, geom2[, solution])

Calculates the 2-D POINT in geom1 that is closest to geom2 using the specified solution type. If geom1 or geom2 is empty, a null is returned. Solution types available:

0 (default) - Euclidean; calculates the closest point using 2-D Euclidean distance
1 - Haversine; calculates the closest point using sphere distance in meters
2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

ST_COLLECT(geom1, geom2)

Returns a MULTI* or GEOMETRYCOLLECTION comprising geom1 and geom2. If geom1 and geom2 are the same, singular geometry type, a MULTI* is returned, e.g., if geom1 and geom2 are both POINTs (empty or no), a MULTIPOINT is returned. If geom1 and geom2 are neither the same type nor singular geometries, a GEOMETRYCOLLECTION is returned.

ST_COLLECTIONEXTRACT (collection, type)

Returns only the specified type from the given geometry collection. Type is a number that maps to the following:

1 = POINT
2 = LINESTRING
3 = POLYGON

ST_COLLECTIONHOMOGENIZE(collection)

Returns the simplest form of the given collection, e.g., a collection with a single POINT will be returned as POINT(x y), and a collection with multiple individual points will be returned as a MULTIPOINT.

ST_CONCAVEHULL (geom, target_percent[, allow_holes])

Returns a potentially concave geometry that encloses all geometries found in the given geom set. Use target_percent (values between 0 and 1) to determine the percent of area of a convex hull the concave hull will attempt to fill; 1 will return the same geometry as an ST_CONVEXHULL operation. Set allow_holes to 1 (true) to allow holes in the resulting geometry; default value is 0 (false). Note that allow_holes is independent of the area of target_percent.

ST_CONTAINS(geom1, geom2)

Returns 1 (true) if no points of geom2 lie in the exterior of geom1 and at least one point of geom2 lies in the interior of geom1. Note that geom1 does not contain its boundary but does contain itself.

ST_CONTAINSPROPERLY(geom1, geom2)

Returns 1 (true) if geom2 intersects the interior of geom1 but not the boundary (or exterior). Note that geom1 does not contain its boundary but does contain itself.

ST_CONVEXHULL(geom)

Returns the minimum convex geometry that encloses all geometries in the given geom set.

ST_COORDDIM(geom)

Returns the coordinate dimension of the given geom, e.g., a geometry with x, y, and z coordinates would return 3.

ST_COVEREDBY(geom1, geom2)

Returns 1 (true) if no point in geom1 is outside geom2.

ST_COVERS(geom1, geom2)

Returns 1 (true) if no point in geom2 is outside geom1.

ST_CROSSES(geom1, geom2)

Returns 1 (true) if the given geometries, geom1 and geom2, spatially cross, meaning some but not all interior points in common. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless of if the two geometries cross

ST_DIFFERENCE(geom1, geom2)

Returns a geometry that represents the part of geom1 that does not intersect with geom2.

ST_DIMENSION(geom)

Returns the dimension of the given geometry geom, which is less than or equal to the coordinate dimension. If geom is a single geometry, a 0 is for POINT, a 1 is for LINESTRING, and a 2 is for POLYGON. If geom is a collection, it will return the largest dimension from the collection. If geom is empty, 0 is returned.

ST_DISJOINT(geom1, geom2)

Returns 1 (true) if the given geometries, geom1 and geom2, do not spatially intersect.

ST_DISTANCE(geom1, geom2[, solution])

Calculates the minimum distance between the given geometries, geom1 and geom2, using the specified solution type. Solution types available:

0 (default) - Euclidean; returns 2-D Euclidean distance
1 - Haversine; returns minimum sphere distance in meters
2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

If geom1 and geom2 intersect (verify using ST_INTERSECTS), the distance will always be 0.

ST_DISTANCEPOINTS (x1, y1, x2, y2[, solution])

Calculates the minimum distance between the given points, x1, y1 and x2, y2, using the specified solution type. Solution types available:

0 (default) - Euclidean; returns 2-D Euclidean distance
1 - Haversine; returns minimum sphere distance in meters
2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

ST_DFULLYWITHIN (geom1, geom2, distance[, solution])

Returns 1 (true) if the maximum distance between geometries geom1 and geom2 is less than or equal to the specified distance of each other using the specified solution type. If geom1 or geom2 is null, 0 (false) is returned. Solution types available:

0 (default) - Euclidean; uses degrees to calculate distance
1 - Sphere; uses meters to calculate distance
2 - Spheroid; uses meters to calculate distance, more accurate than sphere but slower performance

ST_DWITHIN (geom1, geom2, distance[, solution])

Returns 1 (true) if the minimum distance between geometries geom1 and geom2 is within the specified distance of each other using the specified solution type. Solution types available:

0 (default) - Euclidean; uses degrees to calculate distance
1 - Sphere; uses meters to calculate distance
2 - Spheroid; uses meters to calculate distance, more accurate than sphere but slower performance

ST_ELLIPSE(x, y, height, width)

Returns an ellipse using the following values:

x - the x coordinate or longitude used to center the ellipse
y - the y coordinate or latitude used to center the ellipse
height - the height of the ellipse (in degrees)
width - the width of the ellipse (in degrees)

ST_ENDPOINT(geom)

Returns the last point of the given geom as a POINT if it’s a LINESTRING. If geom is not a LINESTRING, null is returned.

ST_ENVDWITHIN (geom1, geom2, distance[, solution])

Returns 1 (true) if geom1 is within the specified distance of the bounding box of geom2 using the specified solution type. Solution types available:

0 (default) - Euclidean; uses degrees to calculate distance
1 - Sphere; uses meters to calculate distance

ST_ENVELOPE(geom)

Returns the bounding box of a given geometry geom.

ST_ENVINTERSECTS(geom1, geom2)

Returns 1 (true) if the bounding box of the given geometries, geom1 and geom2, intersect.

ST_EQUALS(geom1, geom2)

Returns 1 (true) if the given geometries, geom1 and geom2, are spatially equal. Note that order does not matter.

ST_EQUALSEXACT (geom1, geom2[, tolerance])

Returns 1 (true) if the given geometries, geom1 and geom2, are almost spatially equal within some given tolerance. If the values within the given geometries are within the tolerance value of each other, they’re considered equal, e.g., if tolerance is 2, POINT(1 1) and POINT(1 3) are considered equal, but POINT(1 1) and POINT(1 3.1) are not. Note that the geometry types have to match for them to be considered equal. The default tolerance is 0, which makes this function effectively equivalent to ST_EQUALS(geom1, geom2) in the default case.

ST_ERASE(geom1, geom2)

Returns the result of erasing a portion of geom1 equal to the size of geom2.

ST_EXPAND(geom, units)

Returns the bounding box expanded in all directions by the given units of the given geom. The expansion can also be defined for separate directions by providing separate parameters for each direction, e.g., ST_EXPAND(geom, unitsx, unitsy, unitsz, unitsm).

ST_EXPANDBYRATE(geom, rate)

Returns the bounding box expanded by a given rate (a ratio of width and height) for the given geometry geom. The rate must be between 0 and 1.

ST_EXTERIORRING(geom)

Returns a LINESTRING representing the exterior ring of the given POLYGON geom

ST_FORCE2D(geom)

Returns the 2-dimensional version (e.g., X and Y coordinates) of geom, the provided geometry or set of geometries (e.g., via GEOMETRYCOLLECTION or WKT column name).

ST_FORCE3D(geom[, z])

Returns the 3-dimensional version (e.g., X, Y, and Z coordinates) of geom, a provided geometry or set of geometries (e.g., via GEOMETRYCOLLECTION or WKT column name), using z as the geometry’s new z-value. The provided z-values can also be derived from a numeric column. If no z is provided, a 0 will be applied.

If a WKT column is provided for geom and a numeric column is provided for z, the z values will be matched to the provided geometries by row in the source table. If a singular geometry is provided for geom and a column is provided for z, three-dimensional versions of the provided geometry will be returned for each z value found in the provided z column. If columns are provided for both geom and z and nulls are present in either column, the row containing null values will be skipped in the results.

ST_GENERATEPOINTS(geom, num)

Creates a MULTIPOINT containing a number num of randomly generated points within the boundary of geom.

ST_GEOHASH(geom[, precision])

Returns a hash string representation of the given geometry geom with specified precision (the length of the resulting geohash string). The longer the precision, the more precise the hash is. By default, precision is set to 20; the max for precision is 32. Returns null if geom is an empty geometry.See Geohash for an example.

The value returned will not be a geohash of the exact geometry but a geohash of the centroid of the given geometry

ST_GEOMETRYFROMTEXT(wkt)

Alias for ST_GEOMFROMTEXT(wkt)

ST_GEOMETRYN(geom, index)

Returns the index geometry back from the given geom geometry. The index starts from 1 and goes to the number of geometries in geom.

ST_GEOMETRYTYPE(geom)

Returns the type of geometry from the given geom.

ST_GEOMETRYTYPEID(geom)

Returns the type ID of from geom. Type and ID mappings:

POINT = 0
LINESTRING = 1
POLYGON = 3
MULTIPOINT = 4
MULTILINESTRING = 5
MULTIPOLYGON = 6
GEOMETRYCOLLECTION = 7

ST_GEOMFROMGEOHASH (geohash[, precision])

Returns a POLYGON boundary box using the given geohash with a precision set by the integer precision. If precision is specified, the function will use as many characters in the hash equal to precision to create the geometry. If no precision is specified, the full length of the geohash is used.See Geohash for an example.

ST_GEOMFROMH3(h3_index)

Alias for H3_CELLTOBOUNDARY; see H3 Functions.

ST_GEOMFROMTEXT(wkt)

Returns a geometry from the given Well-Known text representation wkt. Note that this function is only compatible with constants.

ST_H3(wkt, resolution)

Alias for H3_GEOMTOCELL; see H3 Functions.

ST_HEXGRID (xmin, ymin, xmax, ymax, cell_side[, limit])

Creates a MULTIPOLYGON containing a grid of hexagons between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual hexagons’ sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

-1 - No limit to the number of cells generated (effectively limited by system memory)
0 (default) - 100 million cells
<n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

ST_INTERIORRINGN(geom, n)

Returns the n-th interior LINESTRING ring of the POLYGON geom. If geom is not a POLYGON or the given n is out of range, a null is returned. The index begins at 1

ST_INTERSECTION(geom1, geom2)

Returns the shared portion between given geometries geom1 and geom2

ST_INTERSECTS(geom1, geom2)

Returns 1 (true) if the given geometries, geom1 and geom2, intersect in 2-D

ST_ISCLOSED(geom)

Returns 1 (true) if the given geometry’s (geom) start and end points coincide

ST_ISCOLLECTION(geom)

Returns 1 (true) if geom is a collection, e.g., GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, etc.

ST_ISEMPTY(geom)

Returns 1 (true) if geom is empty

ST_ISRING(geom)

Returns 1 (true) if LINESTRING geom is both closed (per ST_ISCLOSED) and “simple” (per ST_ISSIMPLE). Returns 0 if geom is not a LINESTRING

ST_ISSIMPLE(geom)

Returns 1 (true) if geom has no anomalous geometric points, e.g., self-intersection or self-tangency

ST_ISVALID(geom)

Returns 1 (true) if geom (typically a [MULTI]POLYGON) is well formed. A POLYGON is valid if its rings do not cross, and its boundary intersects only at POINTs (not along a line). The POLYGON must also not have dangling LINESTRINGs. A MULTIPOLYGON is valid if all of its elements are also valid, and the interior rings of those elements do not intersect. Each element’s boundaries may touch but only at POINTs (not along a line). ST_MAKEVALID(geom) can be used to help correct invalid geometries.

ST_ISVALIDREASON(geom)

Returns Valid Geometry if geom is well formed, according to ST_ISVALID(geom); otherwise, returns the reason geom is determined to be malformed. ST_MAKEVALID(geom) can be used to help correct invalid geometries.Example:

Function Call	`ST_ISVALIDREASON(‘POLYGON((-1 0, 1 0, 1 1, -1 -1))‘)`
Return	`Self-intersection[0 0]`

ST_LENGTH(geom[, solution])

Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, etc. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies; the collection will be recursively searched for LINESTRINGs and MULTILINESTRINGs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available:

0 (default) - 2D Euclidean length
1 - length on a sphere in meters
2 - length on a spheroid in meters

ST_LINEFROMMULTIPOINT(geom)

Creates a LINESTRING from geom if it is a MULTIPOINT. Returns null if geom is not a MULTIPOINT

ST_LINEINTERPOLATEPOINT(geom, frac)

Returns a POINT on the LINESTRING geom that is the frac fraction of the distance along the line. If geom is either empty or not a LINESTRING, null is returned

ST_LINELOCATEPOINT(linestring, point)

Returns the location of the closest point in the given linestring to the given point as a value between 0 and 1. The return value is a fraction of the total linestring length.

ST_LINEMERGE(geom)

Returns a LINESTRING or MULTILINESTRING from a given geom. If geom is a MULTILINESTRING comprising LINESTRINGs with shared endpoints, a contiguous LINESTRING is returned. If geom is a LINESTRING or a MULTILINESTRING comprising LINESTRINGS without shared endpoints, geom is returned If geom is an empty (MULTI)LINESTRING or a (MULTI)POINT or (MULTI)POLYGON, an empty GEOMETRYCOLLECTION is returned.

ST_LINESUBSTRING (geom, start_frac, end_frac)

Returns the fraction of a given geom LINESTRING from the point that is the start_frac fraction of the distance along the line to the point that is the end_frac fraction of the distance along the line.For example, given LINESTRING(1 1, 2 2, 3 3) a start_fraction of 0 and an end_fraction of 0.25 would yield the first quarter of the given LINESTRING, or LINESTRING(1 1, 1.5 1.5).Returns null in the following cases:

input geometry is (MULTI)POINT, MULTILINESTRING, or (MULTI)POLYGON
start_frac is greater than end_frac
start_frac or end_frac are not between 0 & 1, inclusive

ST_LONGESTLINE (geom1, geom2[, solution])

Returns the LINESTRING that represents the longest line of points between the two geometries. If multiple longest lines are found, only the first line found is returned. If geom1 or geom2 is empty, null is returned. Solution types available:

0 (default) - Euclidean; uses degrees to calculate the longest line
1 - Sphere; uses meters to calculate the longest line
2 - Spheroid; uses meters to calculate the longest line, more accurate than sphere but slower performance

ST_MAKEENVELOPE (xmin, ymin, xmax, ymax)

Creates a rectangular POLYGON from the given min and max parameters

ST_MAKELINE(geom[, geom2])

Creates a LINESTRING from geom if it is a MULTIPOINT. If geom is a POINT, there must be at least one other POINT to construct a LINESTRING. If geom is a LINESTRING, it must have at least two points. Returns null if geom is not a POINT, MULTIPOINT, or LINESTRING

This function can be rather costly in terms of performance

ST_MAKEPOINT(x, y)

Creates a POINT at the given coordinate

This function can be rather costly in terms of performance

ST_MAKEPOLYGON(geom)

Creates a POLYGON from geom. Inputs must be closed LINESTRINGs

This function can be rather costly in terms of performance

ST_MAKETRIANGLE2D (x1, y1, x2, y2, x3, y3)

Creates a closed 2-D POLYGON with three vertices

ST_MAKETRIANGLE3D (x1, y1, z1, x2, y2, z2, x3, y3, z3)

Creates a closed 3-D POLYGON with three vertices

ST_MAKEVALID(geom[, options])

Attempts to convert geom into a valid geometry when it is malformed, as determined by ST_ISVALID(geom). Returns geom if it is a valid geometry already. The method used to convert invalid geometries into valid ones can be specified in options as a space-separated string of x=y key/value pairs. The keys and corresponding values are as follows:

method - the algorithm used to convert invalid geometries into valid ones; either:
- linework (default) - build geometry from lines extracted from geom
- structure - build geometry from interior & exterior rings extracted from geom
keepcollapsed - if using the method of structure, whether to drop portions of the converted geometry that collapse to lower dimensions:
- true (default) - keep portions of geometry that collapse to lower dimensions
- false - don’t keep portions of geometry that collapse to lower dimensions

Example using default linework method:

Function Call	`ST_MAKEVALID(‘POLYGON((-1 0, 1 0, 1 1, -1 -1))‘)`
Return	`MULTIPOLYGON (((-1 -1, -1 0, 0 0, -1 -1)), ((1 0, 0 0, 1 1, 1 0)))`

Example using the structure method without dropping collapsible parts of the converted geometry:

Function Call	`ST_MAKEVALID(‘POLYGON((0 0, 0 0, 0 0, 0 0))’, ‘method=structure keepcollapsed=true’)`
Return	`POINT (0 0)`

Example using the structure method with dropping collapsible parts of the converted geometry:

Function Call	`ST_MAKEVALID(‘POLYGON((0 0, 0 0, 0 0, 0 0))’, ‘method=structure keepcollapsed=false’)`
Return	`POLYGON EMPTY`

ST_MAXDISTANCE (geom1, geom2[, solution])

Returns the maximum distance between the given geom1 and geom2 geometries using the specified solution type. If geom1 or geom2 is empty, null is returned. Solution types available:

0 (default) - returns maximum 2-D Euclidean distance
1 - Sphere; returns maximum distance in meters
2 - Spheroid; returns maximum distance in meters, more accurate than sphere but slower performance

ST_MAXX(geom)

Returns the maximum x coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.

ST_MAXY(geom)

Returns the maximum y coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.

ST_MAXZ(geom)

Returns the maximum z coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.

ST_MINX(geom)

Returns the minimum x coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.

ST_MINY(geom)

Returns the minimum y coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.

ST_MINZ(geom)

Returns the minimum z coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.

ST_MULTI(geom)

Returns geom as a MULTI- geometry, e.g., a POINT would return a MULTIPOINT.

ST_MULTIPLERINGBUFFERS (geom, distance[, outside])

Creates multiple buffers at specified distance around the given geom geometry. Multiple distances are specified as comma-separated values in an array, e.g., [10,20,30]. Valid values for outside are:

FULL - indicates that buffers will overlap or cover the given geom geometry. This is the default.
OUTSIDE_ONLY - indicates that buffers will be rings around the given geom geometry.

ST_NDIMS(geom)

Returns the number of dimensions in geom. For X,Y data, this will return 2; if a Z component is present, it will return 3.

ST_NEAR(geom1, geom2)

Returns the portion of geom2 that is closest to geom1. If geom2 is a singular geometry object (e.g., POINT, LINESTRING, POLYGON), geom2 will be returned. If geom2 a multi-geometry, e.g., MULTIPOINT, MULTILINESTRING, etc., the nearest singular geometry in geom2 will be returned.

ST_NORMALIZE(geom)

Returns geom in its normalized (canonical) form, which may rearrange the points in lexicographical order.

ST_NPOINTS(geom)

Returns the number of points (vertices) in geom.

ST_NRINGS(geom)

Returns the total number of rings (including interior rings) in geom. For non-polygonal geometries, it will return 0. For MULTIPOLYGONs, it will return the total number of rings across all components.

ST_NUMGEOMETRIES(geom)

If geom is a collection or MULTI- geometry, returns the number of geometries. If geom is a single geometry, returns 1.

ST_NUMINTERIORRINGS(geom)

Returns the number of interior rings if geom is a POLYGON. Returns null if geom is anything else.

ST_NUMPOINTS(geom)

Returns the number of points in the geom LINESTRING. Returns null if geom is not a LINESTRING.

ST_OVERLAPS(geom1, geom2)

Returns 1 (true) if given geometries geom1 and geom2 share space. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless of if the two geometries overlap

ST_PARTITION(geom[, threshold])

Returns a MULTIPOLYGON representing the given geom partitioned into a number of POLYGONs with a maximum number of vertices equal to the given threshold. Minimum value for threshold is 10; default value is 10000. If geom is not a POLYGON or MULTIPOLYGON, geom is returned. If the number of vertices in geom is less than the threshold, geom is returned.

ST_PERIMETER(geom[, solution])

Returns the perimeter of the geometry if it is a POLYGON or MULTIPOLYGON. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, LINESTRING, or MULTILINESTRING. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies; the collection will be recursively searched for POLYGONs and MULTIPOLYGONs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available:

0 (default) - 2D Euclidean length
1 - length on a sphere in meters
2 - length on a spheroid in meters

ST_POINT(x, y)

Returns a POINT with the given x and y coordinates.

ST_POINTFROMGEOHASH (geohash[, precision])

Returns a POINT using the given geohash with a precision set by the integer precision. If precision is specified, the function will use as many characters in the hash equal to precision to create the geometry. If no precision is specified, the full length of the geohash is used.

The POINT returned represents the center of the bounding box of the geohash

ST_POINTGRID (xmin, ymin, xmax, ymax, cell_side[, limit])

Creates a MULTIPOLYGON containing a square-shaped grid of points between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The distance between the points (in meters) is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

-1 - No limit to the number of cells generated (effectively limited by system memory)
0 (default) - 100 million cells
<n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

ST_POINTN(geom, n)

Returns the n-th point in LINESTRING geom. Negative values are valid, but note that they are counted backwards from the end of geom. A null is returned if geom is not a LINESTRING.

ST_POINTS(geom)

Returns a MULTIPOINT containing all of the coordinates of geom.

ST_PROJECT(geom, distance, azimuth)

Returns a POINT projected from a start point geom along a geodesic calculated using distance and azimuth. If geom is not a POINT, null is returned.

ST_REMOVEPOINT(geom, offset)

Remove a point from LINESTRING geom using offset to skip over POINTs in the LINESTRING. The offset is 0-based.

ST_REMOVEREPEATEDPOINTS (geom, tolerance)

Removes points from geom if the point’s vertices are greater than or equal to the tolerance of the previous point in the geometry’s list. If geom is not a MULTIPOINT, MULTILINESTRING, or a MULTIPOLYGON, no points will be removed.

ST_REVERSE(geom)

Return the geometry with its coordinate order reversed.

ST_ROTATE(geom, radians[, wkt])

Rotates geom counter-clockwise by radians radians. Optionally, the rotation origin can be provided as a WKT POINT WKT POINT (wkt). If not provided, geom will be rotated around (0, 0).

ST_ROTATE(geom, radians[, x, y])

Rotates geom counter-clockwise by radians radians. Optionally, the rotation origin can be provided as a coordinate pair (x & y). If not provided, geom will be rotated around (0, 0).

ST_SCALE(geom, wkt)

Scales geom by multiplying its respective vertices by the corresponding x, y values in the given WKT POINT.

Function Call	`ST_SCALE(‘POLYGON((1 2, -2 1, -1 -2, 2 -1, 1 2))’, GEOMETRY(‘POINT(3 5)‘))`
Return	`POLYGON ((3 10, -6 5, -3 -10, 6 -5, 3 10))`

ST_SCALE(geom, x, y)

Scales geom by multiplying its respective vertices by the given x & y values.

Function Call	`ST_SCALE(‘POLYGON((1 2, -2 1, -1 -2, 2 -1, 1 2))’, 3, 5)`
Return	`POLYGON ((3 10, -6 5, -3 -10, 6 -5, 3 10))`

ST_SEGMENTIZE (geom, max_segment_size[, solution])

Returns the given geom, but segmentized n number of times depending on how the max_segment_size distance (in units based on the solution type) divides up the original geometry. The new geom is guaranteed to have segments that are smaller than the given max_segment_size. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can be segmentized, but only the individual parts will be segmentized, not the collection as a whole. Solution types available:

0 - Euclidean; uses degrees to calculate distance
1 (default) - Sphere; uses meters to calculate distance

ST_SETPOINT(geom1, position, geom2)

Replace a point of LINESTRING geom1 with POINT geom2 at position (base 0). Negative values are valid, but note that they are counted backwards from the end of geom.

ST_SHAREDPATH(geom1, geom2)

Returns a collection containing paths shared by geom1 and geom2.

ST_SHORTESTLINE(geom1, geom2)

Returns the 2-D LINESTRING that represents the shortest line of points between the two geometries. If multiple shortest lines are found, only the first line found is returned. If geom1 or geom2 is empty, null is returned

ST_SIMPLIFY(geom, tolerance)

Returns a simplified version of the given geom using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given tolerance determines how much to simplify the geometry. The higher the tolerance, the more simplified the returned geometry. Some holes might be removed and some invalid polygons (e.g., self-intersecting, etc.) might be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs; any other geometry objects will be returned unsimplified.

The tolerance should be provided in the same units as the data. As a rule of thumb, a tolerance of 0.00001 would correspond to about one meter.

ST_SIMPLIFYPRESERVETOPOLOGY (geom, tolerance)

Returns a simplified version of the given geom using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given tolerance determines how much to simplify the geometry. The higher the tolerance, the more simplified the returned geometry. No holes will be removed and no invalid polygons (e.g., self-intersecting, etc.) will be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs; any other geometry objects will be returned unsimplified.

The tolerance should be provided in the same units as the data. As a rule of thumb, a tolerance of 0.00001 would correspond to about one meter.

ST_SNAP(geom1, geom2, tolerance)

Snaps geom1 to geom2 within the given tolerance. If the tolerance causes geom1 to not snap, the geometries will be returned unchanged.

ST_SPLIT(geom1, geom2)

Returns a collection of geometries resulting from the split between geom1 and geom2 geometries.

ST_SQUAREGRID (xmin, ymin, xmax, ymax, cell_side[, limit])

Creates a MULTIPOLYGON containing a grid of squares between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual squares’ sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

-1 - No limit to the number of cells generated (effectively limited by system memory)
0 (default) - 100 million cells
<n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

ST_STARTPOINT(geom)

Returns the first point of LINESTRING geom as a POINT. Returns null if geom is not a LINESTRING.

ST_SYMDIFFERENCE(geom1, geom2)

Returns a geometry that represents the portions of geom1 and geom2 geometries that do not intersect.

ST_TOUCHES(geom1, geom2)

Returns 1 (true) if the given geometries, geom1 and geom2, have at least one point in common but their interiors do not intersect. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless of if the two geometries touch

ST_TRANSLATE (geom, deltax, deltay[, deltaz])

Translate geom by given offsets deltax and deltay. A z-coordinate offset can be applied using deltaz. intersect.

ST_TRIANGLEGRID (xmin, ymin, xmax, ymax, cell_side[, limit])

Creates a MULTIPOLYGON containing a grid of triangles between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual triangles’ sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

-1 - No limit to the number of cells generated (effectively limited by system memory)
0 (default) - 100 million cells
<n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

ST_UNION(geom1, geom2)

Returns a geometry that represents the point set union of the two given geometries, geom1 and geom2.

ST_UNIONCOLLECTION(geom)

Returns a geometry that represents the point set union of a single given geometry geom.

ST_UPDATE(geom1, geom2)

Returns a geometry that is geom1 geometry updated by geom2 geometry

ST_VORONOIPOLYGONS(geom[, tolerance])

Returns a GEOMETRYCOLLECTION containing Voronoi polygons (regions consisting of points closer to a vertex in geom than any other vertices in geom) calculated from the vertices in geom and the given tolerance. The tolerance determines the distance at which points will be considered the same. An empty GEOMETRYCOLLECTION is returned if geom is an empty geometry, a single POINT, or a LINESTRING or POLYGON composed of equivalent vertices (e.g., POLYGON((0 0, 0 0, 0 0, 0 0)), LINESTRING(0 0, 0 0)).If no tolerance is specified, no vertices will be considered the same; each will have its own polygon.The bounding box for the result POLYGONs extends past the four edges of the input geom bounding box by an amount that is the greater of the input bounding box’s height and width. For instance, an input geom with a 3 x 4 bounding box will result in Voronoi polygons filling a space that is 11 x 12.

ST_WITHIN(geom1, geom2)

Returns 1 (true) if the geom1 geometry is inside the geom2 geometry. Note that as long as at least one point is inside of geom2, geom1 is considered within geom2 even if the rest of the geom1 lies along the boundary of geom2

ST_WKBTOWKT(geom)

Returns the text form (WKT) of a geometry from the given byte form (WKB)

ST_WKTTOWKB(geom)

Returns the byte form (WKB) of a geometry from the given text form (WKT)

ST_X(geom)

Returns the X coordinate of the POINT geom; if the coordinate is not available, null is returned. geom must be a POINT.

ST_XMAX(geom)

Alias for ST_MAXX()

ST_XMIN(geom)

Alias for ST_MINX()

ST_Y(geom)

Returns the Y coordinate of the POINT geom; if the coordinate is not available, null is returned. geom must be a POINT.

ST_YMAX(geom)

Alias for ST_MAXY()

ST_YMIN(geom)

Alias for ST_MINY()

ST_ZMAX(geom)

Alias for ST_MAXZ()

ST_ZMIN(geom)

Alias for ST_MINZ()

Aggregation Functions

The following functions can be used on geospatial/geometry columns within aggregations.

ST_AGGREGATE_COLLECT(geom)

Alias for ST_COLLECT_AGGREGATE()

ST_AGGREGATE_INTERSECTION(geom)

Alias for ST_INTERSECTION_AGGREGATE()

ST_COLLECT_AGGREGATE(geom)

Returns a GEOMETRYCOLLECTION comprising all geometries found in the geom set. Any MULTI* geometries will be divided into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and POINT(1 1) in the results; the same is true for elements of a GEOMETRYCOLLECTION found in geom, where a GEOMETRYCOLLECTION within the provided geom set will also be parsed, effectively flattening it and adding the individual geometries to the resulting GEOMETRYCOLLECTION. Any empty geometries in geom are ignored even if they are part of a GEOMETRYCOLLECTION. Any duplicate WKTs will be retained.

ST_DISSOLVE(geom)

Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with adjacent geometries.

ST_DISSOLVEOVERLAPPING(geom)

ST_INTERSECTION_AGGREGATE(geom)

Returns a POLYGON or MULTIPOLYGON comprising the shared portion between all geometries found in the geom set. Returns an empty GEOMETRYCOLLECTION if there is no shared portion between all geometries. Functionally equivalent to ST_INTERSECTION(ST_INTERSECTION(geom1, geom2), ... geomN).

ST_LINESTRINGFROMORDEREDPOINTS(x, y, t)

Returns a LINESTRING that represents a “track” of the given points (x, y) ordered by the given sort column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are null, the null “point” will be left out of the resulting LINESTRING. If there’s only one non-null “point” in the source table, a POINT is returned. If there are no non-null “points” in the source table, a null is returned.

ST_LINESTRINGFROMORDEREDPOINTS3D(x, y, z, t)

Returns a LINESTRING that represents a “track” of the given 3D points (x, y, z) ordered by the given sort column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are null, the null “point” will be left out of the resulting LINESTRING. If there’s only one non-null “point” in the source table, a POINT is returned. If there are no non-null “points” in the source table, a null is returned.

ST_POLYGONIZE(geom)

Returns a GEOMETRYCOLLECTION containing POLYGONs comprising the provided (MULTI)LINESTRING(s). (MULTI)POINT and (MULTI)POLYGON geometries are ignored when calculating the resulting GEOMETRYCOLLECTION. If a valid POLYGON cannot be constructed from the provided (MULTI)LINESTRING(s), an empty GEOMETRYCOLLECTION will be returned.

Track Functions

The following functions are available in both SQL and the native API.

ST_TRACKDURATION([unit,] t)

Returns the total time, in the given unit, spanned by timestamp values in column t. Grouping by track ID will return the duration per track.The duration can be returned in any of the following date/time units:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
MILLISECOND

The default unit is MILLISECOND.

ST_TRACKLENGTH(lat, lon, t[, solution])

Returns the total length of the track whose position values are specified by lat & lon and whose ordering is determined by an ascending sort on the timestamp t. Length can be returned with any of the following solution types:

Type	Description
`0`	2D Euclidean length in degrees
`1`	(default) Length on a sphere in meters
`2`	Length on a spheroid in meters

ST_TRACK_DWITHIN

The ST_TRACK_DWITHIN table function finds tracks that are related, within spatial or temporal bounds (or both) to the given track(s). The track(s) to use as the filter criteria will be specified by the SEARCH_* parameters. The TRACK_* parameters specify the set of tracks to search through for a match.

This function is only available in SQL or in the native API via /execute/sql.

The basic form of the ST_TRACK_DWITHIN function follows.

ST_TRACK_DWITHIN Table Function Syntax

SELECT *
FROM TABLE
(
    ST_TRACK_DWITHIN
    (
        TRACK_TABLE => INPUT_TABLE(<table name | select statement>),
        TRACK_ID_COLUMN => < '<column name>' | <column position> >,
        TRACK_X_COLUMN => < '<column name>' | <column position> >,
        TRACK_Y_COLUMN => < '<column name>' | <column position> >,
        TRACK_ORDER_COLUMN => < '<column name>' | <column position> >,
        SEARCH_TABLE => INPUT_TABLE(<table name | select statement>),
        SEARCH_ID_COLUMN => < '<column name>' | <column position> >,
        SEARCH_X_COLUMN => < '<column name>' | <column position> >,
        SEARCH_Y_COLUMN => < '<column name>' | <column position> >,
        SEARCH_ORDER_COLUMN => < '<column name>' | <column position> >,
        [
            SEARCH_XY_DISTANCE => '<spatial distance with unit>',
            SPATIAL_SOLUTION_TYPE => <solution type>,
            SEARCH_TIME_DISTANCE => '<temporal distance with unit>'
        ]
    )
)

TRACK_TABLE

Name of the table to search for tracks matching the track(s) specified in the SEARCH_* data set.To perform a search on the flights table, pass the name of the table to INPUT_TABLE:

INPUT_TABLE(flights)

To perform a search on the result of a query, pass the query to INPUT_TABLE:

INPUT_TABLE
(
    SELECT * FROM flights_west
    UNION
    SELECT * FROM flights_east
)

TRACK_ID_COLUMN

Table to search track column, containing the unique identifier for the track to which each track point belongs.

TRACK_X_COLUMN

Table to search track column, containing the longitude value of each track point.

TRACK_Y_COLUMN

Table to search track column, containing the latitude value of each track point.

TRACK_ORDER_COLUMN

Table to search track column, by which the searched track points will be sorted in ascending order.

SEARCH_TABLE

Name of the search criteria track table, containing the track(s) to be used as the filter criteria when searching for matching tracks in the TRACK_* data set.To match tracks from the flights_of_interest table, pass the name of the table to INPUT_TABLE:

INPUT_TABLE(flights_of_interest)

To match tracks from the result of a query, pass the query to INPUT_TABLE:

INPUT_TABLE
(
    SELECT * FROM flights_of_interest_west
    UNION
    SELECT * FROM flights_of_interest_east
)

SEARCH_ID_COLUMN

Search criteria track column, containing the unique identifier for the track to which each track point belongs.

SEARCH_X_COLUMN

Search criteria track column, containing the longitude value of each track point.

SEARCH_Y_COLUMN

Search criteria track column, containing the latitude value of each track point.

SEARCH_ORDER_COLUMN

Search criteria track column, by which the filter track points will be sorted in ascending order.

SEARCH_XY_DISTANCE

The radius around the given tracks to search for matching tracks.

This parameter is not applicable when using a SPATIAL_SOLUTION_TYPE of 0.

Unit	Description
`f`	Feet
`ki`	Kilometers
`m`	(default) Meters
`mi`	Miles

SPATIAL_SOLUTION_TYPE

Spatial match solution type; any of the following:

Type	Description
`0`	(default) 2D Euclidean length in degrees
`1`	Length on a sphere, returned in units specified by `SEARCH_XY_DISTANCE`
`2`	Length on a spheroid, returned in units specified by `SEARCH_XY_DISTANCE`

SEARCH_TIME_DISTANCE

The maximum allowable time difference between a search criteria track’s point and a matched track’s points. The time can use any of the following suffices for units:

Unit	Description
`ms`	Milliseconds
`s`	(default) Seconds
`m`	Minutes
`h`	Hours
`d`	Days
`w`	Weeks
`months`	Months
`y`	Years

To see the matches between a set of flights and a given set of flights of interest:

ST_TRACK_DWITHIN Example

SELECT *
FROM TABLE
(
	ST_TRACK_DWITHIN
	(
		TRACK_TABLE => INPUT_TABLE(example_geospatial.flights),
		TRACK_ID_COLUMN => 'id',
		TRACK_X_COLUMN => 'lon',
		TRACK_Y_COLUMN => 'lat',
		TRACK_ORDER_COLUMN => 'flight_time',
		SEARCH_TABLE => INPUT_TABLE
						(
							SELECT id, lon, lat, flight_time
							FROM example_geospatial.flights_northwest
							UNION
							SELECT id, lon, lat, flight_time
							FROM example_geospatial.flights_northeast
						),
		SEARCH_ID_COLUMN => 1,
		SEARCH_X_COLUMN => 2,
		SEARCH_Y_COLUMN => 3,
		SEARCH_ORDER_COLUMN => 4,
		SEARCH_TIME_DISTANCE => '5m',
		SEARCH_XY_DISTANCE => '1km',
		SPATIAL_SOLUTION_TYPE => 1
	)
)

ST_TRACKINTERSECTS

The ST_TRACKINTERSECTS table function finds tracks pass through the given geofence(s). The geofence(s) use as the filter will be specified by the GEOFENCE_* parameters. The TRACK_* parameters specify the set of tracks to search through for any intersecting the geofence(s). The result will include a record for each intersecting track & geofence pair, with:

a LINESTRING representing the full track intersecting a geofence
a LINESTRING representing the geofence it intersected

This function is only available in SQL or in the native API via /execute/sql.

The basic form of the ST_TRACKINTERSECTS function follows.

ST_TRACKINTERSECTS Table Function Syntax

SELECT *
FROM TABLE
(
    ST_TRACKINTERSECTS
    (
        TRACK_TABLE => INPUT_TABLE(<table name | select statement>),
        TRACK_ID_COLUMN => < '<column name>' | <column position> >,
        TRACK_X_COLUMN => < '<column name>' | <column position> >,
        TRACK_Y_COLUMN => < '<column name>' | <column position> >,
        TRACK_ORDER_COLUMN => < '<column name>' | <column position> >,
        GEOFENCE_TABLE => INPUT_TABLE(<table name | select statement>),
        GEOFENCE_ID_COLUMN => < '<column name>' | <column position> >,
        GEOFENCE_WKT_COLUMN => < '<column name>' | <column position> >
    )
)

TRACK_TABLE

Name of the table to search for tracks intersecting the geofence(s) specified in the SEARCH_* data set.To perform a search on the flights table, pass the name of the table to INPUT_TABLE:

INPUT_TABLE(flights)

To perform a search on the result of a query, pass the query to INPUT_TABLE:

INPUT_TABLE
(
    SELECT * FROM flights_west
    UNION
    SELECT * FROM flights_east
)

TRACK_ID_COLUMN

Table to search track column, containing the unique identifier for the track to which each track point belongs.

TRACK_X_COLUMN

Table to search track column, containing the longitude value of each track point.

TRACK_Y_COLUMN

Table to search track column, containing the latitude value of each track point.

TRACK_ORDER_COLUMN

Table to search track column, by which the searched track points will be sorted in ascending order.

GEOFENCE_TABLE

Name of the geofence table, containing the WKT(s) to be used as the filter criteria when searching for intersecting tracks in the TRACK_* data set.To search for tracks intersecting the geofence(s) from the flight_area_of_interest table, pass the name of the geofence table to INPUT_TABLE:

INPUT_TABLE(flight_area_of_interest)

To search for tracks intersecting the geofence(s) from the result of a query, pass the query to INPUT_TABLE:

INPUT_TABLE
(
    SELECT * FROM flight_area_of_interest_west
    UNION
    SELECT * FROM flight_area_of_interest_east
)

GEOFENCE_ID_COLUMN

Geofence column, containing the unique identifier for the geofence.

GEOFENCE_WKT_COLUMN

Geofence column, containing the WKT bounds of the geofence.

To see the intersections between a set of flights and an area of interest:

ST_TRACKINTERSECTS Example

SELECT *
FROM TABLE
(
	ST_TRACKINTERSECTS
	(
		TRACK_TABLE =>         INPUT_TABLE(example_geospatial.flights),
		TRACK_ID_COLUMN =>     'id',
		TRACK_X_COLUMN =>      'lon',
		TRACK_Y_COLUMN =>      'lat',
		TRACK_ORDER_COLUMN =>  'flight_time',
		GEOFENCE_TABLE =>      INPUT_TABLE(example_geospatial.track_geofence),
		GEOFENCE_ID_COLUMN =>  'fence_name',
		GEOFENCE_WKT_COLUMN => 'fence_wkt'
	)
)
ORDER BY id, fence_name

H3 Functions

The functions below support various operations using the H3 geospatial indexing scheme.

H3_CELLTOBOUNDARY(h3_index)

Returns a POLYGON boundary box of the H3 index identified by the given h3_index.See Geohash-H3 for an example.

H3_CELLTOCENTERCHILD(h3_index, res)

Alias for H3_CELLTOFIRSTCHILD.

H3_CELLTOCHILDN(h3_index, res, i)

Returns the H3 index corresponding to the 0-based i th child at resolution res for the given h3_index. The value i should be less than the number of children returned from calling H3_CELLTOCHILDRENSIZE(h3_index, res).

H3_CELLTOCHILDPOS(h3_index, res)

Returns the position of the given h3_index within an ordered list of the children of the cell’s parent at resolution res. This is the inverse of H3_CHILDPOSTOCELL (H3_CELLTOCHILDN).

H3_CELLTOCHILDRENSIZE(h3_index, res)

Returns the number of child cells at resolution res for the given h3_index.

H3_CELLTOFIRSTCHILD(h3_index, res)

Returns the H3 index corresponding to the first child at resolution res for the given h3_index.This is equivalent to H3_CELLTOCHILDN(h3_index,res,0).

H3_CELLTOLASTCHILD(h3_index, res)

Returns the H3 index corresponding to the last child at resolution res for the given h3_index.This is equivalent to H3_CELLTOCHILDN(h3_index,res,H3_CELLTOCHILDRENSIZE(h3_index, res)-1).

H3_CELLTOPARENT(h3_index, res)

Returns the H3 index corresponding to the parent cell of the given h3_index at resolution res.

H3_CELLTOXY(h3_index)

Returns a WKT POINT corresponding to the centroid of the given h3_index.

H3_CHILDPOSTOCELL(i, h3_index, res)

Alias for H3_CELLTOCHILDN(h3_index, res, i).

H3_GEOMTOCELL(geom, res)

Returns the H3 index, similar to a geohash, for the cell containing the centroid of the geometry geom with the given resolution res. The higher the resolution, the more precise the index is. The resolution res must be an integer between 0 and 15.See Geohash-H3 for an example.

H3_GETRESOLUTION(h3_index)

Returns the resolution of the H3 index h3_index.

H3_GRIDDISK(h3_index, k)

Returns an array of H3 indexes within a given distance k from the provided H3 index h3_index.

H3_GRIDDISKN(h3_index, k, i)

Returns the i ^th H3 index within a given distance k from the provided H3 index h3_index. This function would typically be used in conjunction with H3_NUMGRIDDISK via iter-join. The value of i should be between 0 and the result of H3_NUMGRIDDISK(h3_index, k) - 1.

H3_GRIDRING(h3_index, k)

Returns an array of H3 indexes comprising a hollow ring at a distance k from the provided H3 index h3_index.

H3_H3TOSTRING(h3_index)

Returns the string representation of the H3 index h3_index.

This function is the inverse of H3_STRINGTOH3.

H3_ISVALID(h3_index)

Returns 1 (true) if the given H3 index h3_index is a valid H3 index value; otherwise returns 0 (false).

H3_LATLNGTOCELL (latitude, longitude, res)

Returns the H3 index, similar to a geohash, for the cell containing the latitude and longitude coordinate, with the given resolution res. The higher the resolution, the more precise the index is. The resolution res must be an integer between 0 and 15.Equivalent to H3_XYTOCELL(longitude, latitude, res).

H3_NUMGRIDDISK(h3_index, k)

Returns the number of cells at a distance of k from the provided H3 index h3_index. This function would typically be used in conjunction with H3_GRIDDISKN via iter-join.

H3_NUMPOLYGONTOCELLS(geom, res)

Returns the number of cells at the given resolution res that are within the given geometry geom. Only polygon geometries are supported. This function would typically be used in conjunction with H3_POLYGONTOCELLSN via iter-join.

H3_POLYGONTOCELLS(geom, res)

Returns an array of H3 indexes at the given resolution res that are within the given geometry geom. Only polygon geometries are supported.

H3_POLYGONTOCELLSN(geom, res, i)

Returns the i ^th H3 index at the given resolution res that is within the given geometry geom. Only polygon geometries are supported. This function would typically be used in conjunction with H3_NUMPOLYGONTOCELLS via iter-join. The value of i should be between 0 and the value returned from H3_NUMPOLYGONTOCELLS(geom, res) - 1.

H3_STRINGTOH3(h3_string)

Returns the H3 index corresponding to the string representation h3_string.

This function is the inverse of H3_H3TOSTRING.

H3_XYTOCELL(x, y, res)

Returns the H3 index, similar to a geohash, for the cell containing the x and y coordinate, with the given resolution res. The higher the resolution, the more precise the index is. The resolution res must be an integer between 0 and 15.Equivalent to H3_LATLNGTOCELL(y, x, res).See Geohash-H3 for an example.

JSON Functions

Scalar Functions

These functions can be applied to individual json column values, as well as array & string columns. However, for some functions to work as expected, the array or string value may need to be cast to JSON first.

JSON(expr)

Casts the given expr to JSON data typeExamples:

Function Call	Result
`JSON(‘[1, 2, “C”]’)`	`[1,2,“C”]`
`JSON(’{“A”: “B”, “C”: {“D”: 5, “F”: 7}}’)`	`{“A”:“B”,“C”:{“D”:5,“F”:7}}`
`JSON(null)`	null

JSON_ARRAY (value[,...] <null_hand>)

Creates a JSON array out of the given list of values.An optional <null_hand> parameter can be added to the end of the list to specify handling of null values, though, at present, this only exists for compatibility with other databases’ syntax—the option will be ignored and all null values will be added to the array as null. Accepted null handling options:

NULL ON NULL

This function is only available in SQL or in the native API via /execute/sql.

Examples:

Function Call	Result
`JSON_ARRAY(1, 2, ‘C’)`	`[1,2,“C”]`
`JSON_ARRAY(JSON(’{“A”: “B”}’), JSON(’{“C”: 4}’))`	`[{“A”:“B”},{“C”:4}]`
`JSON_ARRAY(1, null, ‘C’ NULL ON NULL)`	`[1,null,“C”]`

JSON_ARRAY_APPEND(arr, value)

Appends the given value to the given array arr of type string, array, or JSONExamples:

Function Call	Result
`JSON_ARRAY_APPEND(‘[1, 2]’, ‘C’)`	`[1,2,“C”]`
`JSON_ARRAY_APPEND(’{“A”: “B”}’, JSON(’{“C”: 4}’))`	`[{“A”:“B”},{“C”:4}]`

JSON_ARRAY_CONTAINS (json, path, val)

Returns whether the given JSON json contains the primitive value val at the JSON query path, pathExamples:

Function Call	Result
`JSON_ARRAY_CONTAINS(‘[1, 2]’, ’$’, 2)`	`true`
`JSON_ARRAY_CONTAINS(‘[1, 2]’, ’$’, ‘C’)`	`false`
`JSON_ARRAY_CONTAINS(’{“A”: [2, 3]}’, ’$.A’, 2)`	`true`
`JSON_ARRAY_CONTAINS(’{“A”: {“B”: [3, 4]}}’, ’$[“A”][“B”]’, 4)`	`true`

JSON_CARDINALITY(json)

Alias for JSON_LENGTH

JSON_EXISTS(json, path)

Returns whether the given JSON json contains the JSON query path, pathExamples:

Function Call	Result
`JSON_EXISTS(‘[1, 2]’, ’$‘)`	`true`
`JSON_EXISTS(‘[1, 2]’, ’$.A’)`	`false`
`JSON_EXISTS(’{“A”: [2, 3]}’, ’$.A’)`	`true`
`JSON_EXISTS(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)`	`true`

JSON_KEYS(json, path)

Returns the set of keys of the JSON object at the given path in json; null, if the path doesn’t exist or contains a non-object (non-map) valueExamples:

Function Call	Result
`JSON_KEYS(’{}’, ’$’)`	`[]`
`JSON_KEYS(‘[1, 2]’, ’$.A’)`	null
`JSON_KEYS(’{“A”: [2, 3]}’, ’$’)`	`[“A”]`
`JSON_KEYS(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”]’)`	`[“B”,“D”]`
`JSON_KEYS(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)`	null
`JSON_KEYS(’{“A”: {“B”: 3, “D”: {}}}’, ’$[“A”][“D”]’)`	`[]`

JSON_LENGTH(json[, path])

Returns the number of items in the top-level object of the given json, or at the level of the optional JSON pathExamples:

Function Call	Result
`JSON_LENGTH(’{}‘)`	`0`
`JSON_LENGTH(’[]‘)`	`0`
`JSON_LENGTH(‘[1, 2]‘)`	`2`
`JSON_LENGTH(’{“A”: [2, 3]}‘)`	`1`
`JSON_LENGTH(’{“A”: {“B”: 3}, “D”: “E”}‘)`	`2`
`JSON_LENGTH(’{“A”: [2, 3]}’, ’$.A’)`	`2`
`JSON_LENGTH(’{“A”: {“B”: 3}, “D”: “E”}’, ’$.A’)`	`1`

JSON_MAKE_ARRAY(value)

Creates a JSON array out of the given valueExamples:

Function Call	Result
`JSON_MAKE_ARRAY(1)`	`[1]`
`JSON_MAKE_ARRAY(JSON_ARRAY(1,2,3))`	`[[1,2,3]]`
`JSON_MAKE_ARRAY(JSON(’{“A”: “B”}’))`	`[{“A”:“B”}]`

JSON_OBJECT (value[,...] <null_hand>)

Creates a JSON map out of the given value setAn optional <null_hand> parameter can be added to the end of the list to specify handling of null values, though, at present, this only exists for compatibility with other databases’ syntax—the option will be ignored and all null values will be added to the array as null. Accepted null handling options:

NULL ON NULL

This function is only available in SQL or in the native API via /execute/sql.

Examples:

Function Call	Result
`JSON_OBJECT()`	`{}`
`JSON_OBJECT(‘A’: 2)`	`{“A”:2}`
`JSON_OBJECT(‘A’ VALUE 2)`	`{“A”:2}`
`JSON_OBJECT(KEY ‘A’ VALUE 2)`	`{“A”:2}`
`JSON_OBJECT(‘A’: ‘B’, ‘C’: null NULL ON NULL)`	`{“A”:“B”,“C”:null}`

JSON_PRETTY(json)

Returns a formatted version of the given json objectExamples:

Function Call	`JSON_PRETTY(‘[1,2,“C”]‘)`
Return	`[ 1, 2, “C” ]`

Function Call	`JSON_PRETTY(’{“A”: “B”, “C”: {“D”: 5, “F”: 7}}‘)`
Return	`{ “A”: “B”, “C”: { “D”: 5, “F”: 7 } ]`

JSON_QUERY (json, path [<return>])

Returns the JSON object at the given path in json; null, if the path doesn’t exist or contains a primitive (non-object) valueAn optional return clause can be added to the end of the path to specify the return type of the object found, as well as the error handling for the object lookup. The format of the clause is:

[<wrapper> WRAPPER] [<empty_hand> ON EMPTY] [<error hand> ON ERROR]

Return clause is only available in SQL or in the native API via /execute/sql.

Parameter	Description
`<wrapper>`	Whether the returned object(s) should be wrapped in an array before being returned Valid wrapping schemes include: `WITHOUT [ARRAY]` - no wrapper is applied (default) `WITH [UNCONDITIONAL] [ARRAY]` - return the object(s) in an array `WITH CONDITIONAL [ARRAY]` - if returning a single object, don’t wrap it; if returning multiple objects, wrap them in an array
`<empty hand>`	The scheme to use for handling empty returned objects Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `EMPTY ARRAY` - return an empty array `EMPTY OBJECT` - return an empty object
`<error hand>`	The scheme to use for handling errors in looking up the object Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `EMPTY ARRAY` - return an empty array `EMPTY OBJECT` - return an empty object

Examples:

Function Call	Result
`JSON_QUERY(‘[1, 2]’, ’$’)`	`[1,2]`
`JSON_QUERY(‘[1, 2]’, ’$.A’)`	null
`JSON_QUERY(’{“A”: [2, 3]}’, ’$.A’)`	`[2,3]`
`JSON_QUERY(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”]’)`	`{“B”:3,“D”:5}`
`JSON_QUERY(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)`	null
`JSON_QUERY(’{“A”: {“B”: 3, “D”: [5, 6]}}’, ’$[“A”][“D”]’)`	`[5,6]`
`JSON_QUERY(’{“A”: []}’, ’$.B’ EMPTY ARRAY ON EMPTY)`	`[]`
`JSON_QUERY(’{“A”: 0}’, ’$.A’ EMPTY OBJECT ON ERROR)`	`{}`
`JSON_QUERY(’{“A”: 2, “C”: 4}’, ’$.*‘)`	null
`JSON_QUERY(’{“A”: 2, “C”: 4}’, ’$.*’ WITH WRAPPER)`	`[2,4]`

JSON_REPLACE(json, path, repl)

Returns the given json with the value at the JSON path path replaced with the replacement value replExamples:

Function Call	Result
`JSON_REPLACE(‘[1, 2]’, ’$’, ‘[3, 4]’)`	`[3,4]`
`JSON_REPLACE(‘[1, 2]’, ’$.A’, ‘“B”’)`	`[1,2]`
`JSON_REPLACE(’{“A”: [2, 3]}’, ’$.A’, ’{“D”: 5}’)`	`{“A”:{“D”:5}}`
`JSON_REPLACE(’{“A”: {“B”: 3}}’, ’$[“A”]’, ‘“D”’)`	`{“A”:“D”}`
`JSON_REPLACE(’{“A”: {“B”: [3, 4]}}’, ’$[“A”][“B”]’, 5)`	`{“A”:{“B”:5}}`

JSON_TYPE(expr)

Returns the JSON type of the given expr

Expression Type	JSON Type
`BOOLEAN`	`BOOLEAN`
`TINYINT` `SMALLINT` `INTEGER` `BIGINT` `UNSIGNED BIGINT`	`LONG`
`REAL` `DOUBLE`	`DOUBLE`
`JSON` `VARCHAR`	`BOOLEAN` - if `true` or `false` `LONG` - if an integer value `DOUBLE` - if a floating-point value `STRING` - if a double-quoted literal `OBJECT` - if a JSON map `ARRAY` - if a JSON array
`ARRAY` (any)	`ARRAY`

JSON_VALUE (json, path [return])

Returns the JSON value at the given path in json; null, if the path doesn’t exist or contains an object (non-primitive) valueAn optional return clause can be added to the end of the path to specify the return type of the value found, as well as the error handling for the value lookup. The format of the clause is:

[RETURNING <type>] [<empty_hand> ON EMPTY] [<error hand> ON ERROR]

Return clause is only available in SQL or in the native API via /execute/sql.

Parameter	Description
`<type>`	The JSON type of the value to return; if the value is not already of that type, it will be cast to that type, if possible Valid types include: `BOOLEAN` `LONG` `DOUBLE` `STRING`
`<empty hand>`	The scheme to use for handling empty returned values Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `DEFAULT <expr>` - return the given expression `expr`
`<error hand>`	The scheme to use for handling errors in looking up the value Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `DEFAULT <expr>` - return the given expression `expr`

Examples:

Function Call	Result
`JSON_VALUE(‘1’, ’$‘)`	`1`
`JSON_VALUE(‘[1, 2]’, ’$.A’)`	null
`JSON_VALUE(’[“A”, “B”]’, ‘$.1’)`	`B`
`JSON_VALUE(’{“A”: [2, 3]}’, ’$[“A”][“1”]‘)`	`3`
`JSON_VALUE(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)`	`5`
`JSON_VALUE(’{“A”: {“B”: 3, “D”: [5, 6]}}’, ’$.A.D.1’)`	`6`
`JSON_VALUE(‘1’, ’$’ RETURNING DOUBLE)`	`1.0`
`JSON_VALUE(’{“A”: []}’, ’$.B’ DEFAULT ‘<null>’ ON EMPTY)`	`<null>`
`JSON_VALUE(’{“A”: []}’, ’$.A’ ERROR ON ERROR)`	error returned

Aggregation Functions

The following functions can be used on JSON columns within aggregations.

JSON_ARRAYAGG(expr)

Combines column values within a group into a single JSON array of those values.See examples.

JSON_ARRAYAGG_DISTINCT(expr)

Combines column values within a group into a single JSON array of those values, removing any duplicates.See examples.

JSON_COLLECT_SET(expr)

Alias for JSON_ARRAYAGG_DISTINCT.

Math Functions

Scalar Functions

ABS(expr)

Calculates the absolute value of expr

ACOS(expr)

Returns the inverse cosine (arccosine) of expr as a double

ACOSF(expr)

Returns the inverse cosine (arccosine) of expr as a float

ACOSH(expr)

Returns the inverse hyperbolic cosine of expr as a double

ACOSHF(expr)

Returns the inverse hyperbolic cosine of expr as a float

ASIN(expr)

Returns the inverse sine (arcsine) of expr as a double

ASINF(expr)

Returns the inverse sine (arcsine) of expr as a float

ASINH(expr)

Returns the inverse hyperbolic sine of expr as a double

ASINHF(expr)

Returns the inverse hyperbolic sine of expr as a float

ATAN(expr)

Returns the inverse tangent (arctangent) of expr as a double

ATANF(expr)

Returns the inverse tangent (arctangent) of expr as a float

ATANH(expr)

Returns the inverse hyperbolic tangent of expr as a double

ATANHF(expr)

Returns the inverse hyperbolic tangent of expr as a float

ATAN2(x, y)

Returns the inverse tangent (arctangent) using two arguments as a double

ATAN2F(x, y)

Returns the inverse tangent (arctangent) using two arguments as a float

ATN2(x, y)

Alias for ATAN2

ATN2F(x, y)

Alias for ATAN2F

CBRT(expr)

Returns the cube root of expr as a double

CBRTF(expr)

Returns the cube root of expr as a float

CEIL(expr)

Alias for CEILING

CEILING(expr)

Rounds expr up to the next highest integer

COS(expr)

Returns the cosine of expr as a double

COSF(expr)

Returns the cosine of expr as a float

COSH(expr)

Returns the hyperbolic cosine of expr as a double

COSHF(expr)

Returns the hyperbolic cosine of expr as a float

COT(expr)

Returns the cotangent of expr as a double

COTF(expr)

Returns the cotangent of expr as a float

DEGREES(expr)

Returns the conversion of expr (in radians) to degrees as a double

DEGREESF(expr)

Returns the conversion of expr (in radians) to degrees as a float

DIVZ(a, b, c)

Returns the quotient a / b unless b == 0, in which case it returns c

EXP(expr)

Returns e to the power of expr as a double

EXPF(expr)

Returns e to the power of expr as a float

FLOOR(expr)

Rounds expr down to the next lowest integer

GREATER(expr_a, expr_b)

Returns whichever of expr_a and expr_b has the larger value, based on typed comparison

HYPOT(x, y)

Returns the hypotenuse of x and y as a double

HYPOTF(x, y)

Returns the hypotenuse of x and y as a float

IFERROR(expr, val)

Alias for IF_ERROR(expr, val)

IFINF(expr, val)

Alias for IF_INF(expr, val)

IFINFINITY(expr, val)

Alias for IF_INF(expr, val)

IFNAN(expr, val)

Alias for IF_NAN(expr, val)

IF_ERROR(expr, val)

Evaluates the given double or float expr, and if it resolves to infinity or NaN, return val

Conceptually, this function is the same as IF_INF(IF_NAN(expr, val), val)

Example:

Function Call	Result
`IF_ERROR((double(10)/0), 1)`	`1.0`
`IF_ERROR(log(-1), 1)`	`1.0`

IF_INF(expr, val)

Evaluates the given double or float expr, and if it resolves to infinity, return valExample:

Function Call	Result
`IF_INF((double(10)/0), 999)`	`999.0`
`IF_INF(log(-1), 999)`	`NaN`

IF_INFINITY(expr, val)

Alias for IF_INF(expr, val)

IF_NAN(expr, val)

Evaluates the given double or float expr, and if it resolves to NaN, return valExample:

Function Call	Result
`IF_NAN((double(10)/0), -1)`	`Infinity`
`IF_NAN(log(-1), -1)`	`-1.0`

ISINFINITY(expr)

Returns 1 (true) if expr is infinity by IEEE standard; otherwise, returns 0 (false)

IS_INFINITY(expr)

Alias for ISINFINITY

ISNAN(expr)

Returns 1 (true) if expr is not a number by IEEE standard; otherwise, returns 0 (false)

IS_NAN(expr)

Alias for ISNAN

ISNUMERIC(expr)

Returns 1 (true) if expr is a number by IEEE standard; otherwise, returns 0 (false)

IS_NUMERIC(expr)

Alias for ISNUMERIC

LDEXP(x, exp)

Returns the value of x * 2^exp as a double

LDEXPF(x, exp)

Returns the value of x * 2^exp as a float

LESSER(expr_a, expr_b)

Returns whichever of expr_a and expr_b has the smaller value, based on typed comparison

LN(expr)

Returns the natural logarithm of expr as a double

LNF(expr)

Returns the natural logarithm of expr as a float

LOG(expr)

Alias for LN

LOG10(expr)

Returns the base-10 logarithm of expr as a double

LOG10F(expr)

Returns the base-10 logarithm of expr as a float

LOG1P(expr)

Returns the natural logarithm of one plus expr as a double

LOG1PF(expr)

Returns the natural logarithm of one plus expr as a float

LOG2(expr)

Returns the binary (base-2) logarithm of expr as a double

LOG2F(expr)

Returns the binary (base-2) logarithm of expr as a float

LOGF(expr)

Alias for LNF

MAX_CONSECUTIVE_BITS(expr)

Calculates the length of the longest series of consecutive 1 bits in the integer expr

MOD(dividend, divisor)

Calculates the remainder after integer division of dividend by divisor

NEXT_AFTER(expr[, target])

Returns the next representable floating-point value adjacent to expr in the direction of target, incrementing expr if target is greater, decrementing if lesser. The default value of target is MAX_DOUBLE (always increments when omitted). The return type matches the input (float or double).

NEXT_BEFORE(expr)

Returns the next representable floating-point value less than expr. The return type matches the input (float or double).

PI()

Returns the value of pi

POW(base, exponent)

Alias for POWER

POWF(base, exponent)

Alias for POWERF

POWER(base, exponent)

Returns base raised to the power of exponent as a double

POWERF(base, exponent)

Returns base raised to the power of exponent as a float

RADIANS(expr)

Returns the conversion of expr (in degrees) to radians as a double

RADIANSF(expr)

Returns the conversion of expr (in degrees) to radians as a float

RAND([seed])

Returns a random floating-point value, with an optional seed

REGR_VALX(y, x)

Returns NULL if y is NULL; otherwise, returns x

REGR_VALY(y, x)

Returns NULL if x is NULL; otherwise, returns y

ROUND(expr[, scale])

Rounds expr to the nearest decimal number with scale decimal places when scale is a positive number; rounds to the nearest number such that the result has -(scale) zeros to the left of the decimal point when scale is negative; use scale of 0 to round to the nearest integer.The default value of scale is 0.Examples:

Function Call	Result
`ROUND(12345.678)`	`12346`
`ROUND(12345.678, 2)`	`12345.68`
`ROUND(12345.678, 0)`	`12346`
`ROUND(12345.678, -2)`	`12300`

SIGN(expr)

Determines whether a number is positive, negative, or zero; returns one of the following three values:

Expression Value	Result
positive	`1`
zero	`0`
negative	`-1`

SIN(expr)

Returns the sine of expr as a double

SINF(expr)

Returns the sine of expr as a float

SINH(expr)

Returns the hyperbolic sine of expr as a double

SINHF(expr)

Returns the hyperbolic sine of expr as a float

SQRT(expr)

Returns the square root of expr as a double

SQRTF(expr)

Returns the square root of expr as a float

TAN(expr)

Returns the tangent of expr as a double

TANF(expr)

Returns the tangent of expr as a float

TANH(expr)

Returns the hyperbolic tangent of expr as a double

TANHF(expr)

Returns the hyperbolic tangent of expr as a float

TRUNCATE(expr[, scale])

Rounds expr down to the nearest decimal number with scale decimal places when scale is a positive number; rounds down to the nearest number such that the result has -(scale) zeros to the left of the decimal point when scale is negative; use scale of 0 to round down to the nearest integer.The default value of scale is 0.Examples:

Function Call	Result
`TRUNCATE(12345.678)`	`12345`
`TRUNCATE(12345.678, 2)`	`12345.67`
`TRUNCATE(12345.678, 0)`	`12345`
`TRUNCATE(12345.678, -2)`	`12300`

WIDTH_BUCKET(expr, min, max, count)

Defines a set of count equal intervals (buckets) within the range of min & max, and puts the value of expr into one of those buckets, where the value is greater than or equal to the minimum value of the bucket and less than the maximum value of the bucket. Returns the 1-based number of the bucket into which the value of expr fell. For values smaller than min, 0 is returned; for values greater than or equal to max, count + 1 is returned. Examples:In the following examples, a set of 5 equal buckets are defined between 0 and 10 (0-2, 2-4, 4-6, 6-8, & 8-10), and various values are bucketed using that set.

Function Call	Result
`WIDTH_BUCKET(-1, 0, 10, 5)`	`0`
`WIDTH_BUCKET(0, 0, 10, 5)`	`1`
`WIDTH_BUCKET(5, 0, 10, 5)`	`3`
`WIDTH_BUCKET(10, 0, 10, 5)`	`6`
`WIDTH_BUCKET(11, 0, 10, 5)`	`6`

Aggregate Functions

The following functions can be used on numeric columns within aggregations.

APPROX_COUNT_DISTINCT(expr)

The approximate number of distinct values of expr; this is faster to calculate than COUNT_DISTINCT but is only an approximation

APPROX_MEDIAN(expr)

The approximate median of expr; the result should be within about 2% of the true median value. This is equivalent to issuing APPROX_PERCENTILE(expr, 50).

APPROX_PERCENTILE(expr, p)

The approximate pth percentile of expr; p should be a value between 0.0 and 100.0. APPROX_PERCENTILE(expr, 50) will return the approximate median of expr.

AVG(expr)

Average of the values of expr

CORR(expr1, expr2)

Correlation coefficient of the values of expr1 and expr2

CORRELATION(expr1, expr2)

Alias for CORR

CORRCOEF(expr1, expr2)

Alias for CORR

COUNT(expr)

Count of non-null values of expr; use * to count all values within an aggregation group or over an entire table

COUNT_DISTINCT(expr)

Count of the distinct values of expr

COV(expr1, expr2)

Alias for COVAR_POP

COVAR(expr1, expr2)

Alias for COVAR_POP

COVARIANCE(expr1, expr2)

Alias for COVAR_POP

COVAR_POP(expr1, expr2)

Population covariance of the values of expr1 and expr2

COVAR_SAMP(expr1, expr2)

Sample covariance of the values of expr1 and expr2

KURT(expr)

Alias for KURTOSIS_POP

KURTOSIS(expr)

Alias for KURTOSIS_POP

KURTOSIS_POP(expr)

Population kurtosis of the values of expr

KURTOSIS_SAMP(expr)

Sample kurtosis of the values of expr

KURT_POP(expr)

Alias for KURTOSIS_POP

KURT_SAMP(expr)

Alias for KURTOSIS_SAMP

MAX(expr)

Maximum of the values of expr

MEAN(expr)

Alias for AVG

MIN(expr)

Minimum of the values of expr

PRODUCT(expr)

Product of the values of expr

REGR_AVGX(y, x)

Average of the independent variable (SUM(x)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_AVGY(y, x)

Average of the dependent variable (SUM(y)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_COUNT(y, x)

Number of input rows used in computing a linear regression, where both expressions are non-null

REGR_INTERCEPT(y, x)

Y-intercept of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_R2(y, x)

Square of the correlation coefficient, marking how well the least-squares-fit linear regression fit the data set

REGR_SLOPE(y, x)

Slope of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_SXX(y, x)

“Sum of squares” of the independent variable (SUM(x^2) - SUM(x)^2/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_SXY(y, x)

“Sum of Products” of independent variable times dependent variable (SUM(x * y) - SUM(x) * SUM(y)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_SYY(y, x)

“Sum of squares” of the dependent variable (SUM(y^2) - SUM(y)^2/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

SKEW(expr)

Alias for SKEWNESS_POP

SKEWNESS(expr)

Alias for SKEWNESS_POP

SKEWNESS_POP(expr)

Population skew of the values of expr

SKEWNESS_SAMP(expr)

Sample skew of the values of expr

SKEW_POP(expr)

Alias for SKEWNESS_POP

SKEW_SAMP(expr)

Alias for SKEWNESS_SAMP

STDDEV(expr)

Population standard deviation over values of expr (i.e. the denominator is N)

STDDEV_POP(expr)

Population standard deviation over values of expr (i.e. the denominator is N)

STDDEV_SAMP(expr)

Sample standard deviation over values of expr (i.e. the denominator is N-1)

SUM(expr)

Sum of the values of expr

VAR(expr)

Population variance over values of expr (i.e. the denominator is N)

VAR_POP(expr)

Population variance over values of expr (i.e. the denominator is N)

VAR_SAMP(expr)

Sample variance over values of expr (i.e. the denominator is N-1)

VARIANCE(expr)

Alias for VAR

VARIANCE_POP(expr)

Alias for VAR_POP

VARIANCE_SAMP(expr)

Alias for VAR_SAMP

Null Functions

Be mindful that no error is thrown when Kinetica tries to convert different data type in the Null functions below, so if the output is unexpected, it may be that the types used aren’t of the same type.

IS_NULL(expr)

Returns 1 (true) if expr is null; otherwise, returns 0 (false)

ISNULL(expr)

Alias for IS_NULL(expr)

NULLIF(expr_a, expr_b)

Returns null if expr_a equals expr_b; otherwise, returns the value of expr_a. Both expressions should be of the same convertible data type

NVL(expr_a, expr_b)

Returns expr_a if it is not null; otherwise, returns expr_b. Both expressions should be of the same convertible data type; see Short-Circuiting for error-checking details

NVL2(expr, value_if_not_null, value_if_null)

Evaluates expr: if expr does not return a null, value_if_not_null is returned. If expr does return a null, value_if_null is returned. Both value_if_not_null and value_if_null should be of the same data type as expr or implicitly convertible; see Short-Circuiting for error-checking details

REMOVE_NULLABLE(expr)

Alias for ZEROIFNULL

ZEROIFNULL(expr)

Replaces null values with appropriate values based on the column type (e.g., 0 if numeric column, an empty string if string column, etc.). Also removes the nullable column property if used to calculate a derived column.

Record Distribution Functions

Record distribution functions provide a means to locate any database record within the memory processing hierarchy; and through aggregating the results, determine the distribution of records across the cluster.

KI_CHUNK()

Returns the index of the chunk within the rank/TOM containing this record. This value may change as data is added or deleted from a table.

KI_CHUNK_ID()

Returns the ID of the chunk within the rank/TOM containing this record. This value should remain the same even after system restarts.

KI_CHUNK_MAX(col)

Returns the maximum value for the specified column in its chunk. If the data has changed, this value may be higher than any actual values.

KI_CHUNK_MIN(col)

Returns the minimum value for the specified column in its chunk. If the data has changed, this value may be lower than any actual values.

KI_CHUNK_OFFSET()

Returns the index of the record within the chunk.

KI_PARTITION()

Returns the index of the partition within the rank/TOM containing this record, for partitioned tables.

KI_RANK()

Returns the index of the rank containing this record.

KI_TOM()

Returns the index of the TOM within the rank containing this record.

String Functions

There are two different types of string functions:

String columns are stored as byte arrays, but allow multi-byte characters, as they are UTF-8 encoded. Some functions may behave in unexpected ways when given multi-byte input.

Scalar Functions

ASCII(expr)

Returns the ASCII code for the first byte in expr

BIN(expr[, minimum_digits])

Convert the value represented by expr into a binary string representation. Use optional minimum_digits to add leading 0s when needed; defaults to 1.Examples:

Function Call	Result
`BIN(42)`	`‘101010’`
`BIN(42, 8)`	`‘00101010’`

CHAR(expr)

The character represented by the standard ASCII code expr in the range [ 0 - 127 ]

CONCAT(expr_a, expr_b)

Performs a string concatenation of expr_a & expr_b; use nested CONCAT calls to concatenate more than two strings

The resulting field size of any CONCAT will be a string field big enough to hold the concatenated fields, e.g., concatenating a char32 column and a char64 column will result in a char128 column. Concatenations longer than 256 characters will use an unrestricted-width string type.

CONCAT_TRUNCATE(expr_a, expr_b)

Returns the concatenation of expr_a and expr_b, truncated at the maximum size of the larger of expr_a and expr_b. For data columns, the size is explicit; for string constants, the size will be the smallest charN type that can hold the constant string.

CONCAT_TRUNCATE will not work if any parameter is an unrestricted-width string.

Examples:

Function Call	Result
`CONCAT_TRUNCATE(‘ABC123’,’!’)`	`ABC123!`
`CONCAT_TRUNCATE(‘AB’,‘CDE’)`	`ABCD` (char4 is the minimum size required to hold the `CDE` value, so the result is truncated at 4 characters)
`CONCAT_TRUNCATE(‘ABCD1234’,‘DEFG’)`	`ABCD1234` (an 8-character string is the minimum size required to hold the `ABCD1234` value, so no characters can be appended)

CONTAINS(match, expr)

Returns 1 if expr contains match by string-literal comparison; otherwise, returns 0

CONV(expr, from_base, to_base)

Convert the value represented by the expr string from one numeric base to another. Base parameters may be [2 - 36]. Binary, octal and hexadecimal strings may have an optional prefix.Examples:

Function Call	Result
`CONV(‘42’, 10, 16)`	`‘2A’`
`CONV(‘2a’, 16, 10)`	`‘42’`
`CONV(‘11’, 2, 10)`	`‘3’`
`CONV(‘0o52’, 8, 2)`	`‘101010’`

DEC2HEX(expr[, minimum_digits])

Alias for HEX

DIFFERENCE(expr_a, expr_b)

Returns a value between 0 and 4 that represents the difference between the sounds of expr_a and expr_b based on the SOUNDEX() value of the strings—a value of 4 is the best possible sound match

EDIT_DISTANCE(expr_a, expr_b)

Returns the Levenshtein edit distance between expr_a and expr_b; the lower the value, the more similar the two strings are

ENDS_WITH(match, expr)

Returns 1 if expr ends with match by string-literal comparison; otherwise, returns 0

FROM_HEX(expr)

Alias for UNHEX

HEX(expr[, minimum_digits])

Convert the value represented by expr into a hexadecimal string representation. Use optional minimum_digits to add leading 0s when needed; defaults to 1.Examples:

Function Call	Result
`HEX(42)`	`‘2A’`
`HEX(42, 8)`	`‘0000002A’`

HEX2DEC(expr)

Alias for UNHEX

ILIKE(expr, match[, esc_char])

Returns whether expr matches the given match. The match is a string literal one with the following exceptions:

% matches any string of 0 or more characters
_ matches any single character
[character_set] matches any single character listed in the set. The character_set may contain ranges using a dash (-). A A character_set starting with ^ will match any character not in the specified set.

By default, the escape character used to match special characters, such as %, in the expr literally is \. The esc_char, if given, will override this default escape character.ILIKE is case-insensitive. For a case-sensitive match, see LIKE.

The match expression has to match the reference expression completely—it will not return true for partial matches. Add % before and/or after the match for partial matches.

Examples of successful matches:

Function Call
`ILIKE('Brook', 'BROOK')`
`ILIKE('Brooke', 'BROOK_')`
`ILIKE('Brooklyn', 'BROOK%')`
`ILIKE('50_50', '50\\_50')`
`ILIKE('100%', '100\\%')`
`ILIKE('50\\50', '50\\\\50')`
`ILIKE('abqd', 'AB[A-Z]D')`
`ILIKE('abqd', 'AB[^0-9]D')`
`ILIKE('ab-d', 'AB[123~-]D', '~')`
`ILIKE('a_c', 'A~_C', '~')`
`ILIKE('100%', '100~%', '~')`

INITCAP(expr)

Returns expr with the first letter of each word in uppercase

IPV4_PART(expr, part_num)

Returns the octet of the IP address given in expr at the position specified by part_num. Valid part_num values are constants from 1 to 4.Examples:

Function Call	Result
`IPV4_PART(‘12.34.56.78’, 1)`	`12`
`IPV4_PART(‘12.34.56.78’, 4)`	`78`

IS_IPV4(expr)

Returns 1 if expr is an IPV4 address; returns 0 otherwise

ISIPV4(expr)

Alias for IS_IPV4

LCASE(expr)

Converts expr to lowercase

LEFT(expr, num_bytes)

Returns the leftmost num_bytes bytes from expr

LEN(expr)

Alias for LENGTH

LENGTH(expr)

Returns the number of characters in expr

LIKE(expr, match[, esc_char])

Returns whether expr matches the given match. The match is a string literal one with the following exceptions:

% matches any string of 0 or more characters
_ matches any single character
[character_set] matches any single character listed in the set. The character_set may contain ranges using a dash (-). A A character_set starting with ^ will match any character not in the specified set.

By default, the escape character used to match special characters, such as %, in the expr literally is \. The esc_char, if given, will override this default escape character.LIKE is case-sensitive. For a case-insensitive match, see ILIKE.

The match expression has to match the reference expression completely—it will not return true for partial matches. Add % before and/or after the match for partial matches.

Examples of successful matches:

Function Call
`LIKE('Brook', 'Brook')`
`LIKE('Brooke', 'Brook_')`
`LIKE('Brooklyn', 'Brook%')`
`LIKE('50_50', '50\\_50')`
`LIKE('100%', '100\\%')`
`LIKE('50\\50', '50\\\\50')`
`LIKE('abqd', 'ab[a-z]d')`
`LIKE('abqd', 'ab[^0-9]d')`
`LIKE('ab-d', 'ab[123~-]d', '~')`
`LIKE('a_c', 'a~_c', '~')`
`LIKE('100%', '100~%', '~')`

LOCATE(match, expr[, start_pos])

Returns the starting position of the first match of match in expr, starting from position 1 or start_pos (if specified). If match can’t be found or start_pos is outside the range of letters in expr, a 0 is returned.

LOWER(expr)

Alias for LCASE

LPAD(expr, length[, pad])

Left pads the given expr string with the pad string to the given length of bytes. If expr is longer than length, the return value is shortened to length bytes. If length is larger than 256, it will be truncated to 256 bytes. The default padding character is a space.

The use of multi-byte characters in this function may have unexpected results.

Examples:

Function Call	Result
`LPAD(‘test’, 9, ‘pad’)`	`padpatest`
`LPAD(‘test’, 3, ‘pad’)`	`tes`

LTRIM(expr)

Removes whitespace from the left side of expr

OCT(expr[, minimum_digits])

Convert the value represented by expr into an octal (base 8) string representation. Use optional minimum_digits to add leading 0s when needed; defaults to 1.Examples:

Function Call	Result
`OCT(42)`	`‘52’`
`OCT(42, 8)`	`‘00000052’`

POSITION(match, expr[, start_pos])

Alias for LOCATE

REGEXP_COUNT (expr, regex[, position [, mode]])

Returns a count of the number of times the regex pattern is matched in expr. Matches do not overlap, so the start of a future match must start after the end of the previous match.The regex parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for regex. The escape character used to match wildcards in the expr literally is \. See REGEXP_LIKE for more regular expression examples.The optional position parameter specifies where to start searching in expr for the first match. The first character in the string has a position of 1 (the default).The optional mode parameter is a string which can be empty (the default) for the default behavior. See REGEXP_LIKE for the list of supported mode flags.Examples of REGEXP_COUNT:

Function Call	Result
`REGEXP_COUNT(‘abababab’, ‘abab’)`	2
`REGEXP_COUNT(‘abababab’, ‘abab’, 2)`	1
`REGEXP_COUNT(‘don”t’, ‘DON”T’, 1, ‘i’)`	1

REGEXP_INSTR (expr, regex [, position [, occurrence [, begin_end [, mode [,group]]]]])

Returns the starting position (1-based) in expr where a regex match is found. If no match is found, 0 is returned.The regex parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for regex. The escape character used to match wildcards in the expr literally is \. See REGEXP_LIKE for more regular expression examples.The optional position parameter specifies where to start searching in expr for the first match. The first character in the string has a position of 1 (the default).The optional occurrence parameter specifies which occurrence of the regex match is desired. For example, 2 would return the second occurrence of regex in expr. Matches do not overlap, so the start of a future match must start after the end of the previous match.The optional begin_end parameter specifies if the beginning or ending position is desired. Use 0 (the default) for the beginning of the match and use 1 for the position after the end of the match.The optional mode parameter is a string which can be empty (the default) for the default behavior. See REGEXP_LIKE for the list of supported mode flags.The optional group parameter specifies which regular expression group’s (i.e., parentheses inside regex) beginning/ending position to return. The default of 0 uses the entire matched expression, while a group of 1 through 9 corresponds to the 1st group up through the 9th group of the match.Examples of REGEXP_INSTR:

Function Call	Result
`REGEXP_INSTR(‘abcdefg’, ‘bc’)`	2
`REGEXP_INSTR(‘abcdefg’, ‘ab’, 2)`	0
`REGEXP_INSTR(‘abcabc’, ‘a’, 1, 2)`	4
`REGEXP_INSTR(‘abcabc’, ‘abc’, 1, 1, 1)`	4
`REGEXP_INSTR(‘abcabc’, ‘B C’, 1, 1, 0, ‘ix’)`	2
`REGEXP_INSTR(‘abcdefg’, ‘(C(.(.)))’, 1, 1, 0, ‘i’, 3)`	5

REGEXP_LIKE(expr, regex[, mode])

Returns whether expr matches the given regex. Generally, POSIX-compliant regular expressions are supported.The optional mode parameter is a string which can be empty (the default) for the default behavior. It can contain the following letters for the associated optional modified behaviors:

Letter	Meaning
`i`	Case-insensitive matches
`m`	Treat input as multiple lines so `^` and `$` match around newlines and not just the beginning and ending of the string
`n`	Allows `.` to also match a newline character
`x`	Ignore any whitespace (e.g., spaces) in `regex`

The escape character used to match wildcards in the expr literally is \.

The regex can match the expr partially. To perform full matches, ^ and $ can be used to match the start and end of expr, respectively.

Examples of successful matches:

Function Call	Match Type
`REGEXP_LIKE(‘partial’, ‘part’)`	Partial
`REGEXP_LIKE(‘Case’, ‘cAsE’, ‘i’)`	Case-insensitive
`REGEXP_LIKE(‘dot’, ‘d.t’)`	Any character
`REGEXP_LIKE(‘range’, ‘ra[a-z]ge’)`	Character range
`REGEXP_LIKE(‘zeroorone’, ‘z?zer(oor)?one’)`	0 or 1 token
`REGEXP_LIKE(‘zeroormore’, ‘zzer([om]or)e’)`	0 or more tokens
`REGEXP_LIKE(‘oneormore’, ‘o+n([em]or)+e’)`	1 or more tokens
`REGEXP_LIKE(‘A to Z’, ‘^A.*Z$‘)`	Begin/End
`REGEXP_LIKE(‘41ph4Num’, ’^[[:alnum:]]+$‘)`	Character class
`REGEXP_LIKE(‘Escape?’, ‘Escape?’)`	Escape wildcard

REGEXP_MATCH(expr, regex[, options])

Alias for REGEXP_LIKE

REGEXP_REPLACE (expr, regex [, replace [, position [, occurrence [, mode]]]])

Returns the expr string after replacing regex matches with the replace string parameter.The regex parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for regex. The escape character used to match wildcards in the expr literally is \. See REGEXP_LIKE for more regular expression examples.The replace parameter is the optional text with which to replace each match of regex in expr. The default is an empty string, which will simply remove the specified occurrences of regex in expr. A replace of \0 will insert the entire matched expression, while a replace of \1 through \9 will use the corresponding matched grouping (parentheses inside regex) as the replacement text.The optional position parameter specifies where to start searching in expr for the first match. The first character in the string has a position of 1 (the default).The optional occurrence parameter specifies which occurrence of the regex match to replace. For example, 2 would only replace the second occurrence of regex in expr. Matches do not overlap, so the start of a future match must start after the end of the previous match. Use 0 (the default) to replace all occurrences.The optional mode parameter is a string which can be empty (the default) for the default behavior. See REGEXP_LIKE for the list of supported mode flags.Examples of REGEXP_REPLACE:

Function Call	Result
`REGEXP_REPLACE(‘abc’, ‘b’)`	ac
`REGEXP_REPLACE(‘abc’, ‘b.’, ‘x’)`	ax
`REGEXP_REPLACE(‘abcd’, ’.’, ‘x’, 3, 1)`	abxd
`REGEXP_REPLACE(‘abcd’, ‘(b(.))’, ’-\2-’)`	a-c-d

REGEXP_SUBSTR (expr, regex [, position [, occurrence [, mode [,group]]]])

Returns the portion of the expr string that matched regex. An empty string is returned if no match is found.The regex parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for regex. The escape character used to match wildcards in the expr literally is \. See REGEXP_LIKE for more regular expression examples.The optional position parameter specifies where to start searching in expr for the first match. The first character in the string has a position of 1 (the default).The optional occurrence parameter specifies which occurrence of the regex match is desired. For example, 2 would return the second occurrence of regex in expr. Matches do not overlap, so the start of a future match must start after the end of the previous match.The optional mode parameter is a string which can be empty (the default) for the default behavior. See REGEXP_LIKE for the list of supported mode flags.The optional group parameter specifies which regular expression grouping (i.e., parentheses inside regex) to use. The default of 0 uses the entire matched expression, while a group of 1 through 9 corresponds to the 1st group up through the 9th group of the match.Examples of REGEXP_SUBSTR:

Function Call	Result
`REGEXP_SUBSTR(‘abcdefg’, ‘b.’)`	bc
`REGEXP_SUBSTR(‘abcadeafghij’, ‘a…’, 2, 1)`	adea
`REGEXP_SUBSTR(‘abcadeafghij’, ‘a…’, 1, 2)`	afgh
`REGEXP_SUBSTR(‘abcdefg’, ‘(C(.(.)))’, 1, 1, ‘i’, 3)`	e

REPLACE(expr, match, repl)

Replaces every occurrence of match in expr with repl

REPLACE_CHAR(expr, match, repl)

Replaces every occurrence of the single-byte character match in expr with the single-byte character repl

REPLACE_TRUNCATE(expr, match, repl)

Replaces every occurrence of match in expr with repl, and then truncates the resulting string at 256 bytes if it is longer than that

REPLACE_TRUNCATE will not work if any parameter is an unrestricted-width string.

The use of multi-byte characters in this function may have unexpected results.

REVERSE(expr)

Returns expr with the order of bytes reversed.

The use of multi-byte characters in this function may have unexpected results.

Examples:

Function Call	Result
`REVERSE(‘Reverse’)`	`esreveR`
`REVERSE(‘Was it a bat I saw?’)`	`?was I tab a ti saW`

RIGHT(expr, num_bytes)

Returns the rightmost num_bytes bytes from expr

RPAD(expr, length, pad)

Right pads the given expr string with the pad string to the given length of bytes. If expr is longer than length, the return value is shortened to length bytes. If length is larger than 256, it will be truncated to 256 bytes. The default padding character is a space.

The use of multi-byte characters in this function may have unexpected results.

Examples:

Function Call	Result
`RPAD(‘test’, 9, ‘pad’)`	`testpadpa`
`RPAD(‘test’, 3, ‘pad’)`	`tes`

RTRIM(expr)

Removes whitespace from the right side of expr

SOUNDEX(expr)

Returns a soundex value from expr. Only the first word in the string will be considered in the calculation.

This is the algorithm used by most programming languages.

SPACE(n)

Returns a string consisting of n space characters. The value of n can only be within the range of 0-256.

SPLIT(expr, delim, group_num)

Splits expr into groups delimited by the delim single-byte character and returns the group_num split group. If group_num is positive, groups will be counted from the beginning of expr; if negative, groups will be counted from the end of expr going backwards. Two consecutive delimiters will result in an empty string being added to the list of selectable groups. If no instances of delim exist in expr, the entire string is available at group 1 (and -1). Group 0 returns nothing.Examples:

Function Call	Result
`SPLIT(‘apple’, ‘p’, 1)`	`a`
`SPLIT(‘apple’, ‘p’, 2)`	<empty string>
`SPLIT(‘apple’, ‘p’, -1)`	`le`

STARTS_WITH(match, expr)

Returns 1 if expr starts with match by string-literal comparison; otherwise, returns 0

STRCMP(expr_a, expr_b)

Compares expr_a to expr_b in a lexicographical sort

Situation	Result
`expr_a` and `expr_b` are the same	`0`
`expr_a` comes before `expr_b`, lexicographically	`-1`
`expr_a` comes after `expr_b`, lexicographically	`1`

SUBSTR(expr, start_pos[, num_chars])

Alias for SUBSTRING

SUBSTRING(expr, start_pos[, num_bytes])

Returns num_bytes bytes from the expr, starting at the 1-based start_pos byte. If num_bytes is not specified, all bytes after start_pos will be returned.

The use of multi-byte characters in this function may have unexpected results.

Examples:

Function Call	Result
`SUBSTRING(‘banana’, 3)`	`nana`
`SUBSTRING(‘banana’, 3, 2)`	`na`

TO_HEX(expr[, minimum_digits])

Alias for HEX

TRIM(expr)

Removes whitespace from both sides of expr

UCASE(expr)

Converts expr to uppercase

UNHEX(expr)

Convert the hexadecimal string expr into a (decimal) number.Examples:

Function Call	Result
`UNHEX(‘42’)`	`66`
`UNHEX(‘1A’)`	`26`
`UNHEX(‘1a’)`	`26`

UPPER(expr)

Alias for UCASE

Aggregate Functions

The following functions can be used on string columns within aggregations. These functions can be used to convert string column values into delimited lists of those values.

STRING_AGG(expr[, delim])

Combines column values within a group into a single delimited string of those values.The default delimiter is a comma.

STRING_AGG_DISTINCT(expr[, delim])

Combines column values within a group into a single delimited string of those values, removing any duplicates.The default delimiter is a comma.

User/Security Functions

CURRENT_SCHEMA()

Returns the default schema of the current user

CURRENT_USER()

Alias for USER

HASH(column[, seed])

Returns a non-negative integer representing an obfuscated version of column, using the given seed; default seed is 0

IS_MEMBER(role[, user/role])

Returns whether the current user (or the given user/role, if specified) has been assigned the given role, either directly or indirectly:

Situation	Result
Current user (or given `user/role`) has been granted `role`	`true`
Current user (or given `user/role`) has not been granted `role`	`false`
Role `role` does not exist	null

IS_ROLEMEMBER(role[, user/role])

Alias for IS_MEMBER

MASK(expr, start, length[, char])

Masks length bytes of expr, beginning at the byte position identified by start, with * characters (or the single-byte character specified in char):

The use of multi-byte characters in this function may have unexpected results.

Function Call	Result
`MASK(‘Characters’, 4, 5)`	`Cha*****rs`
`MASK(‘Characters’, 5, 2, ’#‘)`	`Char##ters`

NEW_UUID()

Returns a randomly-generated UUID

OBFUSCATE(column[, seed])

Alias for HASH

SHA256(expr)

Returns the hex digits of the SHA-256 hash of the given value expr as a char64 string.

SYSTEM_USER()

Alias for USER

USER()

Returns the username of the current user

Column Expressions

Many of the functions above accept expressions as inputs in place of column names for selecting data from tables e.g. /aggregate/minmax. Given below are some examples of column expressions:

(x + y)
(2 * col1) + col2

Filter Expressions

Data can be filtered with the use of filter expressions within many endpoints; e.g., /filter. These expressions may contain column expressions as well as tests for equality/inequality for selecting records from the database. A filter expression cannot contain aggregation functions and should evaluate to a logical value ( true or false ). When the result of an expression evaluation is a numerical value, the result is converted to a logical value as follows: 0 is considered false and any other value is considered as true. Some examples of filter expressions are given below:

(x > y)
(a != b) or (c = d)
(timestamp > 1456749296789) and (x <= 10.0)
ABS(timestamp - 1456749296789) < 60 * 60 * 1000
QUARTER(timestamp) = 1 and MOD(YEAR(timestamp), 4) = 0
msg_id == 'MSGID1'
(x = 5) and y in (10,20,30)

Aggregate Expressions

Some endpoints accept aggregation expressions as inputs for selecting data from tables, e.g., /aggregate/groupby. Such expressions can only contain aggregation functions and non-nested functions of aggregation functions. Aggregation functions include:

Lookup/Grouping Functions

ARG_MAX(agg_expr, ret_expr)

The value of ret_expr where agg_expr is the maximum value (e.g. ARG_MAX(cost, product_id) returns the product ID of the highest cost product). ARG_MAX(a, b) is equivalent to LAST(b, a).

ARG_MIN(agg_expr, ret_expr)

The value of ret_expr where agg_expr is the minimum value (e.g. ARG_MIN(cost, product_id) returns the product ID of the lowest cost product). ARG_MIN(a, b) is equivalent to FIRST(b, a).

FIRST(ret_expr, agg_expr)

The value of ret_expr where agg_expr is the minimum value (e.g. FIRST(product_id, cost) returns the product ID of the lowest cost product). FIRST(a, b) is equivalent to ARG_MIN(b, a).

GROUPING(expr)

Used primarily with Rollup, Cube, and Grouping Sets, to distinguish the source of null values in an aggregated result set, returns whether expr is part of the aggregation set used to calculate the values in a given result set row. Returns 0 if expr is part of the row’s aggregation set, 1 if expr is not (meaning that aggregation took place across all expr values).For example, in a ROLLUP(A) operation, there will be two potential rows with null in the result set for column A. One row will contain null values of A aggregated together, and the other will contain null, but be an aggregation over the entire table, irrespective of A values. In this case, GROUPING(A) will return 0 for the null values of A aggregated together (as well as all other grouped A values) and 1 for the row resulting from aggregating across all A values.

LAST(ret_expr, agg_expr)

The value of ret_expr where agg_expr is the maximum value (e.g. LAST(product_id, cost) returns the product ID of the highest cost product). LAST(a, b) is equivalent to ARG_MAX(b, a).

Short-Circuiting

The logical operators and & or implement a version of boolean short-circuiting where if either side of the expression encounters an error (divide-by-zero, etc.) but the other side evaluates to a value that alone would determine the overall value of the logical expression, the error will be ignored and the logical value returned:

If one side of an and evaluates to false and the other side evaluates to an error, false will be returned.
If one side of an or evaluates to true and the other side evaluates to an error, true will be returned.

In the normal case, both sides of either of these operators would be evaluated simultaneously. However, to allow these operators to be used to check for error conditions, if an error is encountered on either side but the other side is enough information to determine the overall value of the expression, the error will be ignored. This same behavior applies to the evaluation branches of the following functions:

IF
NVL
NVL2

It also applies to SQL conditional functions.

​Constants

​Operators

​Functions

​Array Functions

​Scalar Functions

​Aggregation/Transposition Functions

​Conditional Functions

​Conversion Functions

​Date/Time Conversion Codes

​Date/Time Functions

​Date/Time Base Functions

​Date/Time Complex Conversion Functions

​Geospatial/Geometry Functions

​Enhanced Performance Scalar Functions

​Scalar Functions

​Aggregation Functions

​Track Functions

​H3 Functions

​JSON Functions

​Scalar Functions

​Aggregation Functions

​Math Functions

​Scalar Functions

​Aggregate Functions

​Null Functions

​Record Distribution Functions

​String Functions

​Scalar Functions

​Aggregate Functions

​User/Security Functions

​Column Expressions

​Filter Expressions

​Aggregate Expressions

​Lookup/Grouping Functions

​Short-Circuiting

Constants

Operators

Functions

Array Functions

Scalar Functions

Aggregation/Transposition Functions

Conditional Functions

Conversion Functions

Date/Time Conversion Codes

Date/Time Functions

Date/Time Base Functions

Date/Time Complex Conversion Functions

Geospatial/Geometry Functions

Enhanced Performance Scalar Functions

Scalar Functions

Aggregation Functions

Track Functions

H3 Functions

JSON Functions

Scalar Functions

Aggregation Functions

Math Functions

Scalar Functions

Aggregate Functions

Null Functions

Record Distribution Functions

String Functions

Scalar Functions

Aggregate Functions

User/Security Functions

Column Expressions

Filter Expressions

Aggregate Expressions

Lookup/Grouping Functions

Short-Circuiting