Skip to content

Scalar functions

Numeric functions

ABS

Since: -

1
ABS(col1)

The absolute value of a value.

AS_VALUE

Since: 0.9.0

1
AS_VALUE(keyCol)

Creates a copy of a key column in the value.

For example:

1
2
3
4
5
6
7
CREATE TABLE AGG AS
   SELECT 
     ID,                  -- this is the grouping column and will be stored in the message key.
     AS_VALUE(ID) AS ID2  -- this creates a copy of ID, called ID2, stored in the message value.
     COUNT(*) AS COUNT
   FROM S
   GROUP BY ID;

CAST

Since: -

1
CAST(COL0 AS BIGINT)

Converts one type to another. The following casts are supported:

from to notes
any 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.

CEIL

Since: -

1
CEIL(col1)

The ceiling of a value.

ENTRIES

Since: 0.6.0

1
ENTRIES(map MAP, sorted BOOLEAN)

Constructs 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

1
EXP(col1)

The exponential of a value.

FLOOR

Since: -

1
FLOOR(col1)

The floor of a value.

GENERATE_SERIES

Since: 0.6.0

1
GENERATE_SERIES(start, end)
1
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

1
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

1
GREATEST(col1, col2...)

The highest non-null value among a variable number of comparable columns. If comparing columns of different numerical types, use CAST to first cast them to be of the same type.

LEAST

Since: 0.20.0

1
LEAST(col1, col2...)

The highest non-null value among a variable number of comparable columns. If comparing columns of different numerical types, use CAST to first cast them to be of the same type.

LN

Since: 0.6.0

1
LN(col1)

The natural logarithm of a value.

RANDOM

Since: -

1
RANDOM()

Return a random DOUBLE value between 0.0 and 1.0.

ROUND

Since: 0.6.0

1
ROUND(col1)
1
ROUND(col1, scale)

Round a value to the number of decimal places as specified by scale to the right of the decimal point. If scale is negative then 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

1
SIGN(col1)

The sign of a numeric value 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

SQRT

Since: 0.6.0

1
SQRT(col1)

The square root of a value.

Collections

ARRAY

Since: 0.7.0

1
ARRAY[exp1, exp2, ...]

Construct an array from a variable number of inputs.

All elements must be coercible to a common Sql type.

ARRAY_CONTAINS

Since: 0.6.0

1
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

1
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:

1
2
ARRAY_DISTINCT(ARRAY[1, 1, 2, 3, 1, 2])  => [1, 2, 3]
ARRAY_DISTINCT(ARRAY['apple', 'apple', NULL, 'cherry'])  => ['apple', NULL, 'cherry']

ARRAY_EXCEPT

Since: 0.10.0

1
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:

1
2
ARRAY_EXCEPT(ARRAY[1, 2, 3, 1, 2], [2, 3])  => [1]
ARRAY_EXCEPT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry'])  => ['apple', NULL]

ARRAY_INTERSECT

Since: 0.10.0

1
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:

1
2
ARRAY_INTERSECT(ARRAY[1, 2, 3, 1, 2], [2, 1])  => [1, 2]
ARRAY_INTERSECT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['apple'])  => ['apple']

ARRAY_JOIN

Since: 0.10.0

1
ARRAY_JOIN(col1, delimiter)

Creates a flat string representation of all the elements contained in the given array. The elements in the resulting string are separated by the chosen delimiter, which is an optional parameter that falls back to a comma ,. The current implementation only allows for array elements of primitive ksqlDB types.

ARRAY_LENGTH

Since: 0.8.0

1
ARRAY_LENGTH(ARRAY[1, 2, 3])

Given an array, return the number of elements in the array.

If the supplied parameter is NULL the method returns NULL.

ARRAY_MAX

Since: 0.10.0

1
ARRAY_MAX(['foo', 'bar', 'baz'])

Returns the maximum value from within a given array of primitive elements (not arrays of other arrays, or maps, or structs, or combinations thereof).

Array entries are compared according to their natural sort order, which sorts the various data-types per the following examples: - array_max[-1, 2, NULL, 0] -> 2 - array_max[false, NULL, true] -> true - array_max['Foo', 'Bar', NULL, 'baz'] -> 'baz' (lower-case characters are "greater" than upper-case characters)

