# Scalar Functions in ksqlDB for Confluent Platform A scalar function takes one or more input values and returns a single output value, grouped below by category. ## Numeric functions ### `ABS` - Since: 0.1.0 ```sql ABS(col1) ``` Returns the absolute value of `col1`. ### `ACOS` - Since: 0.28.0 ```sql ACOS(col1) ``` Returns the inverse (arc) cosine of `col1`, in radians. Use the [DEGREES](#ksqldb-reference-scalar-functions-degrees) function to convert the output to degrees. This function returns `NaN` for any input outside [-1, 1]. ### `ASIN` - Since: 0.28.0 ```sql ASIN(col1) ``` Returns the inverse (arc) sine of `col1`, in radians. Use the [DEGREES](#ksqldb-reference-scalar-functions-degrees) function to convert the output to degrees. This function returns `NaN` for any input outside [-1, 1]. ### `AS_VALUE` - Since: 0.9.0 ```sql AS_VALUE(keyColumn)`` ``` Copies a row’s key column into the row’s value. Example : ```sql CREATE TABLE AGG AS SELECT ID, -- this is the grouping column, which is stored in the message key. AS_VALUE(ID) AS ID2 -- this creates a copy of ID, named ID2, which is stored in the message value. COUNT(*) AS COUNT FROM S GROUP BY ID; ``` ### `ATAN` - Since: 0.28.0 ```sql ATAN(col1) ``` Returns the inverse (arc) tangent of `col1`, in radians. Use the [DEGREES](#ksqldb-reference-scalar-functions-degrees) function to convert the output to degrees. ### `ATAN2` - Since: 0.28.0 ```sql ATAN2(y, x) ``` Returns the inverse (arc) tangent of `y / x`. This is equivalent to the angle *theta* when Cartesian coordinates (x, y) are converted to polar coordinates (*radius*, *theta*). The returned value is in radians. Use the [DEGREES](#ksqldb-reference-scalar-functions-degrees) function to convert the output to degrees. If `x` is zero, `y / x` is undefined, and this function returns the approximate value of a multiple of *π/2*. ### `CAST` - Since: 0.1.0 ```sql CAST(COL0 AS BIGINT) ``` Converts one type to another. The following casts are supported: | From | To | Notes | |----------------------------------------------|--------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | any except `BYTES` | `STRING` | Converts the type to its
string representation. | | `VARCHAR` | `BOOLEAN` | Any string that exactly
matches `true`,
case-insensitive, is
converted to `true`. Any
other value is converted
to `false`. | | `VARCHAR` | `INT`,
`BIGINT`,
`DECIMAL`,
`DOUBLE` | Converts string
representation of numbers
to number types.
Conversion will fail if
text does not contain a
number or the number does
not fit in the indicated
type. | | `VARCHAR` | `TIME` | Converts time strings to
`TIME`. Conversion fails
if text is not in
`HH:mm:ss` format. | | `VARCHAR` | `DATE` | Converts date strings to
`DATE`. Conversion fails
if text is not in
`yyyy-MM-dd` format. | | `VARCHAR` | `TIMESTAMP` | Converts datestrings to
`TIMESTAMP`. Conversion
fails if text is not in
ISO-8601 format. | | `TIMESTAMP` | `TIME`,
`DATE` | Converts a `TIMESTAMP`
to `TIME` or `DATE` by
extracting the time or
date portion of the
`TIMESTAMP`. | | `DATE` | `TIMESTAMP` | Converts a `DATE` to
`TIMESTAMP` by setting
the time portion to
`00:00:00.000` | | `INT`, `BIGINT`,
`DECIMAL`,
`DOUBLE` | `INT`,
`BIGINT`,
`DECIMAL`,
`DOUBLE` | Convert between numeric
types. Conversion can
result in rounding | | `ARRAY` | `ARRAY` | (Since 0.14) Convert
between arrays of
different element types | | `MAP` | `MAP` | (Since 0.14) Convert
between maps of different
key and value types | | `STRUCT` | `STRUCT` | (Since 0.14) Convert
between structs of
different field types.
Only fields that exist in
the target STRUCT type are
copied across. Any fields
in the target type that
don’t exist in the source
are set to `NULL`. Field
name matching is
case-sensitive. | ### `CBRT` - Since: 0.29.0 ```sql CBRT(col1) ``` Returns the cube root of `col1`. ### `CEIL` - Since: 0.1.0 ```sql CEIL(col1) ``` Returns the smallest integer value that’s greater than or equal to `col1`. ### `COS` Since: 0.28.0 ```sql COS(col1) ``` Returns the cosine of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. ### `COSH` - Since: 0.28.0 ```sql COSH(col1) ``` Returns the hyperbolic cosine of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. ### `COT` - Since: 0.28.0 ```sql COT(col1)`` ``` Returns the cotangent of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. This implementation returns a large value approaching positive or negative infinity near the asymptotes, because *2π* and similar values cannot be represented exactly. At *0*, it returns `Infinity` with the same sign as the input. ### `DEGREES` - Since: 0.28.0 ```sql DEGREES(col1) ``` Converts `col1` from radians to degrees. ### `ENTRIES` - Since: 0.6.0 ```sql ENTRIES(map MAP, sorted BOOLEAN) ``` Creates an array of structs from the entries in a map. Each struct has a field named `K` containing the key, which is a string, and a field named `V`, which holds the value. If `sorted` is true, the entries are sorted by key. ### `EXP` - Since: 0.6.0 ```sql EXP(col1) ``` Returns the exponential of `col1`, which is *e* raised to the power of `col1`. ### `FLOOR` - Since: 0.1.0 ```sql FLOOR(col1) ``` Returns the largest integer value that’s less than or equal to `col1`. ### `GENERATE_SERIES` - Since: 0.6.0 ```sql GENERATE_SERIES(start, end) GENERATE_SERIES(start, end, step) ``` Constructs an array of values between `start` and `end`, inclusive. Parameters `start` and `end` can be an `INT` or `BIGINT`. `step`, if supplied, specifies the step size. The step can be positive or negative. If not supplied, `step` defaults to `1`. Parameter `step` must be an `INT`. ### `GEO_DISTANCE` - Since: 0.6.0 ```sql GEO_DISTANCE(lat1, lon1, lat2, lon2, unit) ``` The great-circle distance between two lat-long points, both specified in decimal degrees. An optional final parameter specifies `KM` (the default) or `miles`. ### `GREATEST` - Since: 0.20.0 ```sql GREATEST(col1, col2, …) ``` Returns the largest non-null value from a variable number of comparable columns. If comparing columns of different numerical types, first use the [CAST](#ksqldb-reference-scalar-functions-cast) function to cast them to be of the same type. ### `LEAST` - Since: 0.20.0 ```sql LEAST(col1, col2, …) ``` Returns the smallest non-null value from a variable number of comparable columns. If comparing columns of different numerical types, first use the [CAST](#ksqldb-reference-scalar-functions-cast) function to cast them to be of the same type. ### `LN` - Since: 0.6.0 ```sql LN(col1) ``` Returns the natural logarithm of `col1`, which is . The value of `col1` must be greater than 0. ### `LOG` - Since: 0.29.0 ```sql LOG(value) LOG(base, value) ``` The single-parameter version of this method returns the base 10 logarithm of the `value`. The two-parameter version returns the logarithm with the given `base` of the `value`. This function returns `-Infinity` for any `base` when the `value` is `0`. It returns `NaN` when the `value` is negative, when the `base` is negative, when the `base` is `0`, or when the `base` is `1`. ### `PI` - Since: 0.28.0 ```sql PI() ``` Returns an approximate value of *π*. ### `POWER` - Since: 0.29.0 ```sql POWER(base, exponent)`` ``` Calculates the value of the `base` raised to the `exponent`. This function returns `Infinity` when the result overflows the `DOUBLE` type. ### `RADIANS` - Since: 0.28.0 ```sql RADIANS(col1) ``` Converts `col1` from degrees to radians. ### `RANDOM` - Since: 0.1.0 ```sql RANDOM() ``` Returns a random `DOUBLE` value between 0.0 and 1.0. ### `ROUND` - Since: 0.1.0 ```sql ROUND(col1) ROUND(col1, scale) ``` Rounds a value to the number of decimal places specified by `scale`. If `scale` is negative, the value is rounded to the right of the decimal point. Numbers equidistant to the nearest value are rounded up, in the positive direction. If the number of decimal places is not provided, it defaults to zero. ### `SIGN` - Since: 0.6.0 ```sql SIGN(col1) ``` Returns the sign of `col1` as an `INTEGER`: - -1 if the argument is negative - 0 if the argument is zero - 1 if the argument is positive - `null` argument is `null` ### `SIN` Since: 0.28.0 ```sql SIN(col1) ``` Returns the sine of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. ### `SINH` - Since: 0.28.0 ```sql SINH(col1) ``` Returns the hyperbolic sine of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. ### `SQRT` - Since: 0.6.0 ```sql SQRT(col1) ``` Returns the square root of `col`. ### `TAN` - Since: 0.28.0 ```sql TAN(col1) ``` Returns the tangent of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. This implementation returns a large value approaching positive or negative infinity near the asymptotes, because *π/2* and similar values cannot be represented exactly. ### `TANH` - Since: 0.28.0 ```sql TANH(col1) ``` Returns the hyperbolic tangent of `col1`. `col1` is in radians. Use the [RADIANS](#ksqldb-reference-scalar-functions-radians) function to convert the input to radians, if necessary. ### `TRUNC` - Since: 0.29.0 ```sql TRUNC(col1) TRUNC(col1, scale) ``` Truncates (rounds toward zero) a value to the number of decimal places specified by `scale`. If `scale` is negative, the value is truncated to the left of the decimal point. For example, `TRUNC(12345.67, -3)` returns `12000`. If the number of decimal places is not provided, it defaults to zero. ## Collections ### `ARRAY` - Since: 0.7.0 ```sql ARRAY[exp1, exp2, …] ``` Constructs an array from a variable number of inputs. All elements must be coercible to a common SQL type. For more information, see [Implicit Type Coercion](type-coercion.md#ksqldb-reference-casting-implicit-type-coercion). ### `ARRAY_CONCAT` - Since: 0.21.0 ```sql ARRAY_CONCAT(array1, array2) ``` Returns an array representing the concatenation of both input arrays. Returns `NULL` if both input arrays are `NULL`. If only one argument is `NULL`, the result is the other argument. Example : ```sql -- returns [1, 2, 3, 1, 2, 4, 1] SELECT ARRAY_CONCAT(ARRAY[1, 2, 3, 1, 2], [4, 1]);
-- returns ['apple', 'apple', NULL, 'cherry', 'cherry'] SELECT ARRAY_CONCAT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry']); ``` ### `ARRAY_CONTAINS` - Since: 0.6.0 ```sql ARRAY_CONTAINS(ARRAY[1, 2, 3], 3) ``` Given an array, checks if a search value is contained in the array. Accepts any `ARRAY` type. The type of the second param must match the element type of the `ARRAY`. ### `ARRAY_DISTINCT` - Since: 0.10.0 ```sql ARRAY_DISTINCT([1, 2, 3]) ``` Returns an array of all the distinct values, including `NULL` if present, from the input array. The output array elements are in order of their first occurrence in the input. Returns `NULL` if the input array is `NULL`. Examples : ```sql -- returns [1, 2, 3] SELECT ARRAY_DISTINCT(ARRAY[1, 1, 2, 3, 1, 2]);
-- returns ['apple', NULL, 'cherry'] SELECT ARRAY_DISTINCT(ARRAY['apple', 'apple', NULL, 'cherry']); ``` ### `ARRAY_EXCEPT` - Since: 0.10.0 ```sql ARRAY_EXCEPT(array1, array2) ``` Returns an array of all the distinct elements from an array, except for those also present in a second array. The order of entries in the first array is preserved but duplicates are removed. Returns `NULL` if either input is `NULL`. Examples : ```sql -- returns [1] SELECT ARRAY_EXCEPT(ARRAY[1, 2, 3, 1, 2], [2, 3]);
-- returns ['apple', NULL] SELECT ARRAY_EXCEPT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry']); ``` ### `ARRAY_INTERSECT` - Since: 0.10.0 ```sql ARRAY_INTERSECT(array1, array2) ``` Returns an array of all the distinct elements from the intersection of both input arrays. The order of entries in the output is the same as in the first input array. Returns `NULL` if either input array is `NULL`. Examples : ```sql -- returns [1, 2] SELECT ARRAY_INTERSECT(ARRAY[1, 2, 3, 1, 2], [2, 1]);
-- returns ['apple'] SELECT ARRAY_INTERSECT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['apple']); ``` ### `ARRAY_JOIN` - Since: 0.10.0 ```sql ARRAY_JOIN(col1, delimiter) ``` Creates a flat string representation of all the elements contained in an array. The elements in the resulting string are separated by the chosen `delimiter`, which is an optional parameter. The default is the comma character, `,`. Array elements are limited to primitive ksqlDB types only. ### `ARRAY_LENGTH` - Since: 0.8.0 ```sql ARRAY_LENGTH(ARRAY[1, 2, 3]) ``` Returns the number of elements in an array. If the supplied parameter is `NULL`, the method returns `NULL`. ### `ARRAY_MAX` - Since: 0.10.0 ```sql ARRAY_MAX(['foo', 'bar', 'baz']) ``` Returns the maximum value from an array of primitive elements. Arrays of other arrays, arrays of maps, arrays of structs, or combinations of these types aren’t supported. If the array field is `NULL`, or contains only `NULL` values, `NULL` is returned. Array entries are compared according to their natural sort order, which sorts the various data types as shown in the following examples. Examples : ```sql -- returns 2 SELECT ARRAY_MAX[-1, 2, NULL, 0];
-- returns true SELECT ARRAY_MAX[false, NULL, true];
-- returns 'baz' -- lower-case characters are "greater" than upper-case characters SELECT ARRAY_MAX['Foo', 'Bar', NULL, 'baz']; ``` ### `ARRAY_MIN` - Since: 0.10.0 ```sql ARRAY_MIN(['foo', 'bar', 'baz']) ``` Returns the minimum value from an array of primitive elements. Arrays of other arrays, arrays of maps, arrays of structs, or combinations of these types aren’t supported. If the array field is `NULL`, or contains only `NULL` values, `NULL` is returned. Array entries are compared according to their natural sort order, which sorts the various data types as shown in the following examples. Examples : ```sql -- returns -1 SELECT ARRAY_MIN[-1, 2, NULL, 0];
-- returns false SELECT ARRAY_MIN[false, NULL, true];
-- returns 'Bar' SELECT ARRAY_MIN['Foo', 'Bar', NULL, 'baz']; ``` ### `ARRAY_REMOVE` - Since: 0.11.0 ```sql ARRAY_REMOVE(array, element) ``` Removes all elements from `array` that are equal to `element`. If the `array` field is `NULL`, `NULL` is returned. Examples : ```sql -- returns [1, 3, 1] SELECT ARRAY_REMOVE([1, 2, 3, 2, 1], 2);
-- returns [NULL, true, true] SELECT ARRAY_REMOVE([false, NULL, true, true], false);
-- returns ['Foo', 'Bar', 'baz'] SELECT ARRAY_REMOVE(['Foo', 'Bar', NULL, 'baz'], null); ``` ### `ARRAY_SORT` - Since: 0.10.0 ```sql ARRAY_SORT(['foo', 'bar', 'baz'], 'ASC|DESC') ``` Given an array of primitive elements, returns an array of the same elements sorted according to their natural sort order. Arrays of other arrays, arrays of maps, arrays of structs, or combinations of these types aren’t supported. Any `NULL` values in the array are moved to the end. If the array field is `NULL`, `NULL` is returned. The optional second parameter specifies whether to sort the elements in ascending (`ASC`) or descending (`DESC`) order. If neither is specified, the default is ascending order. Examples : ```sql -- returns [-1, 0, 2, NULL] SELECT ARRAY_SORT[-1, 2, NULL, 0];
-- returns [false, true, NULL] SELECT ARRAY_SORT[false, NULL, true];
-- returns ['Bar', 'Foo', 'baz', NULL] SELECT ARRAY_SORT['Foo', 'Bar', NULL, 'baz']; ``` ### `ARRAY_UNION` - Since: 0.10.0 ```sql ARRAY_UNION(array1, array2) ``` Returns an array of all the distinct elements from both input arrays, in the order they’re encountered. Returns `NULL` if either input array is `NULL`. Examples : ```sql -- returns [1, 2, 3, 4] SELECT ARRAY_UNION(ARRAY[1, 2, 3, 1, 2], [4, 1]);
-- returns ['apple', NULL, 'cherry'] SELECT ARRAY_UNION(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry']); ``` ### `AS_MAP` - Since: 0.6.0 ```sql AS_MAP(keys, vals) ``` Constructs a map from a list of keys and a list of values. ### `ELT` - Since: 0.6.0 ```sql ELT(n INTEGER, args VARCHAR[]) ``` Returns element `n` in the `args` list of strings, or `NULL` if `n` is less than 1 or greater than the number of arguments. The `ELT` function is 1-indexed. `ELT` is the complement to the `FIELD` function. ### `FIELD` - Since: 0.6.0 ```sql FIELD(str VARCHAR, args VARCHAR[]) ``` Returns the 1-indexed position of `str` in `args`, or 0 if not found. If `str` is `NULL`, the return value is 0, because `NULL` isn’t considered to be equal to any value. `FIELD` is the complement to the `ELT` function. ### `JSON_ARRAY_CONTAINS` - Since: 0.6.0 ```sql JSON_ARRAY_CONTAINS('[1, 2, 3]', 3) ``` Given a `STRING` containing a JSON array, checks if a search value is contained in the array. Returns `false` if the first parameter doesn’t contain a JSON array. ### `MAP` - Since: 0.7.0 ```sql MAP(key VARCHAR := value, …) ``` Constructs a map from specific key-value tuples. All values must be coercible to a common SQL type. For more information, see [Implicit Type Coercion](type-coercion.md#ksqldb-reference-casting-implicit-type-coercion). ### `MAP_KEYS` - Since: 0.10.0 ```sql MAP_KEYS(a_map) ``` Returns an array that contains all keys from the specified map. Returns `NULL` if the input map is `NULL`. Example : ```sql -- returns ['apple', 'banana'] SELECT MAP_KEYS( MAP('apple' := 10, 'banana' := 20) ); ``` ### `MAP_VALUES` - Since: 0.10.0 ```sql MAP_VALUES(a_map) ``` Returns an array that contains all values from the specified map. Returns `NULL` if the input map is `NULL`. Example : ```sql -- returns [10, 20] SELECT MAP_VALUES( MAP('apple' := 10, 'banana' := 20) ); ``` ### `MAP_UNION` - Since: 0.10.0 ```sql MAP_UNION(map1, map2) ``` Returns a new map containing the union of all entries from both input maps. If a key is present in both input maps, the corresponding value from *map2* is returned. Returns `NULL` if all input maps are `NULL`. Examples : ```sql -- returns ['apple': 10, 'banana': 20, 'cherry': 99] SELECT MAP_UNION( MAP('apple' := 10, 'banana' := 20), MAP('cherry' := 99) );
-- returns ['apple': 50, 'banana': 20] SELECT MAP_UNION( MAP('apple' := 10, 'banana' := 20), MAP('apple' := 50) ); ``` ### `SLICE` - Since: 0.6.0 ```sql SLICE(col1, from, to) ``` Slices a list based on the supplied indices. The indices start at 1 and include both endpoints. ## Invocation functions Apply lambda functions to collections. ### `FILTER` - Since: 0.17.0 ```sql FILTER(array, x => …) FILTER(map, (k,v) => …) ``` Filters a collection with a lambda function. If the collection is an array, the lambda function must have one input argument. If the collection is a map, the lambda function must have two input arguments. ### `REDUCE` - Since: 0.17.0 ```sql REDUCE(array, state, (s, x) => …) REDUCE(map, state, (s, k, v) => …) ``` Reduces a collection starting from an initial state. If the collection is an array, the lambda function must have two input arguments. If the collection is a map, the lambda function must have three input arguments. If the state is `NULL`, the result is `NULL`. ### `TRANSFORM` - Since: 0.17.0 ```sql TRANSFORM(array, x => …) TRANSFORM(map, (k,v) => …, (k,v) => …) ``` Transforms a collection by using a lambda function. If the collection is an array, the lambda function must have one input argument. If the collection is a map, two lambda functions must be provided, and both lambdas must have two arguments: a map entry key and a map entry value. ## Strings ### `CHR` - Since: 0.10.0 ```sql CHR(decimal_code | utf_string) ``` Returns a single-character string representing the Unicode code-point described by the input. The input parameter can be either a decimal character code or a string representation of a UTF code. Returns `NULL` if the input is `NULL` or doesn’t represent a valid code-point. Commonly used to insert control characters such as `Tab` (9), `Line Feed` (10), or `Carriage Return` (13) into strings. Examples : ```sql -- returns 'K' SELECT CHR(75);
-- returns 'K' SELECT CHR('\u004b');
-- returns '好' SELECT CHR(22909);
-- returns '好' SELECT CHR('\u597d'); ``` ### `CONCAT` - Since: 0.1.0 ```sql CONCAT(col1, col2, 'hello', …, col-n) CONCAT(bytes1, bytes2, …, bytes-n) ``` Concatenates two or more string or bytes expressions. Any inputs which evaluate to `NULL` are replaced with an empty string or bytes in the output. ### `CONCAT_WS` - Since: 0.10.0 ```sql CONCAT_WS(separator, expr1, expr2, …) ``` Concatenates two or more string or bytes expressions, inserting a separator string or bytes between each. If the separator is `NULL`, this function returns `NULL`. Any expressions which evaluate to `NULL` are skipped. Example : ```sql -- returns 'apple, banana, date' SELECT CONCAT_WS(', ', 'apple', 'banana', NULL, 'date'); ``` ### `ENCODE` - Since: 0.10.0 ```sql ENCODE(col1, input_encoding, output_encoding) ``` Given a STRING that is encoded as `input_encoding`, encode it using the `output_encoding`. The accepted input and output encodings are: - `hex` - `utf8` - `ascii` - `base64` Throws an exception if the provided encodings are not supported. The following example encodes a `hex` representation of a string to a `utf8` representation. Example : ```sql ENCODE(string, 'hex', 'utf8') ``` ### `EXTRACTJSONFIELD` - Since: 0.11.0 ```sql EXTRACTJSONFIELD(message, '$.log.cloud') ``` Given a `STRING` that contains JSON data, extracts the value at the specified [JSONPath](https://jsonpath.com/). For example, given a STRING containing the following JSON: ```json { "log": { "cloud": "gcp836Csd", "app": "ksProcessor", "instance": 4 } } ``` `EXTRACTJSONFIELD(message, '$.log.cloud')` returns the STRING `gcp836Csd`. If the requested JSONPath does not exist, the function returns `NULL`. The result of `EXTRACTJSONFIELD` is always a `STRING`. Use `CAST` to convert the result to another type. For example, `CAST(EXTRACTJSONFIELD(message, '$.log.instance') AS INT)` extracts the instance number from the previous JSON object as a `INT`. The return type of `EXTRACTJSONFIELD` is `STRING`, so JSONPaths that select multiple elements, like those containing wildcards, aren’t supported. #### NOTE `EXTRACTJSONFIELD` is useful for extracting data from JSON when either the schema of the JSON data isn’t static or the JSON data is embedded in a row that’s encoded using a different format, for example, a JSON field within an Avro-encoded message. If the whole row is encoded as JSON with a known schema or structure, use the JSON format and define the structure as the source’s columns. For example, a stream of JSON objects similar to the previous example could be defined using a statement similar to the following: ```sql CREATE STREAM LOGS (LOG STRUCT, …) WITH (VALUE_FORMAT='JSON', …) ``` ### `FROM_BYTES` - Since: 0.21.0 ```sql FROM_BYTES(bytes, encoding) ``` Converts a `BYTES` column to a `STRING` in the specified encoding type. The following list shows the supported encoding types. - `hex` - `utf8` - `ascii` - `base64` ### `IS_JSON_STRING` - Since: 0.24.0 ```sql -- returns a Boolean IS_JSON_STRING(json_string) ``` Returns `true` if `json_string` can be parsed as a valid JSON value; otherwise, `false` . Examples : ```sql -- returns true SELECT IS_JSON_STRING('[1, 2, 3]');
-- returns true SELECT IS_JSON_STRING('{}');
-- returns true SELECT IS_JSON_STRING('1');
-- returns true SELECT IS_JSON_STRING('\"abc\"');
-- returns true SELECT IS_JSON_STRING('null');
-- returns false SELECT IS_JSON_STRING('');
-- returns false SELECT IS_JSON_STRING('abc');
-- returns false SELECT IS_JSON_STRING(NULL); ``` ### `JSON_ARRAY_LENGTH` - Since: 0.24.0 ```sql -- returns an Integer or NULL JSON_ARRAY_LENGTH(json_string) ``` Parses `json_string` as a JSON value and returns the length of the top-level array. Returns `NULL` if the string can’t be interpreted as a JSON array, for example, when the string is `NULL` or it doesn’t contain valid JSON, or the JSON value is not an array. Examples : ```sql -- returns 3 SELECT JSON_ARRAY_LENGTH('[1, 2, 3]');
-- returns 3 SELECT JSON_ARRAY_LENGTH('[1, [1, [2]], 3]');
-- returns 0 SELECT JSON_ARRAY_LENGTH('[]');
-- returns NULL SELECT JSON_ARRAY_LENGTH('{}');
-- returns NULL SELECT JSON_ARRAY_LENGTH('123');
-- returns NULL SELECT JSON_ARRAY_LENGTH(NULL);
-- returns NULL and logs an "Invalid JSON format" exception in the server log SELECT JSON_ARRAY_LENGTH('abc'); ``` ### `JSON_CONCAT` - Since: 0.24.0 ```sql -- returns a String JSON_CONCAT(json_string1, json_string2, ...) ``` Given N strings, parses them as JSON values and returns a string representing their concatenation. Concatenation rules are identical to PostgreSQL’s [|| operator](https://www.postgresql.org/docs/14/functions-json.html): - If all strings deserialize into JSON objects, return an object with a union of the input keys. If there are duplicate objects, take values from the last object. - If all strings deserialize into JSON arrays, return the result of array concatenation. - If at least one of the deserialized values is not an object, convert non-array inputs to a single-element array and return the result of array concatenation. - If at least one of the input strings is `NULL` or can’t be deserialized as JSON, return `NULL`. Similar to PostgreSQL’s `||` operator, this function merges only top-level object keys or arrays. Examples : ```sql -- returns '{"a":1,"b":2}' SELECT JSON_CONCAT('{\"a\": 1}', '{\"b\": 2}');
-- returns '{"a":{"3":4}}' SELECT JSON_CONCAT('{\"a\": {\"5\": 6}}', '{\"a\": {\"3\": 4}}');
-- returns '{}' SELECT JSON_CONCAT('{}', '{}');
-- returns '[1,2,3,4]' SELECT JSON_CONCAT('[1, 2]', '[3, 4]');
-- returns '[ 1, [2], [[3]], [[[4]]] ]' SELECT JSON_CONCAT('[1, [2]]', '[[[3]], [[[4]]]]');
-- returns '[null, null]' SELECT JSON_CONCAT('null', 'null');
-- returns '[1,2,{"a":1}]' SELECT JSON_CONCAT('[1, 2]', '{\"a\": 1}');
-- returns '[1, 2, 3]' SELECT JSON_CONCAT('[1, 2]', '3');
-- returns '[1, 2]' SELECT JSON_CONCAT('1', '2');
-- returns '[]' SELECT JSON_CONCAT('[]', '[]');
-- returns NULL SELECT JSON_CONCAT('abc', '[1]');
-- returns NULL SELECT JSON_CONCAT(NULL, '[1]'); ``` ### `JSON_KEYS` - Since: 0.24.0 ```sql -- returns Array JSON_KEYS(json_string) ``` Parses `json_string` as a JSON object and returns an array of strings representing the top-level keys. Returns `NULL` if the string can’t be interpreted as a JSON object, for example, when the string is `NULL` or it does not contain valid JSON, or the JSON value is not an object. Examples : ```sql -- returns ['a', 'b', 'd'] SELECT JSON_KEYS('{\"a\": \"abc\", \"b\": { \"c\": \"a\" }, \"d\": 1}');
-- returns [] SELECT JSON_KEYS('{}');
-- returns NULL SELECT JSON_KEYS('[]');
-- returns NULL SELECT JSON_KEYS('123') => NULL JSON_KEYS(NULL);
-- returns NULL SELECT JSON_KEYS(''); ``` ### `JSON_RECORDS` - Since: 0.24.0 ```sql -- returns Map JSON_RECORDS(json_string) ``` Parses `json_string` as a JSON object and returns a map representing the top-level keys and values. Returns `NULL` if the string can’t be interpreted as a JSON object, for example, when the string is `NULL` or it does not contain valid JSON, or the JSON value is not an object. Examples : ```sql -- returns {d=1, a="abc", b={"c":"a"}} SELECT JSON_RECORDS('{\"a\": \"abc\", \"b\": { \"c\": \"a\" }, \"d\": 1}');
-- returns {} SELECT JSON_RECORDS('{}');
-- returns NULL SELECT JSON_RECORDS('[]');
-- returns NULL SELECT JSON_RECORDS('123');
-- returns NULL SELECT JSON_RECORDS(NULL);
-- returns NULL SELECT JSON_RECORDS('abc'); ``` ### `JSON_ITEMS` - Since: 0.29.0 ```sql -- returns Array JSON_ITEMS(json_string) ``` Given a string with JSON array, converts it to a ksqlDB array of JSON strings. Returns `NULL` if the string can’t be interpreted as a JSON array, for example, when the string is `NULL` or it does not contain valid JSON, or the JSON value is not an array. Examples : ```sql -- returns ["{\"type\": \"A\", \"ts\": \"2022-01-27\"}", "{\"type\": \"B\", \"ts\": \"2022-05-18\"}"] SELECT JSON_ITEMS('[{\"type\": \"A\", \"ts\": \"2022-01-27\"}, {\"type\": \"B\", \"ts\": \"2022-05-18\"}]');
-- returns [] SELECT JSON_ITEMS('[]');
-- returns ["1","2","3"] SELECT JSON_ITEMS('[1, 2, 3]');
-- returns NULL SELECT JSON_ITEMS(NULL);
-- returns NULL SELECT JSON_ITEMS('abc'); ``` ### `TO_JSON_STRING` - Since: 0.24.0 ```sql -- returns a String TO_JSON_STRING(val) ``` Given any ksqlDB type, returns the equivalent JSON string. Examples for primitives types : ```sql -- returns '1' SELECT TO_JSON_STRING(1);
-- returns '15.3' SELECT TO_JSON_STRING(15.3);
-- returns '"abc"' SELECT TO_JSON_STRING('abc');
-- returns 'true' SELECT TO_JSON_STRING(true);
-- returns '"2021-10-11"' SELECT TO_JSON_STRING(PARSE_DATE('2021-10-11', 'yyyy-MM-dd'));
-- returns '"13:25"' SELECT TO_JSON_STRING(PARSE_TIME('13:25', 'HH:mm'));
-- returns '"2021-06-30T12:18:39.446"' SELECT TO_JSON_STRING(PARSE_TIMESTAMP('2021-06-31 12:18:39.446', 'yyyy-MM-dd HH:mm:ss.SSS'));
-- returns 'null' SELECT TO_JSON_STRING(NULL); ``` Examples for compound types : ```sql -- returns '[1, 2, 3]' SELECT TO_JSON_STRING(Array[1, 2, 3]);
-- returns '{"ID":1,"NAME":"A"}' SELECT TO_JSON_STRING(Struct(id := 1, name := 'A'));
-- returns '{"c": 2, "d": 4}' SELECT TO_JSON_STRING(Map('c' := 2, 'd' := 4));
-- returns '[{"JSON_KEY": 1, "JSON_VALUE": {"c": 2, "d": 3}}]' SELECT TO_JSON_STRING(Array[Struct(json_key := 1, json_value := Map('c' := 2, 'd' := 3))]); ``` ### `INITCAP` - Since: 0.6.0 ```sql INITCAP(col1) ``` Capitalizes the first letter in each word and converts all other letters to lowercase. Words are delimited by whitespace. ### `INSTR` - Since: 0.10.0 ```sql INSTR(string, substring, [position], [occurrence]) ``` Returns the position of `substring` in `string`. The first character is at position 1. If `position` is provided, search starts from the specified position. A negative value for `position` causes the search to work from the end to the start of `string`. If `occurrence` is provided, the position of the *n*-th occurrence is returned. If `substring` is not found, the return value is 0. Examples : ```sql -- returns 2 SELECT INSTR('CORPORATE FLOOR', 'OR');
-- returns 5 SELECT INSTR('CORPORATE FLOOR', 'OR', 3);
-- returns 14 SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2);
-- returns 5 SELECT INSTR('CORPORATE FLOOR', 'OR', -3);
-- returns 2 SELECT INSTR('CORPORATE FLOOR', 'OR', -3, 2);
-- returns 0 SELECT INSTR('CORPORATE FLOOR', 'MISSING'); ``` ### `LCASE` - Since: 0.1.0 ```sql LCASE(col1) ``` Converts a string to lowercase. ### `LEN` - Since: 0.1.0 ```sql LEN(string) LEN(bytes) ``` Returns the length of a `STRING` or the number of bytes in a `BYTES` value. ### `LPAD` - Since: 0.10.0 ```sql LPAD(input, length, padding) ``` Pads the input string or bytes, beginning from the left, with the specified padding of the same type, until the target length is reached. If the input is longer than `length`, it is truncated. If the padding string or byte array is empty or `NULL`, or the target length is negative, `NULL` is returned. Examples : ```sql -- returns 'BarBFoo' SELECT LPAD('Foo', 7, 'Bar');
-- returns 'Fo' SELECT LPAD('Foo', 2, 'Bar');
-- returns 'Ba' SELECT LPAD('', 2, 'Bar');
-- returns '00123' SELECT LPAD('123', 5, '0'); ``` ### `MASK` - Since: 0.6.0 ```sql MASK(col1, 'X', 'x', 'n', '-') ``` Convert a string to a masked or obfuscated version of itself. The optional arguments following the input string to be masked are the characters to be substituted for upper-case, lower-case, numeric, and other characters of the input, respectively. If the mask characters are omitted, the default values are applied, as shown in the following example. ```sql -- returns "Xx-Xxxx--nnn" MASK("My Test $123"); ``` Set a given mask character to `NULL` to prevent any masking of that character type. ```sql -- returns "*y *est $111" MASK("My Test $123", '*', NULL, '1', NULL); ``` ### `MASK_KEEP_LEFT` - Since: 0.6.0 ```sql MASK_KEEP_LEFT(col1, numChars, 'X', 'x', 'n', '-') ``` Similar to the `MASK` function, except that the first or left-most `numChars` characters aren’t masked in any way. Example : ```sql -- returns "My Txxx--nnn" SELECT MASK_KEEP_LEFT("My Test $123", 4); ``` ### `MASK_KEEP_RIGHT` - Since: 0.6.0 ```sql MASK_KEEP_RIGHT(col1, numChars, 'X', 'x', 'n', '-') ``` Similar to the `MASK` function, except that the last or right-most `numChars` characters aren’t masked in any way. Example : ```sql -- returns "Xx-Xxxx-$123" SELECT MASK_KEEP_RIGHT("My Test $123", 4); ``` ### `MASK_LEFT` - Since: 0.6.0 ```sql MASK_LEFT(col1, numChars, 'X', 'x', 'n', '-') ``` Similar to the `MASK` function, except that only the first or left-most `numChars` characters have any masking applied to them. Example : ```sql -- returns "Xx-Xest $123" SELECT MASK_LEFT("My Test $123", 4); ``` ### `MASK_RIGHT` - Since: 0.6.0 ```sql MASK_RIGHT(col1, numChars, 'X', 'x', 'n', '-') ``` Similar to the `MASK` function, except that only the last or right-most `numChars` characters have any masking applied to them. Example : ```sql -- returns "My Test -nnn" SELECT MASK_RIGHT("My Test $123", 4); ``` ### `MD5` - Since: 0.29.0 ```sql MD5('hash me') MD5(col1) ``` Returns the hex-encoded MD5 hash of the given `STRING`. ### `REPLACE` - Since: 0.6.0 ```sql REPLACE(col1, 'foo', 'bar') ``` Replaces all instances of a substring in a string with a new string. ### `REGEXP_EXTRACT` - Since: 0.8.0 ```sql REGEXP_EXTRACT('.*', col1) REGEXP_EXTRACT('(([AEIOU]).)', col1, 2)`` ``` Extracts the first substring matched by the regular expression pattern from the input. You can specify a capturing group number to return that specific group. If a number isn’t specified, the entire substring is returned by default. Example : ```sql -- returns "there" SELECT REGEXP_EXTRACT("(.*) (.*)", 'hello there', 2); ``` ### `REGEXP_EXTRACT_ALL` - Since: 0.10.0 ```sql REGEXP_EXTRACT_ALL('.*', col1) REGEXP_EXTRACT_ALL('(([AEIOU]).)', col1, 2)`` ``` Extracts all substrings matched by the regular expression pattern from the input. You can specify a capturing group number to return that specific group. If a number isn’t specified, the entire substring is returned by default. Example : ```sql -- returns ["there", "day"] SELECT REGEXP_EXTRACT("(\\w+) (\\w+)", "hello there nice day", 2); ``` ### `REGEXP_REPLACE` - Since: 0.10.0 ```sql REGEXP_REPLACE(col1, 'a.b+', 'bar') ``` Replaces all matches of a regular expression in an input string with a new string. If either the input string, the regular expression, or the new string is `NULL`, the result is `NULL`. ### `REGEXP_SPLIT_TO_ARRAY` - Since: 0.10.0 ```sql REGEXP_SPLIT_TO_ARRAY(col1, 'a.b+') ``` Splits a string into an array of substrings based on a regular expression. If there is no match, the original string is returned as the only element in the array. If the regular expression is empty, all characters in the string are split. If either the string or the regular expression is `NULL`, a `NULL` value is returned. If the regular expression is found at the beginning or end of the string, or there are contiguous matches, an empty element is added to the array. ### `RPAD` - Since: 0.10.0 ```sql RPAD(input, length, padding) ``` Pads the input string or bytes, starting from the end, with the specified padding of the same type, until the target length is reached. If the input is longer than the specified target length, it is truncated. If the padding string or byte array is empty or `NULL`, or the target length is negative, `NULL` is returned. Examples : ```sql -- returns 'FooBarB' SELECT RPAD('Foo', 7, 'Bar');
-- returns 'Fo' SELECT RPAD('Foo', 2, 'Bar');
-- returns 'Ba' SELECT RPAD('', 2, 'Bar'); ``` ### `SPLIT` - Since: 0.6.0 ```sql SPLIT(col1, delimiter) ``` Splits a string into an array of substrings, or bytes into an array of subarrays, based on a delimiter. If the delimiter isn’t found, the original string or byte array is returned as the only element in the array. If the delimiter is empty, every character in the string or byte in the array is split. If the delimiter is found at the beginning or end of the string or bytes, or there are contiguous delimiters, an empty space is added to the array. Returns `NULL` if either parameter is `NULL`. ### `SPLIT_TO_MAP` - Since: 0.10.0 ```sql SPLIT_TO_MAP(input, entryDelimiter, kvDelimiter) ``` Splits a string into key-value pairs and creates a map from them. The `entryDelimiter` splits the string into key-value pairs which are then split by `kvDelimiter`. If the same key is present multiple times in the input, the latest value for the key is returned. Returns `NULL` if the input text is `NULL`. Returns `NULL` if either of the delimiters is `NULL` or an empty string. Example : ```sql -- returns { 'apple':'green', 'cherry':'red'} SELECT SPLIT_TO_MAP('apple':='green'/'cherry':='red', '/', ':='); ``` ### `SUBSTRING` Since: 0.1.0 ```sql SUBSTRING(str, pos, [len]) SUBSTRING(bytes, pos, [len]) ``` Returns the portion of `str` or `bytes` that starts at `pos` and has length `len`, or continues to the end of the string or bytes. The first character or byte is at position 1. Example : ```sql -- returns "stre" SUBSTRING("stream", 1, 4); ``` ### `TO_BYTES` - Since: 0.21.0 ```sql TO_BYTES(string, encoding) ``` Converts a `STRING` column in the specified encoding type to a `BYTES` column. The following list shows the supported encoding types. - `hex` - `utf8` - `ascii` - `base64` ### `TRIM` - Since: 0.1.0 ```sql TRIM(col1) ``` Removes the spaces from the beginning and end of a string. ### `UCASE` - Since: 0.1.0 ```sql UCASE(col1) ``` Converts a string to uppercase. ### `UUID` - Since: 0.10.0 ```sql UUID() UUID(bytes) ``` Creates a Universally Unique Identifier (UUID) generated according to RFC 4122. A call to UUID() returns a value conforming to UUID version 4, sometimes called “random UUID”, as described in RFC 4122. A call to UUID(bytes) returns a value conforming to UUID. The value is a 128-bit number represented as a string of five hexadecimal numbers, *aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee*, for example, `237e9877-e79b-12d4-a765-321741963000`. ## Bytes ### `BIGINT_FROM_BYTES` - Since: 0.23.1 ```sql BIGINT_FROM_BYTES(col1, [byteOrder]) ``` Converts a `BYTES` value to a `BIGINT` value according to the specified byte order. `BYTES` must be 8 bytes long, or a `NULL` value is returned. Byte order values must be `BIG_ENDIAN` or `LITTLE_ENDIAN`. If omitted, `BIG_ENDIAN` is used. A `NULL` value is returned if an invalid byte order value is provided. Example, where `b` is a `BYTES` value represented as a base64 string `AAAAASoF8gA=`: ```sql -- returns 5000000000 SELECT BIGINT_FROM_BYTES(b, 'BIG_ENDIAN'); ``` ### `DOUBLE_FROM_BYTES` - Since: 0.23.1 ```sql DOUBLE_FROM_BYTES(col1, [byteOrder]) ``` Converts a `BYTES` value to a `DOUBLE` value according to the specified byte order. `BYTES` must be 8 bytes long, or a `NULL` value is returned. Byte order values must be `BIG_ENDIAN` or `LITTLE_ENDIAN`. If omitted, `BIG_ENDIAN` is used. A `NULL` value is returned if an invalid byte order value is provided. Example, where `b` is a `BYTES` value represented as a base64 string `QICm/ZvJ9YI=`: ```sql -- returns 532.8738323 SELECT DOUBLE_FROM_BYTES(b, 'BIG_ENDIAN'); ``` ### `INT_FROM_BYTES` - Since: 0.23.1 ```sql INT_FROM_BYTES(col1, [byteOrder]) ``` Converts a `BYTES` value to an `INT` value according to the specified byte order. `BYTES` must be 4 bytes long, or a `NULL` value is returned. Byte order values must be `BIG_ENDIAN` or `LITTLE_ENDIAN`. If omitted, `BIG_ENDIAN` is used. A `NULL` value is returned if an invalid byte order value is provided. Example, where `b_big` is a `BYTES` value represented as a base64 string `AAAH5Q==`: ```sql -- returns 2021 SELECT INT_FROM_BYTES(b, 'BIG_ENDIAN'); ``` ### `TO_BYTES` - Since: 0.21.0 ```sql TO_BYTES(col1, encoding) ``` Converts a `STRING` value in the specified encoding to `BYTES`. The following list shows the supported encoding types. - `hex` - `utf8` - `ascii` - `base64` ## Nulls ### `COALESCE` - Since: 0.9.0 ```sql COALESCE(a, b, c, d) ``` Returns the first parameter that is not `NULL`. All parameters must be of the same type. If the parameter is a complex type, for example, `ARRAY` or `STRUCT`, the contents of the complex type are not inspected. The behaviour is the same: the first `NOT NULL` element is returned. ### `IFNULL` - Since: 0.9.0 ```sql IFNULL(expression, altValue) ``` If `expression` is `NULL`, returns `altValue`; otherwise, returns `expression`. If `expression` evaluates to a complex type, for example, `ARRAY` or `STRUCT`, the contents of the complex type are not inspected. ### `NULLIF` - Since: 0.19.0 ```sql NULLIF(expression1, expression2) ``` Returns `NULL` if `expression1` is equal to `expression2`; otherwise, returns `expression1`. If `expression` evaluates to a complex type, for example, `ARRAY` or `STRUCT`, the contents of the complex type are not inspected. ## Date and time ### `CONVERT_TZ` - Since: 0.17.0 ```sql CONVERT_TZ(col1, 'from_timezone', 'to_timezone') ``` Converts a `TIMESTAMP` value from `from_timezone` to `to_timezone`. The `from_timezone` and `to_timezone` parameters are `java.util.TimeZone` ID formats, for example: - “UTC” - “America/Los_Angeles” - “PDT” - “Europe/London” For more information on timestamp formats, see [DateTimeFormatter](https://cnfl.io/java-dtf). ### `DATEADD` - Since: 0.20.0 ```sql DATEADD(unit, interval, col0) ``` Adds an interval to a date. Intervals are defined by an integer value and a supported [time unit](../../reference/sql/time.md#ksqldb-reference-time-units). ### `DATESUB` - Since: 0.20.0 ```sql DATESUB(unit, interval, col0) ``` Subtracts an interval from a date. Intervals are defined by an integer value and a supported [time unit](../../reference/sql/time.md#ksqldb-reference-time-units). ### `FORMAT_DATE` - Since: 0.20.0 ```sql FORMAT_DATE(date, 'yyyy-MM-dd') ``` Converts a `DATE` value into a string that represents the date in the specified format. You can escape single-quote characters in the timestamp format by using two successive single quotes, `''`, for example: `'yyyy-MM-dd''T'''`. ### `FORMAT_TIME` - Since: 0.20.0 ```sql FORMAT_TIME(time, 'HH:mm:ss.SSS') ``` Converts a `TIME` value into the string representation of the time in the given format. You can escape single-quote characters in the time format by using two successive single quotes, `''`, for example: `'''T''HH:mm:ssX'`. For more information on time formats, see [DateTimeFormatter](https://cnfl.io/java-dtf). ### `FORMAT_TIMESTAMP` - Since: 0.17.0 ```sql FORMAT_TIMESTAMP(timestamp, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE]) ``` Converts a `TIMESTAMP` value into the string representation of the timestamp in the specified format. You can escape single-quote characters in the timestamp format by using two successive single quotes, `''`, for example: `'yyyy-MM-dd''T''HH:mm:ssX'`. The optional `TIMEZONE` parameter is a `java.util.TimeZone` ID format, for example: - “UTC” - “America/Los_Angeles” - “PDT” - “Europe/London” #### NOTE To use the `FORMAT_TIMESTAMP` function with a BIGINT millisecond timestamp parameter, convert the millisecond value to a `TIMESTAMP` by using the `FROM_UNIXTIME` function, for example: ```sql FORMAT_TIMESTAMP(FROM_UNIXTIME(unix_timestamp)) ``` For more information on timestamp formats, see [DateTimeFormatter](https://cnfl.io/java-dtf). ### `FROM_DAYS` - Since: 0.20.0 ```sql FROM_DAYS(days) ``` Converts an `INT` number of days since epoch to a `DATE` value. ### `FROM_UNIXTIME` - Since: 0.17.0 ```sql FROM_UNIXTIME(milliseconds) ``` Converts a `BIGINT` millisecond timestamp value into a `TIMESTAMP` value. ### `PARSE_DATE` - Since: 0.20.0 ```sql PARSE_DATE(col1, 'yyyy-MM-dd') ``` Converts a string representation of a date in the specified format into a `DATE` value. You can escape single-quote characters in the timestamp format by using two successive single quotes, `''`, for example: `'yyyy-MM-dd''T'''`. ### `PARSE_TIME` - Since: 0.20.0 ```sql PARSE_TIME(col1, 'HH:mm:ss.SSS') ``` Converts a string value in the specified format into a `TIME` value. You can escape single-quote characters in the time format by using successive single quotes, `''`, for example: `'''T''HH:mm:ssX'`. For more information on time formats, see [DateTimeFormatter](https://cnfl.io/java-dtf). ### `PARSE_TIMESTAMP` - Since: 0.17.0 ```sql PARSE_TIMESTAMP(col1, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE]) ``` Converts a string value in the given format into the `TIMESTAMP` value. You can escape single-quote characters in the timestamp format by using successive single quotes, `''`, for example: `'yyyy-MM-dd''T''HH:mm:ssX'`. The optional `TIMEZONE` parameter is a `java.util.TimeZone` ID format, for example: - “UTC” - “America/Los_Angeles” - “PDT” - “Europe/London” ### `TIMEADD` - Since: 0.20.0 ```sql TIMEADD(unit, interval, COL0) ``` Adds an interval to a `TIME`. Intervals are defined by an integer value and a supported [time unit](../../reference/sql/time.md#ksqldb-reference-time-units). ### `TIMESUB` - Since: 0.20.0 ```sql TIMESUB(unit, interval, COL0) ``` Subtracts an interval from a `TIME`. Intervals are defined by an integer value and a supported [time unit](../../reference/sql/time.md#ksqldb-reference-time-units). ### `TIMESTAMPADD` - Since: 0.17.0 ```sql TIMESTAMPADD(unit, interval, COL0) ``` Adds an interval to a `TIMESTAMP`. Intervals are defined by an integer value and a supported [time unit](../../reference/sql/time.md#ksqldb-reference-time-units). ### `TIMESTAMPSUB` - Since: 0.17.0 ```sql TIMESTAMPSUB(unit, interval, COL0) ``` Subtracts an interval from a `TIMESTAMP`. Intervals are defined by an integer value and a supported [time unit](../../reference/sql/time.md#ksqldb-reference-time-units). ### `UNIX_DATE` - Since: 0.6.0 ```sql UNIX_DATE([date]) ``` If `UNIX_DATE` is called with the date parameter, the function returns the `DATE` value as an `INTEGER` value representing the number of days since `1970-01-01`. If the `date` parameter is not provided, the function returns an integer representing days since `1970-01-01`. #### IMPORTANT The returned integer may differ depending on the local time of different ksqlDB Server instances. ### `UNIX_TIMESTAMP` - Since: 0.6.0 ```sql UNIX_TIMESTAMP([timestamp]) ``` If `UNIX_TIMESTAMP` is called with the timestamp parameter, the function returns the `TIMESTAMP` value as a `BIGINT` value representing the number of milliseconds since `1970-01-01T00:00:00 UTC`. If the `timestamp` parameter is not provided, the function returns the current UNIX timestamp in milliseconds, represented as a `BIGINT`. #### IMPORTANT The returned `BIGINT` may differ depending on the local time of different ksqlDB Server instances. ## URLs All ksqlDB URL functions assume URI syntax defined in [RFC 39386](https://tools.ietf.org/html/rfc3986). For more information on the structure of a URI, including definitions of the various components, see [Section 3 of the RFC](https://datatracker.ietf.org/doc/html/rfc3986#section-3). For encoding and decoding, ksqlDB uses the `application/x-www-form-urlencoded` convention. ### `URL_DECODE_PARAM` - Since: 0.6.0 ```sql URL_DECODE_PARAM(col1) ``` Unescapes the `URL-param-encoded` value in `col1`. This is the inverse of the `URL_ENCODE_PARAM` function. Example : ```sql -- returns "url encoded" SELECT URL_DECODE_PARAM("url%20encoded"); ``` ### `URL_ENCODE_PARAM` - Since: 0.6.0 ```sql URL_ENCODE_PARAM(col1) ``` Escapes the value of `col1` such that it can safely be used in URL query parameters. #### NOTE `URL_ENCODE_PARAM` is not the same as encoding a value for use in the path portion of a URL. Example : ```sql -- returns "url%20encoded" SELECT URL_ENCODE_PARAM("url encoded"); ``` ### `URL_EXTRACT_FRAGMENT` - Since: 0.6.0 ```sql URL_EXTRACT_FRAGMENT(url) ``` Extracts the fragment portion of the specified value. Returns `NULL` if `url` is not a valid URL or if the fragment doesn’t exist. All encoded values are decoded. Examples : ```sql -- returns "frag" SELECT URL_EXTRACT_FRAGMENT("http://test.com#frag");
-- returns "frag space" SELECT URL_EXTRACT_FRAGMENT("http://test.com#frag%20space"); ``` ### `URL_EXTRACT_HOST` - Since: 0.6.0 ```sql URL_EXTRACT_HOST(url) ``` Extracts the host-name portion of the specified value. Returns `NULL` if `url` is not a valid URI according to RFC-2396. Example : ```sql -- returns "test.com" SELECT URL_EXTRACT_HOST("http://test.com:8080/path"); ``` ### `URL_EXTRACT_PARAMETER` - Since: 0.6.0 ```sql URL_EXTRACT_PARAMETER(url, parameter_name) ``` Extracts the value of the requested parameter from the query-string of `url`. Returns `NULL` if the parameter is not present, has no value specified for it in the query string, or `url` is not a valid URI. The function encodes the parameter and decodes the output. To get all parameter values from a URL as a single string, use `URL_EXTRACT_QUERY.` Examples : ```sql -- returns "c d" SELECT URL_EXTRACT_PARAMETER("http://test.com?a%20b=c%20d", "a b");
-- returns "bar" SELECT URL_EXTRACT_PARAMETER("http://test.com?a=foo&b=bar", "b"); ``` ### `URL_EXTRACT_PATH` - Since: 0.6.0 ```sql URL_EXTRACT_PATH(url) ``` Extracts the path from `url`. Returns `NULL` if `url` is not a valid URI but returns an empty string if the path is empty. Examples : ```sql -- returns "path/to" SELECT URL_EXTRACT_PATH("http://test.com/path/to#a"); ``` ### `URL_EXTRACT_PORT` - Since: 0.6.0 ```sql URL_EXTRACT_PORT(url) ``` Extracts the port number from `url`. Returns `NULL` if `url` is not a valid URI or does not contain an explicit port number. Example : ```sql -- returns "8080" SELECT URL_EXTRACT_PORT("http://localhost:8080/path"); ``` ### `URL_EXTRACT_PROTOCOL` - Since: 0.6.0 ```sql URL_EXTRACT_PROTOCOL(url) ``` Extracts the protocol from `url`. Returns `NULL` if `url` is an invalid URI or has no protocol. Example : ```sql -- returns "http" SELECT URL_EXTRACT_PROTOCOL("http://test.com?a=foo&b=bar"); ``` ### `URL_EXTRACT_QUERY` - Since: 0.6.0 ```sql URL_EXTRACT_QUERY(url) ``` Extracts the decoded query-string portion of `url`. Returns `NULL` if no query-string is present or `url` is not a valid URI. Example : ```sql -- returns "a=foo bar&b=baz" SELECT URL_EXTRACT_QUERY("http://test.com?a=foo%20bar&b=baz"); ``` ## Deprecated ### `DATETOSTRING` - Since: 0.7.1 Deprecated since 0.20.0 (use FORMAT_DATE) ```sql DATETOSTRING(START_DATE, 'yyyy-MM-dd') ``` Converts an integer representation of a date into a string representing the date in the given format. Single quotes in the timestamp format can be escaped with two successive single quotes, `''`, for example: `'yyyy-MM-dd''T'''`. The integer represents days since epoch matching the encoding used by Connect dates. ### `STRINGTODATE` - Since: 0.7.1 Deprecated since 0.20.0 (use PARSE_DATE) ```sql STRINGTODATE(col1, 'yyyy-MM-dd') ``` Converts a string representation of a date in the given format into an integer representing days since epoch. Single quotes in the timestamp format can be escaped with two successive single quotes, `''`, for example: `'yyyy-MM-dd''T'''`. ### `STRINGTOTIMESTAMP` - Since: 0.7.1 Deprecated since 0.17.0 (use PARSE_TIMESTAMP) ```sql STRINGTOTIMESTAMP(col1, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE]) ``` Converts a string value in the given format into the BIGINT value that represents the millisecond timestamp. Single quotes in the timestamp format can be escaped with two successive single quotes, `''`, for example: `'yyyy-MM-dd''T''HH:mm:ssX'`. TIMEZONE is an optional parameter and it is a `java.util.TimeZone` ID format, for example: “UTC”, “America/Los_Angeles”, “PDT”, “Europe/London”. For more information on timestamp formats, see [DateTimeFormatter](https://cnfl.io/java-dtf). ### `TIMESTAMPTOSTRING` - Since: 0.7.1 Deprecated since 0.17.0 (use [FORMAT_TIMESTAMP](#format_timestamp)) ```sql TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE]) ``` Converts a BIGINT millisecond timestamp value into the string representation of the timestamp in the given format. Single quotes in the timestamp format can be escaped with two successive single quotes, `''`, for example: `'yyyy-MM-dd''T''HH:mm:ssX'`. TIMEZONE is an optional parameter, and it is a `java.util.TimeZone` ID format, for example, “UTC”, “America/Los_Angeles”, “PDT”, or “Europe/London”. For more information on timestamp formats, see [DateTimeFormatter](https://cnfl.io/java-dtf). #### NOTE To use the ``FORMAT_TIMESTAMP` <#format_timestamp>\`_\_ function with a BIGINT millisecond timestamp parameter, convert the millisecond value to a `TIMESTAMP` by using the `FROM_UNIXTIME` function, for example: ```sql FORMAT_TIMESTAMP(FROM_UNIXTIME(unix_timestamp)) ```