If the array field is NULL, or contains only NULLs, then NULL is returned.

ARRAY_MIN

Since: 0.10.0

1
ARRAY_MIN(['foo', 'bar', 'baz'])

Returns the minimum value from within a given array of primitive elements (not arrays of other arrays, or maps, or structs, or combinations thereof).

Array entries are compared according to their natural sort order, which sorts the various data-types per the following examples: - array_min[-1, 2, NULL, 0] -> -1 - array_min[false, NULL, true] -> false - array_min['Foo', 'Bar', NULL, 'baz'] -> 'Bar'

If the array field is NULL, or contains only NULLs, then NULL is returned.

ARRAY_REMOVE

Since: 0.11.0

1
ARRAY_REMOVE(array, element)

Removes all elements from the input array equal to element.

Examples:

1
2
3
- array_remove([1, 2, 3, 2, 1], 2) -> [1, 3, 1]
- array_remove([false, NULL, true, true], false) -> [NULL, true, true]
- array_remove(['Foo', 'Bar', NULL, 'baz'], null) -> ['Foo', 'Bar', 'baz']
If the array field is NULL then NULL is returned.

ARRAY_SORT

Since: 0.10.0

1
ARRAY_SORT(['foo', 'bar', 'baz'], 'ASC|DESC')

Given an array of primitive elements (not arrays of other arrays, or maps, or structs, or combinations thereof), returns an array of the same elements sorted according to their natural sort order. Any NULLs contained in the array will always be moved to the end.

For example: - array_sort[-1, 2, NULL, 0] -> [-1, 0, 2, NULL] - array_sort[false, NULL, true] -> [false, true, NULL] - array_sort['Foo', 'Bar', NULL, 'baz'] -> ['Bar', 'Foo', 'baz', NULL]

If the array field is NULL then NULL is returned.

An optional second parameter can be used to specify whether to sort the elements in 'ASC'ending or 'DESC'ending order. If neither is specified then the default is ascending order.

ARRAY_UNION

Since: 0.10.0

1
ARRAY_UNION(array1, array2)

Returns an array of all the distinct elements from both input arrays, in the order in which they are first encountered.

Returns NULL if either input array is NULL.

Examples:

1
2
ARRAY_UNION(ARRAY[1, 2, 3, 1, 2], [4, 1])  => [1, 2, 3, 4]
ARRAY_UNION(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry'])  => ['apple', NULL, 'cherry']

AS_MAP

Since: 0.6.0

1
AS_MAP(keys, vals)

Construct a map from a list of keys and a list of values.

ELT

Since: 0.6.0

1
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. This function is 1-indexed. ELT is the complement to FIELD.

FIELD

Since: 0.6.0

1
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 is not considered to be equal to any value. FIELD is the complement to ELT.

JSON_ARRAY_CONTAINS

Since: 0.6.0

1
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 does not contain a JSON array.

MAP

Since: 0.7.0

1
MAP(key VARCHAR := value, ...)

Construct a map from specific key-value tuples.

All values must be coercible to a common Sql type.

MAP_KEYS

Since: 0.10.0

1
MAP_KEYS(a_map)

Returns an array that contains all of the keys from the specified map.

Returns NULL if the input map is NULL.

Example:

1
map_keys( map('apple' := 10, 'banana' := 20) )  => ['apple', 'banana'] 

MAP_VALUES

Since: 0.10.0

1
MAP_VALUES(a_map)

Returns an array that contains all of the values from the specified map.

Returns NULL if the input map is NULL.

Example:

1
map_values( map('apple' := 10, 'banana' := 20) )  => [10, 20] 

MAP_UNION

Since: 0.10.0

1
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 of the input maps are NULL.

Example:

1
2
3
map_union( map('apple' := 10, 'banana' := 20), map('cherry' := 99) )  => ['apple': 10, 'banana': 20, 'cherry': 99] 

map_union( map('apple' := 10, 'banana' := 20), map('apple' := 50) )  => ['apple': 50, 'banana': 20] 

SLICE

Since: 0.6.0

1
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.

TRANSFORM

Since: 0.17.0

1
2
3
TRANSFORM(array, x => ...)

TRANSFORM(map, (k,v) => ..., (k,v) => ...)

Transform 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.

Reduce

Since: 0.17.0

1
2
3
REDUCE(array, state, (s, x) => ...)

REDUCE(map, state, (s, k, v) => ...)

Reduce 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.

Filter

Since: 0.17.0

1
2
3
FILTER(array, x => ...)

FILTER(map, (k,v) => ...)

Filter 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.

Strings

CHR

Since: 0.10.0

1
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 does not 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:

1
2
3
4
CHR(75)        => 'K'
CHR('\u004b')  => 'K'
CHR(22909)     => '好'
CHR('\u597d')  => '好'

CONCAT

Since: -

1
CONCAT(col1, col2, 'hello', ..., col-n)

Concatenate two or more string expressions. Any input strings which evaluate to NULL are replaced with empty string in the output.

CONCAT_WS

Since: 0.10.0

1
CONCAT_WS(separator, expr1, expr2, ...)

Concatenates two or more string expressions, inserting a separator string between each.

If the separator is NULL, this function returns NULL. Any expressions which evaluate to NULL are skipped.

Example:

1
CONCAT_WS(', ', 'apple', 'banana', NULL, 'date')  ->  'apple, banana, date'

ENCODE

Since: 0.10.0

1
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, and base64. Throws an exception if the provided encodings are not supported.

For example, to encode a string in hex to utf8, use ENCODE(string, 'hex', 'utf8').

EXTRACTJSONFIELD

Since: -

1
EXTRACTJSONFIELD(message, '$.log.cloud')

Given a STRING that contains JSON data, extract the value at the specified JSONPath.

For example, given a STRING containing the following JSON:

1
2
3
4
5
6
7
{
   "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) will extract the instance number from the above JSON object as a INT.

The return type of the UDF is STRING, so JSONPaths that select multiple elements, like those containing wildcards, aren't supported.

Note

EXTRACTJSONFIELD is useful for extracting data from JSON where either the schema of the JSON data is not static, or where the JSON data is embedded in a row 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 example above could be defined using a statement similar to this:

CREATE STREAM LOGS (LOG STRUCT<CLOUD STRING, APP STRING, INSTANCE INT>, ...) WITH (VALUE_FORMAT='JSON', ...)

INITCAP

Since: 0.6.0

1
INITCAP(col1)

Capitalize the first letter in each word and convert all other letters to lowercase. Words are delimited by whitespace.

INSTR

Since: 0.10.0

1
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. Negative position causes the search to work from end to start of string.

If occurrence is provided, the position of n-th occurrence is returned.

If substring is not found, the return value is 0.

Examples:

1
2
3
4
5
6
INSTR('CORPORATE FLOOR', 'OR') -> 2
INSTR('CORPORATE FLOOR', 'OR', 3) -> 5
INSTR('CORPORATE FLOOR', 'OR', 3, 2) -> 14
INSTR('CORPORATE FLOOR', 'OR', -3) -> 5
INSTR('CORPORATE FLOOR', 'OR', -3, 2) -> 2b
INSTR('CORPORATE FLOOR', 'MISSING') -> 0

LCASE

Since: -

1
LCASE(col1)

Convert a string to lowercase.

LEN

Since: -

1
LEN(col1)

The length of a string.

LPAD

Since: 0.10.0

1
LPAD(input, length, padding)

Pads the input string, beginning from the left, with the specified padding string, until the target length is reached. If the input string is longer than the specified target length, it is truncated.

If the padding string is empty or NULL, or the target length is negative, NULL is returned.

Examples:

1
2
3
4
LPAD('Foo', 7, 'Bar')  =>  'BarBFoo'
LPAD('Foo', 2, 'Bar')  =>  'Fo'
LPAD('', 2, 'Bar')  =>  'Ba'
LPAD('123', 5, '0')  => '00123'

MASK

Since: 0.6.0

1
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 then the default values, shown in the following example, are applied.

Set a given mask character to NULL to prevent any masking of that character type. For example: MASK("My Test $123") will return Xx-Xxxx--nnn, applying all default masks. MASK("My Test $123", '*', NULL, '1', NULL) will yield *y *est $111.

MASK_KEEP_LEFT

Since: 0.6.0

1
MASK_KEEP_LEFT(col1, numChars, 'X', 'x', 'n', '-')

Similar to the MASK function above, except that the first or left-most numChars characters will not be masked in any way. For example: MASK_KEEP_LEFT("My Test $123", 4) will return My Txxx--nnn.

MASK_KEEP_RIGHT

Since: 0.6.0

1
MASK_KEEP_RIGHT(col1, numChars, 'X', 'x', 'n', '-')

Similar to the MASK function above, except that the last or right-most numChars characters will not be masked in any way. For example:MASK_KEEP_RIGHT("My Test $123", 4) will return Xx-Xxxx-$123.

MASK_LEFT

Since: 0.6.0

1
MASK_LEFT(col1, numChars, 'X', 'x', 'n', '-')

Similar to the MASK function above, except that only the first or left-most numChars characters will have any masking applied to them. For example, MASK_LEFT("My Test $123", 4) will return Xx-Xest $123.

MASK_RIGHT

Since: 0.6.0

1
MASK_RIGHT(col1, numChars, 'X', 'x', 'n', '-')

Similar to the MASK function above, except that only the last or right-most numChars characters will have any masking applied to them. For example: MASK_RIGHT("My Test $123", 4) will return My Test -nnn.

REPLACE

Since: 0.6.0

1
REPLACE(col1, 'foo', 'bar')

Replace all instances of a substring in a string with a new string.

REGEXP_EXTRACT

Since: 0.8.0

1
REGEXP_EXTRACT('.*', col1)
1
REGEXP_EXTRACT('(([AEIOU]).)', col1, 2)

Extract the first subtring matched by the regex pattern from the input.

A capturing group number can also be specified in order to return that specific group. If a number isn't specified, the entire substring is returned by default.

For example, REGEXP_EXTRACT("(.*) (.*)", 'hello there', 2) returns "there".

REGEXP_EXTRACT_ALL

Since: 0.10.0

1
REGEXP_EXTRACT_ALL('.*', col1)
1
REGEXP_EXTRACT_ALL('(([AEIOU]).)', col1, 2)

Extract all subtrings matched by the regex pattern from the input.

A capturing group number can also be specified in order to return that specific group. If a number isn't specified, the entire substring is returned by default.

For example, REGEXP_EXTRACT("(\\w+) (\\w+)", 'hello there nice day', 2) returns ['there', 'day'].

REGEXP_REPLACE

Since: 0.10.0

1
REGEXP_REPLACE(col1, 'a.b+', 'bar')

Replace all matches of a regex in an input string with a new string. If either the input string, regular expression, or new string is null, the result is null.

REGEXP_SPLIT_TO_ARRAY

Since: 0.10.0

1
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, then 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, then an empty element is added to the array.

RPAD

Since: 0.10.0

1
RPAD(input, length, padding)

Pads the input string, starting from the end, with the specified padding string until the target length is reached. If the input string is longer than the specified target length it will be truncated.

If the padding string is empty or NULL, or the target length is negative, then NULL is returned.

Examples:

1
2
3
RPAD('Foo', 7, 'Bar')  =>  'FooBarB'
RPAD('Foo', 2, 'Bar')  =>  'Fo'
RPAD('', 2, 'Bar')  =>  'Ba'

SPLIT

Since: 0.6.0

1
SPLIT(col1, delimiter)

Splits a string into an array of substrings based on a delimiter. If the delimiter is not found, then the original string is returned as the only element in the array. If the delimiter is empty, then all characters in the string are split. If either, string or delimiter, are NULL, then a NULL value is returned.

If the delimiter is found at the beginning or end of the string, or there are contiguous delimiters, then an empty space is added to the array.

SPLIT_TO_MAP

Since: 0.10.0

1
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 that 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:

1
SPLIT_TO_MAP('apple':='green'/'cherry':='red', '/', ':=')  => { 'apple':'green', 'cherry':'red'}

SUBSTRING

Since: -

1
SUBSTRING(col1, 2, 5)
1
SUBSTRING(str, pos, [len])

Returns a substring of str that starts at pos (first character is at position 1) and has length len, or continues to the end of the string.

For example, SUBSTRING("stream", 1, 4) returns "stre".

TRIM

Since: -

1
TRIM(col1)

Trim the spaces from the beginning and end of a string.

UCASE

Since: -

1
UCASE(col1)

Convert a string to uppercase.

UUID

Since: 0.10.0

1
UUID()
Create 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. The value is a 128-bit number represented as a string of five hexadecimal numbers aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee.

Nulls

COALESCE

Since: 0.9.0

1
COALESCE(a, b, c, d)

Returns the first parameter that is not NULL. All parameters must be of the same type.

Where the parameter type 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: -

1
IFNULL(expression, altValue)

If the provided expression is NULL, returns altValue, otherwise, returns expression.

Where the parameter type is a complex type, for example ARRAY or STRUCT, the contents of the complex type are not inspected.

NULLIF

Since: -

1
NULLIF(expression1, expression2)

Returns NULL if expression1 is equal to expression2; otherwise, returns expression1.

If the parameter type is a complex type, for example, ARRAY or STRUCT, the contents of the complex type are not inspected.

Date and time

UNIX_DATE

Since: 0.6.0

1
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, it returns an integer representing days since 1970-01-01. The returned integer may differ depending on the local time of different ksqlDB Server instances.

UNIX_TIMESTAMP

Since: 0.6.0

1
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, it returns the current Unix timestamp in milliseconds, represented as a BIGINT. The returned timestamp may differ depending on the local time of different ksqlDB Server instances.

DATETOSTRING

Since: -

Deprecated since 0.20.0 (use FORMAT_DATE)

1
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: -

Deprecated since 0.20.0 (use PARSE_DATE)

1
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: -

Deprecated since 0.16.0 (use PARSE_TIMESTAMP)

1
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.

TIMESTAMPTOSTRING

Since: -

Deprecated since 0.16.0 (use FORMAT_TIMESTAMP)

1
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", "Europe/London". For more information on timestamp formats, see DateTimeFormatter.

FORMAT_TIMESTAMP

1
FORMAT_TIMESTAMP(timestamp, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])

Converts a 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", "Europe/London". For more information on timestamp formats, see DateTimeFormatter.

PARSE_TIMESTAMP

1
PARSE_TIMESTAMP(col1, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])

Converts a string value in the given format into the TIMESTAMP value. 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.

FORMAT_DATE

1
FORMAT_DATE(date, 'yyyy-MM-dd')

Converts a DATE value into a string that represents the date in the given format. You can escape single-quote characters in the timestamp format by using two successive single quotes, '', for example: 'yyyy-MM-dd''T'''.

PARSE_DATE

1
PARSE_DATE(col1, 'yyyy-MM-dd')

Converts a string representation of a date in the given 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'''.

FORMAT_TIME

Since: 0.20

1
FORMAT_TIME(time, 'HH:mm:ss.SSS')

Converts a TIME value into the string representation of the time in the given format. Single quotes in the time format can be escaped with two successive single quotes, '', for example: '''T''HH:mm:ssX'.

For more information on time formats, see DateTimeFormatter.

PARSE_TIME

Since: 0.20

1
PARSE_TIME(col1, 'HH:mm:ss.SSS')

Converts a string value in the given format into a TIME value. Single quotes in the time format can be escaped with two successive single quotes, '', for example: '''T''HH:mm:ssX'.

For more information on time formats, see DateTimeFormatter.

CONVERT_TZ

1
CONVERT_TZ(col1, 'from_timezone', 'to_timezone')

Converts a TIMESTAMP value from from_timezone to to_timezone. from_timezone and to_timezone are java.util.TimeZone ID formats, for example: "UTC", "America/Los_Angeles", "PDT","Europe/London". For more information on timestamp formats, see DateTimeFormatter.

FROM_UNIXTIME

1
FROM_UNIXTIME(milliseconds)

Converts a BIGINT millisecond timestamp value into a TIMESTAMP value.

FROM_DAYS

1
FROM_DAYS(days)

Converts an INT number of days since epoch to a DATE value.

TIMESTAMPADD

Since: 0.17

1
TIMESTAMPADD(unit, interval, COL0)

Adds an interval to a timestamp. Intervals are defined by an integer value and a supported time unit.

TIMESTAMPSUB

Since: 0.17

1
TIMESTAMPSUB(unit, interval, COL0)

Subtracts an interval from a timestamp. Intervals are defined by an integer value and a supported time unit.

TIMEADD

Since: 0.20

1
TIMEADD(unit, interval, COL0)

Adds an interval to a time. Intervals are defined by an integer value and a supported time unit.

TIMESUB

Since: 0.20

1
TIMESUB(unit, interval, COL0)

Subtracts an interval from a time. Intervals are defined by an integer value and a supported time unit.

DATEADD

Since: 0.20

1
DATEADD(unit, interval, COL0)

Adds an interval to a date. Intervals are defined by an integer value and a supported time unit.

DATESUB

Since: 0.20

1
DATESUB(unit, interval, COL0)

Subtracts an interval from a date. Intervals are defined by an integer value and a supported time unit.

URLs

Note

All ksqlDB URL functions assume URI syntax defined in RFC 39386. For more information on the structure of a URI, including definitions of the various components, see Section 3 of the RFC. For encoding/decoding, the application/x-www-form-urlencoded convention is followed.

URL_DECODE_PARAM

1
URL_DECODE_PARAM(col1)

Since: 0.6.0

Unescapes the URL-param-encoded_ value in col1. This is the inverse of URL_ENCODE_PARAM.

  • Input: 'url%20encoded
  • Output: url encoded

URL_ENCODE_PARAM

1
URL_ENCODE_PARAM(col1)

Since: 0.6.0

Escapes the value of col1 such that it can safely be used in URL query parameters. Note that this is not the same as encoding a value for use in the path portion of a URL.

  • Input: url encoded
  • Output: 'url%20encoded

URL_EXTRACT_FRAGMENT

1
URL_EXTRACT_FRAGMENT(url)

Since: 0.6.0

Extract the fragment portion of the specified value. Returns NULL if url is not a valid URL or if the fragment does not exist. Any encoded value will be decoded.

  • Input: http://test.com#frag,
  • Output: frag
  • Input: http://test.com#frag%20space,
  • Output: frag space

URL_EXTRACT_HOST

1
URL_EXTRACT_HOST(url)

Since: 0.6.0

Extract the host-name portion of the specified value. Returns NULL if the url is not a valid
URI according to RFC-2396.

  • Input: http://test.com:8080/path,
  • Output: test.com

URL_EXTRACT_PARAMETER

Since: 0.6.0

1
URL_EXTRACT_PARAMETER(url, parameter_name)

Extract 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. Encodes the param and decodes the output (see examples).

To get all of the parameter values from a URL as a single string, see URL_EXTRACT_QUERY.

  • Input: http://test.com?a%20b=c%20d, a b
  • Output: c d
  • Input: http://test.com?a=foo&b=bar, b
  • Output: bar

URL_EXTRACT_PATH

1
URL_EXTRACT_PATH(url)

Since: 0.6.0

Extracts the path from url. Returns NULL if url is not a valid URI but
returns an empty string if the path is empty.

  • Input: http://test.com/path/to#a
  • Output: path/to

URL_EXTRACT_PORT

1
URL_EXTRACT_PORT(url)

Since: 0.6.0

Extract the port number from url. Returns NULL if url is not a valid URI or does not contain an explicit port number.

  • Input: http://localhost:8080/path
  • Output: 8080

URL_EXTRACT_PROTOCOL

1
URL_EXTRACT_PROTOCOL(url)

Since: 0.6.0

Extract the protocol from url. Returns NULL if url is an invalid URI or has no protocol.

  • Input: http://test.com?a=foo&b=bar
  • Output: http

URL_EXTRACT_QUERY

Since: 0.6.0

1
URL_EXTRACT_QUERY(url)

Extract the decoded query-string portion of url. Returns NULL if no query-string is
present or url is not a valid URI.

  • Input: http://test.com?a=foo%20bar&b=baz
  • Output: a=foo bar&b=baz

Last update: 2021-08-19