docs/source/user-guide/sql/scalar_functions.md
absReturns the absolute value of a number.
abs(numeric_expression)
> SELECT abs(-5);
+----------+
| abs(-5) |
+----------+
| 5 |
+----------+
acosReturns the arc cosine or inverse cosine of a number.
acos(numeric_expression)
> SELECT acos(1);
+----------+
| acos(1) |
+----------+
| 0.0 |
+----------+
acoshReturns the area hyperbolic cosine or inverse hyperbolic cosine of a number.
acosh(numeric_expression)
> SELECT acosh(2);
+------------+
| acosh(2) |
+------------+
| 1.31696 |
+------------+
asinReturns the arc sine or inverse sine of a number.
asin(numeric_expression)
> SELECT asin(0.5);
+------------+
| asin(0.5) |
+------------+
| 0.5235988 |
+------------+
asinhReturns the area hyperbolic sine or inverse hyperbolic sine of a number.
asinh(numeric_expression)
> SELECT asinh(1);
+------------+
| asinh(1) |
+------------+
| 0.8813736 |
+------------+
atanReturns the arc tangent or inverse tangent of a number.
atan(numeric_expression)
> SELECT atan(1);
+-----------+
| atan(1) |
+-----------+
| 0.7853982 |
+-----------+
atan2Returns the arc tangent or inverse tangent of expression_y / expression_x.
atan2(expression_y, expression_x)
> SELECT atan2(1, 1);
+------------+
| atan2(1,1) |
+------------+
| 0.7853982 |
+------------+
atanhReturns the area hyperbolic tangent or inverse hyperbolic tangent of a number.
atanh(numeric_expression)
> SELECT atanh(0.5);
+-------------+
| atanh(0.5) |
+-------------+
| 0.5493061 |
+-------------+
cbrtReturns the cube root of a number.
cbrt(numeric_expression)
> SELECT cbrt(27);
+-----------+
| cbrt(27) |
+-----------+
| 3.0 |
+-----------+
ceilReturns the nearest integer greater than or equal to a number.
ceil(numeric_expression)
> SELECT ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
| 4.0 |
+------------+
cosReturns the cosine of a number.
cos(numeric_expression)
> SELECT cos(0);
+--------+
| cos(0) |
+--------+
| 1.0 |
+--------+
coshReturns the hyperbolic cosine of a number.
cosh(numeric_expression)
> SELECT cosh(1);
+-----------+
| cosh(1) |
+-----------+
| 1.5430806 |
+-----------+
cotReturns the cotangent of a number.
cot(numeric_expression)
> SELECT cot(1);
+---------+
| cot(1) |
+---------+
| 0.64209 |
+---------+
degreesConverts radians to degrees.
degrees(numeric_expression)
> SELECT degrees(pi());
+------------+
| degrees(0) |
+------------+
| 180.0 |
+------------+
expReturns the base-e exponential of a number.
exp(numeric_expression)
> SELECT exp(1);
+---------+
| exp(1) |
+---------+
| 2.71828 |
+---------+
factorialFactorial. Returns 1 if value is less than 2.
factorial(numeric_expression)
> SELECT factorial(5);
+---------------+
| factorial(5) |
+---------------+
| 120 |
+---------------+
floorReturns the nearest integer less than or equal to a number.
floor(numeric_expression)
> SELECT floor(3.14);
+-------------+
| floor(3.14) |
+-------------+
| 3.0 |
+-------------+
gcdReturns the greatest common divisor of expression_x and expression_y. Returns 0 if both inputs are zero.
gcd(expression_x, expression_y)
> SELECT gcd(48, 18);
+------------+
| gcd(48,18) |
+------------+
| 6 |
+------------+
isnanReturns true if a given number is +NaN or -NaN otherwise returns false.
isnan(numeric_expression)
> SELECT isnan(1);
+----------+
| isnan(1) |
+----------+
| false |
+----------+
iszeroReturns true if a given number is +0.0 or -0.0 otherwise returns false.
iszero(numeric_expression)
> SELECT iszero(0);
+------------+
| iszero(0) |
+------------+
| true |
+------------+
lcmReturns the least common multiple of expression_x and expression_y. Returns 0 if either input is zero.
lcm(expression_x, expression_y)
> SELECT lcm(4, 5);
+----------+
| lcm(4,5) |
+----------+
| 20 |
+----------+
lnReturns the natural logarithm of a number.
ln(numeric_expression)
> SELECT ln(2.71828);
+-------------+
| ln(2.71828) |
+-------------+
| 1.0 |
+-------------+
logReturns the base-x logarithm of a number. Can either provide a specified base, or if omitted then takes the base-10 of a number.
log(base, numeric_expression)
log(numeric_expression)
> SELECT log(10);
+---------+
| log(10) |
+---------+
| 1.0 |
+---------+
log10Returns the base-10 logarithm of a number.
log10(numeric_expression)
> SELECT log10(100);
+-------------+
| log10(100) |
+-------------+
| 2.0 |
+-------------+
log2Returns the base-2 logarithm of a number.
log2(numeric_expression)
> SELECT log2(8);
+-----------+
| log2(8) |
+-----------+
| 3.0 |
+-----------+
nanvlReturns the first argument if it's not NaN. Returns the second argument otherwise.
nanvl(expression_x, expression_y)
> SELECT nanvl(0, 5);
+------------+
| nanvl(0,5) |
+------------+
| 0 |
+------------+
piReturns an approximate value of π.
pi()
powAlias of power.
powerReturns a base expression raised to the power of an exponent.
power(base, exponent)
> SELECT power(2, 3);
+-------------+
| power(2,3) |
+-------------+
| 8 |
+-------------+
radiansConverts degrees to radians.
radians(numeric_expression)
> SELECT radians(180);
+----------------+
| radians(180) |
+----------------+
| 3.14159265359 |
+----------------+
randomReturns a random float value in the range [0, 1). The random seed is unique to each row.
random()
> SELECT random();
+------------------+
| random() |
+------------------+
| 0.7389238902938 |
+------------------+
roundRounds a number to the nearest integer.
round(numeric_expression[, decimal_places])
> SELECT round(3.14159);
+--------------+
| round(3.14159)|
+--------------+
| 3.0 |
+--------------+
signumReturns the sign of a number.
Negative numbers return -1.
Zero and positive numbers return 1.
signum(numeric_expression)
> SELECT signum(-42);
+-------------+
| signum(-42) |
+-------------+
| -1 |
+-------------+
sinReturns the sine of a number.
sin(numeric_expression)
> SELECT sin(0);
+----------+
| sin(0) |
+----------+
| 0.0 |
+----------+
sinhReturns the hyperbolic sine of a number.
sinh(numeric_expression)
> SELECT sinh(1);
+-----------+
| sinh(1) |
+-----------+
| 1.1752012 |
+-----------+
sqrtReturns the square root of a number.
sqrt(numeric_expression)
tanReturns the tangent of a number.
tan(numeric_expression)
> SELECT tan(pi()/4);
+--------------+
| tan(PI()/4) |
+--------------+
| 1.0 |
+--------------+
tanhReturns the hyperbolic tangent of a number.
tanh(numeric_expression)
> SELECT tanh(20);
+----------+
| tanh(20) |
+----------+
| 1.0 |
+----------+
truncTruncates a number to a whole number or truncated to the specified decimal places.
trunc(numeric_expression[, decimal_places])
decimal_places is a positive integer, truncates digits to the
right of the decimal point. If decimal_places is a negative
integer, replaces digits to the left of the decimal point with 0.> SELECT trunc(42.738);
+----------------+
| trunc(42.738) |
+----------------+
| 42 |
+----------------+
coalesceReturns the first of its arguments that is not null. Returns null if all arguments are null. This function is often used to substitute a default value for null values.
coalesce(expression1[, ..., expression_n])
> select coalesce(null, null, 'datafusion');
+----------------------------------------+
| coalesce(NULL,NULL,Utf8("datafusion")) |
+----------------------------------------+
| datafusion |
+----------------------------------------+
greatestReturns the greatest value in a list of expressions. Returns null if all expressions are null.
greatest(expression1[, ..., expression_n])
> select greatest(4, 7, 5);
+---------------------------+
| greatest(4,7,5) |
+---------------------------+
| 7 |
+---------------------------+
ifnullAlias of nvl.
leastReturns the smallest value in a list of expressions. Returns null if all expressions are null.
least(expression1[, ..., expression_n])
> select least(4, 7, 5);
+---------------------------+
| least(4,7,5) |
+---------------------------+
| 4 |
+---------------------------+
nullifReturns null if expression1 equals expression2; otherwise it returns expression1.
This can be used to perform the inverse operation of coalesce.
nullif(expression1, expression2)
> select nullif('datafusion', 'data');
+-----------------------------------------+
| nullif(Utf8("datafusion"),Utf8("data")) |
+-----------------------------------------+
| datafusion |
+-----------------------------------------+
> select nullif('datafusion', 'datafusion');
+-----------------------------------------------+
| nullif(Utf8("datafusion"),Utf8("datafusion")) |
+-----------------------------------------------+
| |
+-----------------------------------------------+
nvlReturns expression2 if expression1 is NULL otherwise it returns expression1 and expression2 is not evaluated. This function can be used to substitute a default value for NULL values.
nvl(expression1, expression2)
> select nvl(null, 'a');
+---------------------+
| nvl(NULL,Utf8("a")) |
+---------------------+
| a |
+---------------------+\
> select nvl('b', 'a');
+--------------------------+
| nvl(Utf8("b"),Utf8("a")) |
+--------------------------+
| b |
+--------------------------+
nvl2Returns expression2 if expression1 is not NULL; otherwise it returns expression3.
nvl2(expression1, expression2, expression3)
> select nvl2(null, 'a', 'b');
+--------------------------------+
| nvl2(NULL,Utf8("a"),Utf8("b")) |
+--------------------------------+
| b |
+--------------------------------+
> select nvl2('data', 'a', 'b');
+----------------------------------------+
| nvl2(Utf8("data"),Utf8("a"),Utf8("b")) |
+----------------------------------------+
| a |
+----------------------------------------+
asciiReturns the first Unicode scalar value of a string.
ascii(str)
> select ascii('abc');
+--------------------+
| ascii(Utf8("abc")) |
+--------------------+
| 97 |
+--------------------+
> select ascii('🚀');
+-------------------+
| ascii(Utf8("🚀")) |
+-------------------+
| 128640 |
+-------------------+
Related functions:
bit_lengthReturns the bit length of a string.
bit_length(str)
> select bit_length('datafusion');
+--------------------------------+
| bit_length(Utf8("datafusion")) |
+--------------------------------+
| 80 |
+--------------------------------+
Related functions:
btrimTrims the specified trim string from the start and end of a string. If no trim string is provided, all spaces are removed from the start and end of the input string.
btrim(str[, trim_str])
> select btrim('__datafusion____', '_');
+-------------------------------------------+
| btrim(Utf8("__datafusion____"),Utf8("_")) |
+-------------------------------------------+
| datafusion |
+-------------------------------------------+
trim(BOTH trim_str FROM str)
trim(trim_str FROM str)
Related functions:
char_lengthAlias of character_length.
character_lengthReturns the number of characters in a string.
character_length(str)
> select character_length('Ångström');
+------------------------------------+
| character_length(Utf8("Ångström")) |
+------------------------------------+
| 8 |
+------------------------------------+
Related functions:
chrReturns a string containing the character with the specified Unicode scalar value.
chr(expression)
> select chr(128640);
+--------------------+
| chr(Int64(128640)) |
+--------------------+
| 🚀 |
+--------------------+
Related functions:
concatConcatenates multiple strings together.
concat(str[, ..., str_n])
> select concat('data', 'f', 'us', 'ion');
+-------------------------------------------------------+
| concat(Utf8("data"),Utf8("f"),Utf8("us"),Utf8("ion")) |
+-------------------------------------------------------+
| datafusion |
+-------------------------------------------------------+
Related functions:
concat_wsConcatenates multiple strings together with a specified separator.
concat_ws(separator, str[, ..., str_n])
> select concat_ws('_', 'data', 'fusion');
+--------------------------------------------------+
| concat_ws(Utf8("_"),Utf8("data"),Utf8("fusion")) |
+--------------------------------------------------+
| data_fusion |
+--------------------------------------------------+
Related functions:
containsReturn true if search_str is found within string (case-sensitive).
contains(str, search_str)
> select contains('the quick brown fox', 'row');
+---------------------------------------------------+
| contains(Utf8("the quick brown fox"),Utf8("row")) |
+---------------------------------------------------+
| true |
+---------------------------------------------------+
ends_withTests if a string ends with a substring.
ends_with(str, substr)
> select ends_with('datafusion', 'soin');
+--------------------------------------------+
| ends_with(Utf8("datafusion"),Utf8("soin")) |
+--------------------------------------------+
| false |
+--------------------------------------------+
> select ends_with('datafusion', 'sion');
+--------------------------------------------+
| ends_with(Utf8("datafusion"),Utf8("sion")) |
+--------------------------------------------+
| true |
+--------------------------------------------+
find_in_setReturns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.
find_in_set(str, strlist)
> select find_in_set('b', 'a,b,c,d');
+----------------------------------------+
| find_in_set(Utf8("b"),Utf8("a,b,c,d")) |
+----------------------------------------+
| 2 |
+----------------------------------------+
initcapCapitalizes the first character in each word in the input string. Words are delimited by non-alphanumeric characters.
initcap(str)
> select initcap('apache datafusion');
+------------------------------------+
| initcap(Utf8("apache datafusion")) |
+------------------------------------+
| Apache Datafusion |
+------------------------------------+
Related functions:
instrAlias of strpos.
leftReturns a specified number of characters from the left side of a string.
left(str, n)
> select left('datafusion', 4);
+-----------------------------------+
| left(Utf8("datafusion"),Int64(4)) |
+-----------------------------------+
| data |
+-----------------------------------+
Related functions:
lengthAlias of character_length.
levenshteinReturns the Levenshtein distance between the two given strings.
levenshtein(str1, str2)
> select levenshtein('kitten', 'sitting');
+---------------------------------------------+
| levenshtein(Utf8("kitten"),Utf8("sitting")) |
+---------------------------------------------+
| 3 |
+---------------------------------------------+
lowerConverts a string to lower-case.
lower(str)
> select lower('Ångström');
+-------------------------+
| lower(Utf8("Ångström")) |
+-------------------------+
| ångström |
+-------------------------+
Related functions:
lpadPads the left side of a string with another string to a specified string length.
lpad(str, n[, padding_str])
> select lpad('Dolly', 10, 'hello');
+---------------------------------------------+
| lpad(Utf8("Dolly"),Int64(10),Utf8("hello")) |
+---------------------------------------------+
| helloDolly |
+---------------------------------------------+
Related functions:
ltrimTrims the specified trim string from the beginning of a string. If no trim string is provided, spaces are removed from the start of the input string.
ltrim(str[, trim_str])
> select ltrim(' datafusion ');
+-------------------------------+
| ltrim(Utf8(" datafusion ")) |
+-------------------------------+
| datafusion |
+-------------------------------+
> select ltrim('___datafusion___', '_');
+-------------------------------------------+
| ltrim(Utf8("___datafusion___"),Utf8("_")) |
+-------------------------------------------+
| datafusion___ |
+-------------------------------------------+
trim(LEADING trim_str FROM str)
Related functions:
octet_lengthReturns the length of a string in bytes.
octet_length(str)
> select octet_length('Ångström');
+--------------------------------+
| octet_length(Utf8("Ångström")) |
+--------------------------------+
| 10 |
+--------------------------------+
Related functions:
overlayReturns the string which is replaced by another string from the specified position and specified count length.
overlay(str PLACING substr FROM pos [FOR count])
> select overlay('Txxxxas' placing 'hom' from 2 for 4);
+--------------------------------------------------------+
| overlay(Utf8("Txxxxas"),Utf8("hom"),Int64(2),Int64(4)) |
+--------------------------------------------------------+
| Thomas |
+--------------------------------------------------------+
positionAlias of strpos.
repeatReturns a string with an input string repeated a specified number.
repeat(str, n)
> select repeat('data', 3);
+-------------------------------+
| repeat(Utf8("data"),Int64(3)) |
+-------------------------------+
| datadatadata |
+-------------------------------+
replaceReplaces all occurrences of a specified substring in a string with a new substring.
replace(str, substr, replacement)
> select replace('ABabbaBA', 'ab', 'cd');
+-------------------------------------------------+
| replace(Utf8("ABabbaBA"),Utf8("ab"),Utf8("cd")) |
+-------------------------------------------------+
| ABcdbaBA |
+-------------------------------------------------+
reverseReverses the character order of a string.
reverse(str)
> select reverse('datafusion');
+-----------------------------+
| reverse(Utf8("datafusion")) |
+-----------------------------+
| noisufatad |
+-----------------------------+
rightReturns a specified number of characters from the right side of a string.
right(str, n)
> select right('datafusion', 6);
+------------------------------------+
| right(Utf8("datafusion"),Int64(6)) |
+------------------------------------+
| fusion |
+------------------------------------+
Related functions:
rpadPads the right side of a string with another string to a specified string length.
rpad(str, n[, padding_str])
> select rpad('datafusion', 20, '_-');
+-----------------------------------------------+
| rpad(Utf8("datafusion"),Int64(20),Utf8("_-")) |
+-----------------------------------------------+
| datafusion_-_-_-_-_- |
+-----------------------------------------------+
Related functions:
rtrimTrims the specified trim string from the end of a string. If no trim string is provided, all spaces are removed from the end of the input string.
rtrim(str[, trim_str])
> select rtrim(' datafusion ');
+-------------------------------+
| rtrim(Utf8(" datafusion ")) |
+-------------------------------+
| datafusion |
+-------------------------------+
> select rtrim('___datafusion___', '_');
+-------------------------------------------+
| rtrim(Utf8("___datafusion___"),Utf8("_")) |
+-------------------------------------------+
| ___datafusion |
+-------------------------------------------+
trim(TRAILING trim_str FROM str)
Related functions:
split_partSplits a string based on a specified delimiter and returns the substring in the specified position.
split_part(str, delimiter, pos)
> select split_part('1.2.3.4.5', '.', 3);
+--------------------------------------------------+
| split_part(Utf8("1.2.3.4.5"),Utf8("."),Int64(3)) |
+--------------------------------------------------+
| 3 |
+--------------------------------------------------+
starts_withTests if a string starts with a substring.
starts_with(str, substr)
> select starts_with('datafusion','data');
+----------------------------------------------+
| starts_with(Utf8("datafusion"),Utf8("data")) |
+----------------------------------------------+
| true |
+----------------------------------------------+
strposReturns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0.
strpos(str, substr)
> select strpos('datafusion', 'fus');
+----------------------------------------+
| strpos(Utf8("datafusion"),Utf8("fus")) |
+----------------------------------------+
| 5 |
+----------------------------------------+
position(substr in origstr)
substrExtracts a substring of a specified number of characters from a specific starting position in a string.
substr(str, start_pos[, length])
> select substr('datafusion', 5, 3);
+----------------------------------------------+
| substr(Utf8("datafusion"),Int64(5),Int64(3)) |
+----------------------------------------------+
| fus |
+----------------------------------------------+
substring(str from start_pos for length)
substr_indexReturns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
substr_index(str, delim, count)
> select substr_index('www.apache.org', '.', 1);
+---------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(1)) |
+---------------------------------------------------------+
| www |
+---------------------------------------------------------+
> select substr_index('www.apache.org', '.', -1);
+----------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(-1)) |
+----------------------------------------------------------+
| org |
+----------------------------------------------------------+
substringAlias of substr.
substring_indexAlias of substr_index.
to_hexConverts an integer to a hexadecimal string.
to_hex(int)
> select to_hex(12345689);
+-------------------------+
| to_hex(Int64(12345689)) |
+-------------------------+
| bc6159 |
+-------------------------+
translatePerforms character-wise substitution based on a mapping.
translate(str, from, to)
> select translate('twice', 'wic', 'her');
+--------------------------------------------------+
| translate(Utf8("twice"),Utf8("wic"),Utf8("her")) |
+--------------------------------------------------+
| there |
+--------------------------------------------------+
trimAlias of btrim.
upperConverts a string to upper-case.
upper(str)
> select upper('dataFusion');
+---------------------------+
| upper(Utf8("dataFusion")) |
+---------------------------+
| DATAFUSION |
+---------------------------+
Related functions:
uuidReturns UUID v4 string value which is unique per row.
uuid()
> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 6ec17ef8-1934-41cc-8d59-d0c8f9eea1f0 |
+--------------------------------------+
decodeDecode binary data from textual representation in string.
decode(expression, format)
Related functions:
encodeEncode binary data into a textual representation.
encode(expression, format)
base64, base64pad, hexRelated functions:
Apache DataFusion uses a PCRE-like regular expression syntax (minus support for several features including look-around and backreferences). The following regular expression functions are supported:
regexp_countReturns the number of matches that a regular expression has in a string.
regexp_count(str, regexp[, start, flags])
> select regexp_count('abcAbAbc', 'abc', 2, 'i');
+---------------------------------------------------------------+
| regexp_count(Utf8("abcAbAbc"),Utf8("abc"),Int64(2),Utf8("i")) |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
regexp_instrReturns the position in a string where the specified occurrence of a POSIX regular expression is located.
regexp_instr(str, regexp[, start[, N[, flags[, subexpr]]]])
> SELECT regexp_instr('ABCDEF', 'C(.)(..)');
+---------------------------------------------------------------+
| regexp_instr(Utf8("ABCDEF"),Utf8("C(.)(..)")) |
+---------------------------------------------------------------+
| 3 |
+---------------------------------------------------------------+
regexp_likeReturns true if a regular expression has at least one match in a string, false otherwise.
regexp_like(str, regexp[, flags])
select regexp_like('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+--------------------------------------------------------+
| regexp_like(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+--------------------------------------------------------+
| true |
+--------------------------------------------------------+
SELECT regexp_like('aBc', '(b|d)', 'i');
+--------------------------------------------------+
| regexp_like(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+--------------------------------------------------+
| true |
+--------------------------------------------------+
Additional examples can be found here
regexp_matchReturns the first regular expression matches in a string.
regexp_match(str, regexp[, flags])
> select regexp_match('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+---------------------------------------------------------+
| regexp_match(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+---------------------------------------------------------+
| [Köln] |
+---------------------------------------------------------+
SELECT regexp_match('aBc', '(b|d)', 'i');
+---------------------------------------------------+
| regexp_match(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+---------------------------------------------------+
| [B] |
+---------------------------------------------------+
Additional examples can be found here
regexp_replaceReplaces substrings in a string that match a regular expression.
regexp_replace(str, regexp, replacement[, flags])
> select regexp_replace('foobarbaz', 'b(..)', 'X\\1Y', 'g');
+------------------------------------------------------------------------+
| regexp_replace(Utf8("foobarbaz"),Utf8("b(..)"),Utf8("X\1Y"),Utf8("g")) |
+------------------------------------------------------------------------+
| fooXarYXazY |
+------------------------------------------------------------------------+
SELECT regexp_replace('aBc', '(b|d)', 'Ab\\1a', 'i');
+-------------------------------------------------------------------+
| regexp_replace(Utf8("aBc"),Utf8("(b|d)"),Utf8("Ab\1a"),Utf8("i")) |
+-------------------------------------------------------------------+
| aAbBac |
+-------------------------------------------------------------------+
Additional examples can be found here
current_dateReturns the current date in the session time zone.
The current_date() return value is determined at query time and will return the same date, no matter when in the query plan the function executes.
current_date()
(optional) SET datafusion.execution.time_zone = '+00:00';
SELECT current_date();
current_timeReturns the current time in the session time zone.
The current_time() return value is determined at query time and will return the same time, no matter when in the query plan the function executes.
The session time zone can be set using the statement 'SET datafusion.execution.time_zone = desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc.
current_time()
(optional) SET datafusion.execution.time_zone = '+00:00';
SELECT current_time();
current_timestampAlias of now.
date_binCalculates time intervals and returns the start of the interval nearest to the specified timestamp. Use date_bin to downsample time series data by grouping rows into time-based "bins" or "windows" and applying an aggregate or selector function to each window.
For example, if you "bin" or "window" data into 15 minute intervals, an input timestamp of 2023-01-01T18:18:18Z will be updated to the start time of the 15 minute bin it is in: 2023-01-01T18:15:00Z.
date_bin(interval, expression, origin-timestamp)
interval: Bin interval.
expression: Time expression to operate on. Can be a constant, column, or function.
origin-timestamp: Optional. Starting point used to determine bin boundaries. If not specified defaults 1970-01-01T00:00:00Z (the UNIX epoch in UTC). The following intervals are supported:
-- Bin the timestamp into 1 day intervals
> SELECT date_bin(interval '1 day', time) as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z') t(time);
+---------------------+
| bin |
+---------------------+
| 2023-01-01T00:00:00 |
| 2023-01-03T00:00:00 |
+---------------------+
2 row(s) fetched.
-- Bin the timestamp into 1 day intervals starting at 3AM on 2023-01-01
> SELECT date_bin(interval '1 day', time, '2023-01-01T03:00:00') as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z') t(time);
+---------------------+
| bin |
+---------------------+
| 2023-01-01T03:00:00 |
| 2023-01-03T03:00:00 |
+---------------------+
2 row(s) fetched.
-- Bin the time into 15 minute intervals starting at 1 min
> SELECT date_bin(interval '15 minutes', time, TIME '00:01:00') as bin
FROM VALUES (TIME '02:18:18'), (TIME '19:00:03') t(time);
+----------+
| bin |
+----------+
| 02:16:00 |
| 18:46:00 |
+----------+
2 row(s) fetched.
date_formatAlias of to_char.
date_partReturns the specified part of the date as an integer.
date_part(part, expression)
part: Part of the date to return. The following date parts are supported:
expression: Time expression to operate on. Can be a constant, column, or function.
extract(field FROM source)
date_truncTruncates a timestamp or time value to a specified precision.
date_trunc(precision, expression)
precision: Time precision to truncate to. The following precisions are supported:
For Timestamp types:
For Time types (hour, minute, second, millisecond, microsecond only):
expression: Timestamp or time expression to operate on. Can be a constant, column, or function.
datepartAlias of date_part.
datetruncAlias of date_trunc.
from_unixtimeConverts an integer to RFC3339 timestamp format (YYYY-MM-DDT00:00:00.000000000Z). Integers and unsigned integers are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z) return the corresponding timestamp.
from_unixtime(expression[, timezone])
> select from_unixtime(1599572549, 'America/New_York');
+-----------------------------------------------------------+
| from_unixtime(Int64(1599572549),Utf8("America/New_York")) |
+-----------------------------------------------------------+
| 2020-09-08T09:42:29-04:00 |
+-----------------------------------------------------------+
make_dateMake a date from year/month/day component parts.
make_date(year, month, day)
> select make_date(2023, 1, 31);
+-------------------------------------------+
| make_date(Int64(2023),Int64(1),Int64(31)) |
+-------------------------------------------+
| 2023-01-31 |
+-------------------------------------------+
> select make_date('2023', '01', '31');
+-----------------------------------------------+
| make_date(Utf8("2023"),Utf8("01"),Utf8("31")) |
+-----------------------------------------------+
| 2023-01-31 |
+-----------------------------------------------+
Additional examples can be found here
make_timeMake a time from hour/minute/second component parts.
make_time(hour, minute, second)
> select make_time(13, 23, 1);
+-------------------------------------------+
| make_time(Int64(13),Int64(23),Int64(1)) |
+-------------------------------------------+
| 13:23:01 |
+-------------------------------------------+
> select make_time('23', '01', '31');
+-----------------------------------------------+
| make_time(Utf8("23"),Utf8("01"),Utf8("31")) |
+-----------------------------------------------+
| 23:01:31 |
+-----------------------------------------------+
Additional examples can be found here
nowReturns the current timestamp in the system configured timezone (None by default).
The now() return value is determined at query time and will return the same timestamp, no matter when in the query plan the function executes.
now()
to_charReturns a string representation of a date, time, timestamp or duration based on a Chrono format. Unlike the PostgreSQL equivalent of this function numerical formatting is not supported.
to_char(expression, format)
> select to_char('2023-03-01'::date, '%d-%m-%Y');
+----------------------------------------------+
| to_char(Utf8("2023-03-01"),Utf8("%d-%m-%Y")) |
+----------------------------------------------+
| 01-03-2023 |
+----------------------------------------------+
Additional examples can be found here
to_dateConverts a value to a date (YYYY-MM-DD).
Supports strings, numeric and timestamp types as input.
Strings are parsed as YYYY-MM-DD (e.g. '2023-07-20') if no Chrono formats are provided.
Integers and doubles are interpreted as days since the unix epoch (1970-01-01T00:00:00Z).
Returns the corresponding date.
Note: to_date returns Date32, which represents its values as the number of days since unix epoch(1970-01-01) stored as signed 32 bit value. The largest supported date value is 9999-12-31.
to_date('2017-05-31', '%Y-%m-%d')
> select to_date('2023-01-31');
+-------------------------------+
| to_date(Utf8("2023-01-31")) |
+-------------------------------+
| 2023-01-31 |
+-------------------------------+
> select to_date('2023/01/31', '%Y-%m-%d', '%Y/%m/%d');
+---------------------------------------------------------------------+
| to_date(Utf8("2023/01/31"),Utf8("%Y-%m-%d"),Utf8("%Y/%m/%d")) |
+---------------------------------------------------------------------+
| 2023-01-31 |
+---------------------------------------------------------------------+
Additional examples can be found here
to_local_timeConverts a timestamp with a timezone to a timestamp without a timezone (with no offset or timezone information). This function handles daylight saving time changes.
to_local_time(expression)
> SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp);
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20 |
+---------------------------------------------+
> SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels');
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20 |
+---------------------------------------------+
> SELECT
time,
arrow_typeof(time) as type,
to_local_time(time) as to_local_time,
arrow_typeof(to_local_time(time)) as to_local_time_type
FROM (
SELECT '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels' AS time
);
+---------------------------+----------------------------------+---------------------+--------------------+
| time | type | to_local_time | to_local_time_type |
+---------------------------+----------------------------------+---------------------+--------------------+
| 2024-04-01T00:00:20+02:00 | Timestamp(ns, "Europe/Brussels") | 2024-04-01T00:00:20 | Timestamp(ns) |
+---------------------------+----------------------------------+---------------------+--------------------+
# combine `to_local_time()` with `date_bin()` to bin on boundaries in the timezone rather
# than UTC boundaries
> SELECT date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AS date_bin;
+---------------------+
| date_bin |
+---------------------+
| 2024-04-01T00:00:00 |
+---------------------+
> SELECT date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AT TIME ZONE 'Europe/Brussels' AS date_bin_with_timezone;
+---------------------------+
| date_bin_with_timezone |
+---------------------------+
| 2024-04-01T00:00:00+02:00 |
+---------------------------+
to_timeConverts a value to a time (HH:MM:SS.nnnnnnnnn).
Supports strings and timestamps as input.
Strings are parsed as HH:MM:SS, HH:MM:SS.nnnnnnnnn, or HH:MM if no Chrono formats are provided.
Timestamps will have the time portion extracted.
Returns the corresponding time.
Note: to_time returns Time64(Nanosecond), which represents the time of day in nanoseconds since midnight.
to_time('12:30:45', '%H:%M:%S')
> select to_time('12:30:45');
+---------------------------+
| to_time(Utf8("12:30:45")) |
+---------------------------+
| 12:30:45 |
+---------------------------+
> select to_time('12-30-45', '%H-%M-%S');
+--------------------------------------------+
| to_time(Utf8("12-30-45"),Utf8("%H-%M-%S")) |
+--------------------------------------------+
| 12:30:45 |
+--------------------------------------------+
> select to_time('2024-01-15 14:30:45'::timestamp);
+--------------------------------------------------+
| to_time(Utf8("2024-01-15 14:30:45")) |
+--------------------------------------------------+
| 14:30:45 |
+--------------------------------------------------+
Additional examples can be found here
to_timestampConverts a value to a timestamp (YYYY-MM-DDT00:00:00.000000<TZ>) in the session time zone. Supports strings,
integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00')
if no Chrono formats are provided.
Strings that parse without a time zone are treated as if they are in the
session time zone, or UTC if no session time zone is set.
Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z).
Note: to_timestamp returns Timestamp(ns, TimeZone) where the time zone is the session time zone. The supported range
for integer input is between-9223372037 and 9223372036. Supported range for string input is between
1677-09-21T00:12:44.0 and 2262-04-11T23:47:16.0. Please use to_timestamp_seconds
for the input outside of supported bounds.
The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'.
The time zone can be a value like +00:00, 'Europe/London' etc.
to_timestamp(expression[, ..., format_n])
> select to_timestamp('2023-01-31T09:26:56.123456789-05:00');
+-----------------------------------------------------------+
| to_timestamp(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-----------------------------------------------------------+
| 2023-01-31T14:26:56.123456789 |
+-----------------------------------------------------------+
> select to_timestamp('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+--------------------------------------------------------------------------------------------------------+
| to_timestamp(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+--------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123456789 |
+--------------------------------------------------------------------------------------------------------+
Additional examples can be found here
to_timestamp_microsConverts a value to a timestamp (YYYY-MM-DDT00:00:00.000000<TZ>) in the session time zone. Supports strings,
integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00')
if no Chrono formats are provided.
Strings that parse without a time zone are treated as if they are in the
session time zone, or UTC if no session time zone is set.
Integers, unsigned integers, and doubles are interpreted as microseconds since the unix epoch (1970-01-01T00:00:00Z).
The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'.
The time zone can be a value like +00:00, 'Europe/London' etc.
to_timestamp_micros(expression[, ..., format_n])
> select to_timestamp_micros('2023-01-31T09:26:56.123456789-05:00');
+------------------------------------------------------------------+
| to_timestamp_micros(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+------------------------------------------------------------------+
| 2023-01-31T14:26:56.123456 |
+------------------------------------------------------------------+
> select to_timestamp_micros('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+---------------------------------------------------------------------------------------------------------------+
| to_timestamp_micros(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+---------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123456 |
+---------------------------------------------------------------------------------------------------------------+
Additional examples can be found here
to_timestamp_millisConverts a value to a timestamp (YYYY-MM-DDT00:00:00.000<TZ>) in the session time zone. Supports strings,
integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00')
if no Chrono formats are provided.
Strings that parse without a time zone are treated as if they are in the
session time zone, or UTC if no session time zone is set.
Integers, unsigned integers, and doubles are interpreted as milliseconds since the unix epoch (1970-01-01T00:00:00Z).
The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'.
The time zone can be a value like +00:00, 'Europe/London' etc.
to_timestamp_millis(expression[, ..., format_n])
> select to_timestamp_millis('2023-01-31T09:26:56.123456789-05:00');
+------------------------------------------------------------------+
| to_timestamp_millis(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+------------------------------------------------------------------+
| 2023-01-31T14:26:56.123 |
+------------------------------------------------------------------+
> select to_timestamp_millis('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+---------------------------------------------------------------------------------------------------------------+
| to_timestamp_millis(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+---------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123 |
+---------------------------------------------------------------------------------------------------------------+
Additional examples can be found here
to_timestamp_nanosConverts a value to a timestamp (YYYY-MM-DDT00:00:00.000000000<TZ>) in the session time zone. Supports strings,
integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00')
if no Chrono formats are provided.
Strings that parse without a time zone are treated as if they are in the
session time zone. Integers, unsigned integers, and doubles are interpreted as nanoseconds since the unix epoch (1970-01-01T00:00:00Z).
The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'.
The time zone can be a value like +00:00, 'Europe/London' etc.
to_timestamp_nanos(expression[, ..., format_n])
> select to_timestamp_nanos('2023-01-31T09:26:56.123456789-05:00');
+-----------------------------------------------------------------+
| to_timestamp_nanos(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-----------------------------------------------------------------+
| 2023-01-31T14:26:56.123456789 |
+-----------------------------------------------------------------+
> select to_timestamp_nanos('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+--------------------------------------------------------------------------------------------------------------+
| to_timestamp_nanos(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+--------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123456789 |
+---------------------------------------------------------------------------------------------------------------+
Additional examples can be found here
to_timestamp_secondsConverts a value to a timestamp (YYYY-MM-DDT00:00:00<TZ>) in the session time zone. Supports strings,
integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00')
if no Chrono formats are provided.
Strings that parse without a time zone are treated as if they are in the
session time zone, or UTC if no session time zone is set.
Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z).
The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'.
The time zone can be a value like +00:00, 'Europe/London' etc.
to_timestamp_seconds(expression[, ..., format_n])
> select to_timestamp_seconds('2023-01-31T09:26:56.123456789-05:00');
+-------------------------------------------------------------------+
| to_timestamp_seconds(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-------------------------------------------------------------------+
| 2023-01-31T14:26:56 |
+-------------------------------------------------------------------+
> select to_timestamp_seconds('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+----------------------------------------------------------------------------------------------------------------+
| to_timestamp_seconds(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+----------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00 |
+----------------------------------------------------------------------------------------------------------------+
Additional examples can be found here
to_unixtimeConverts a value to seconds since the unix epoch (1970-01-01T00:00:00).
Supports strings, dates, timestamps, integer, unsigned integer, and float types as input.
Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00')
if no Chrono formats are provided.
Integers, unsigned integers, and floats are interpreted as seconds since the unix epoch (1970-01-01T00:00:00).
to_unixtime(expression[, ..., format_n])
> select to_unixtime('2020-09-08T12:00:00+00:00');
+------------------------------------------------+
| to_unixtime(Utf8("2020-09-08T12:00:00+00:00")) |
+------------------------------------------------+
| 1599566400 |
+------------------------------------------------+
> select to_unixtime('01-14-2023 01:01:30+05:30', '%q', '%d-%m-%Y %H/%M/%S', '%+', '%m-%d-%Y %H:%M:%S%#z');
+-----------------------------------------------------------------------------------------------------------------------------+
| to_unixtime(Utf8("01-14-2023 01:01:30+05:30"),Utf8("%q"),Utf8("%d-%m-%Y %H/%M/%S"),Utf8("%+"),Utf8("%m-%d-%Y %H:%M:%S%#z")) |
+-----------------------------------------------------------------------------------------------------------------------------+
| 1673638290 |
+-----------------------------------------------------------------------------------------------------------------------------+
todayAlias of current_date.
array_any_valueReturns the first non-null element in the array.
array_any_value(array)
> select array_any_value([NULL, 1, 2, 3]);
+-------------------------------+
| array_any_value(List([NULL,1,2,3])) |
+-------------------------------------+
| 1 |
+-------------------------------------+
array_appendAppends an element to the end of an array.
array_append(array, element)
> select array_append([1, 2, 3], 4);
+--------------------------------------+
| array_append(List([1,2,3]),Int64(4)) |
+--------------------------------------+
| [1, 2, 3, 4] |
+--------------------------------------+
array_catAlias of array_concat.
array_concatConcatenates arrays.
array_concat(array[, ..., array_n])
> select array_concat([1, 2], [3, 4], [5, 6]);
+---------------------------------------------------+
| array_concat(List([1,2]),List([3,4]),List([5,6])) |
+---------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+---------------------------------------------------+
array_containsAlias of array_has.
array_dimsReturns an array of the array's dimensions.
array_dims(array)
> select array_dims([[1, 2, 3], [4, 5, 6]]);
+---------------------------------+
| array_dims(List([1,2,3,4,5,6])) |
+---------------------------------+
| [2, 3] |
+---------------------------------+
array_distanceReturns the Euclidean distance between two input arrays of equal length.
array_distance(array1, array2)
> select array_distance([1, 2], [1, 4]);
+------------------------------------+
| array_distance(List([1,2], [1,4])) |
+------------------------------------+
| 2.0 |
+------------------------------------+
array_distinctReturns distinct values from the array after removing duplicates.
array_distinct(array)
> select array_distinct([1, 3, 2, 3, 1, 2, 4]);
+---------------------------------+
| array_distinct(List([1,2,3,4])) |
+---------------------------------+
| [1, 2, 3, 4] |
+---------------------------------+
array_elementExtracts the element with the index n from the array.
array_element(array, index)
> select array_element([1, 2, 3, 4], 3);
+-----------------------------------------+
| array_element(List([1,2,3,4]),Int64(3)) |
+-----------------------------------------+
| 3 |
+-----------------------------------------+
array_emptyAlias of empty.
array_exceptReturns an array of the elements that appear in the first array but not in the second.
array_except(array1, array2)
> select array_except([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_except([1, 2, 3, 4], [5, 6, 3, 4]); |
+----------------------------------------------------+
| [1, 2] |
+----------------------------------------------------+
> select array_except([1, 2, 3, 4], [3, 4, 5, 6]);
+----------------------------------------------------+
| array_except([1, 2, 3, 4], [3, 4, 5, 6]); |
+----------------------------------------------------+
| [1, 2] |
+----------------------------------------------------+
array_extractAlias of array_element.
array_hasReturns true if the array contains the element.
array_has(array, element)
> select array_has([1, 2, 3], 2);
+-----------------------------+
| array_has(List([1,2,3]), 2) |
+-----------------------------+
| true |
+-----------------------------+
array_has_allReturns true if all elements of sub-array exist in array.
array_has_all(array, sub-array)
> select array_has_all([1, 2, 3, 4], [2, 3]);
+--------------------------------------------+
| array_has_all(List([1,2,3,4]), List([2,3])) |
+--------------------------------------------+
| true |
+--------------------------------------------+
array_has_anyReturns true if the arrays have any elements in common.
array_has_any(array1, array2)
> select array_has_any([1, 2, 3], [3, 4]);
+------------------------------------------+
| array_has_any(List([1,2,3]), List([3,4])) |
+------------------------------------------+
| true |
+------------------------------------------+
array_indexofAlias of array_position.
array_intersectReturns an array of elements in the intersection of array1 and array2.
array_intersect(array1, array2)
> select array_intersect([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_intersect([1, 2, 3, 4], [5, 6, 3, 4]); |
+----------------------------------------------------+
| [3, 4] |
+----------------------------------------------------+
> select array_intersect([1, 2, 3, 4], [5, 6, 7, 8]);
+----------------------------------------------------+
| array_intersect([1, 2, 3, 4], [5, 6, 7, 8]); |
+----------------------------------------------------+
| [] |
+----------------------------------------------------+
array_joinAlias of array_to_string.
array_lengthReturns the length of the array dimension.
array_length(array, dimension)
> select array_length([1, 2, 3, 4, 5], 1);
+-------------------------------------------+
| array_length(List([1,2,3,4,5]), 1) |
+-------------------------------------------+
| 5 |
+-------------------------------------------+
array_maxReturns the maximum value in the array.
array_max(array)
> select array_max([3,1,4,2]);
+-----------------------------------------+
| array_max(List([3,1,4,2])) |
+-----------------------------------------+
| 4 |
+-----------------------------------------+
array_minReturns the minimum value in the array.
array_min(array)
> select array_min([3,1,4,2]);
+-----------------------------------------+
| array_min(List([3,1,4,2])) |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
array_ndimsReturns the number of dimensions of the array.
array_ndims(array, element)
> select array_ndims([[1, 2, 3], [4, 5, 6]]);
+----------------------------------+
| array_ndims(List([1,2,3,4,5,6])) |
+----------------------------------+
| 2 |
+----------------------------------+
array_pop_backReturns the array without the last element.
array_pop_back(array)
> select array_pop_back([1, 2, 3]);
+-------------------------------+
| array_pop_back(List([1,2,3])) |
+-------------------------------+
| [1, 2] |
+-------------------------------+
array_pop_frontReturns the array without the first element.
array_pop_front(array)
> select array_pop_front([1, 2, 3]);
+-------------------------------+
| array_pop_front(List([1,2,3])) |
+-------------------------------+
| [2, 3] |
+-------------------------------+
array_positionReturns the position of the first occurrence of the specified element in the array, or NULL if not found. Comparisons are done using IS DISTINCT FROM semantics, so NULL is considered to match NULL.
array_position(array, element)
array_position(array, element, index)
> select array_position([1, 2, 2, 3, 1, 4], 2);
+----------------------------------------------+
| array_position(List([1,2,2,3,1,4]),Int64(2)) |
+----------------------------------------------+
| 2 |
+----------------------------------------------+
> select array_position([1, 2, 2, 3, 1, 4], 2, 3);
+----------------------------------------------------+
| array_position(List([1,2,2,3,1,4]),Int64(2), Int64(3)) |
+----------------------------------------------------+
| 3 |
+----------------------------------------------------+
array_positionsSearches for an element in the array, returns all occurrences.
array_positions(array, element)
> select array_positions([1, 2, 2, 3, 1, 4], 2);
+-----------------------------------------------+
| array_positions(List([1,2,2,3,1,4]),Int64(2)) |
+-----------------------------------------------+
| [2, 3] |
+-----------------------------------------------+
array_prependPrepends an element to the beginning of an array.
array_prepend(element, array)
> select array_prepend(1, [2, 3, 4]);
+---------------------------------------+
| array_prepend(Int64(1),List([2,3,4])) |
+---------------------------------------+
| [1, 2, 3, 4] |
+---------------------------------------+
array_push_backAlias of array_append.
array_push_frontAlias of array_prepend.
array_removeRemoves the first element from the array equal to the given value.
array_remove(array, element)
> select array_remove([1, 2, 2, 3, 2, 1, 4], 2);
+----------------------------------------------+
| array_remove(List([1,2,2,3,2,1,4]),Int64(2)) |
+----------------------------------------------+
| [1, 2, 3, 2, 1, 4] |
+----------------------------------------------+
array_remove_allRemoves all elements from the array equal to the given value.
array_remove_all(array, element)
> select array_remove_all([1, 2, 2, 3, 2, 1, 4], 2);
+--------------------------------------------------+
| array_remove_all(List([1,2,2,3,2,1,4]),Int64(2)) |
+--------------------------------------------------+
| [1, 3, 1, 4] |
+--------------------------------------------------+
array_remove_nRemoves the first max elements from the array equal to the given value.
array_remove_n(array, element, max))
> select array_remove_n([1, 2, 2, 3, 2, 1, 4], 2, 2);
+---------------------------------------------------------+
| array_remove_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(2)) |
+---------------------------------------------------------+
| [1, 3, 2, 1, 4] |
+---------------------------------------------------------+
array_repeatReturns an array containing element count times.
array_repeat(element, count)
> select array_repeat(1, 3);
+---------------------------------+
| array_repeat(Int64(1),Int64(3)) |
+---------------------------------+
| [1, 1, 1] |
+---------------------------------+
> select array_repeat([1, 2], 2);
+------------------------------------+
| array_repeat(List([1,2]),Int64(2)) |
+------------------------------------+
| [[1, 2], [1, 2]] |
+------------------------------------+
array_replaceReplaces the first occurrence of the specified element with another specified element.
array_replace(array, from, to)
> select array_replace([1, 2, 2, 3, 2, 1, 4], 2, 5);
+--------------------------------------------------------+
| array_replace(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+--------------------------------------------------------+
| [1, 5, 2, 3, 2, 1, 4] |
+--------------------------------------------------------+
array_replace_allReplaces all occurrences of the specified element with another specified element.
array_replace_all(array, from, to)
> select array_replace_all([1, 2, 2, 3, 2, 1, 4], 2, 5);
+------------------------------------------------------------+
| array_replace_all(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+------------------------------------------------------------+
| [1, 5, 5, 3, 5, 1, 4] |
+------------------------------------------------------------+
array_replace_nReplaces the first max occurrences of the specified element with another specified element.
array_replace_n(array, from, to, max)
> select array_replace_n([1, 2, 2, 3, 2, 1, 4], 2, 5, 2);
+-------------------------------------------------------------------+
| array_replace_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(5),Int64(2)) |
+-------------------------------------------------------------------+
| [1, 5, 5, 3, 2, 1, 4] |
+-------------------------------------------------------------------+
array_resizeResizes the list to contain size elements. Initializes new elements with value or empty if value is not set.
array_resize(array, size, value)
> select array_resize([1, 2, 3], 5, 0);
+-------------------------------------+
| array_resize(List([1,2,3],5,0)) |
+-------------------------------------+
| [1, 2, 3, 0, 0] |
+-------------------------------------+
array_reverseReturns the array with the order of the elements reversed.
array_reverse(array)
> select array_reverse([1, 2, 3, 4]);
+------------------------------------------------------------+
| array_reverse(List([1, 2, 3, 4])) |
+------------------------------------------------------------+
| [4, 3, 2, 1] |
+------------------------------------------------------------+
array_sliceReturns a slice of the array based on 1-indexed start and end positions.
array_slice(array, begin, end)
> select array_slice([1, 2, 3, 4, 5, 6, 7, 8], 3, 6);
+--------------------------------------------------------+
| array_slice(List([1,2,3,4,5,6,7,8]),Int64(3),Int64(6)) |
+--------------------------------------------------------+
| [3, 4, 5, 6] |
+--------------------------------------------------------+
array_sortSort array.
array_sort(array, desc, nulls_first)
ASC or DESC).NULLS FIRST or NULLS LAST).> select array_sort([3, 1, 2]);
+-----------------------------+
| array_sort(List([3,1,2])) |
+-----------------------------+
| [1, 2, 3] |
+-----------------------------+
array_to_stringConverts each element to its text representation.
array_to_string(array, delimiter[, null_string])
> select array_to_string([[1, 2, 3, 4], [5, 6, 7, 8]], ',');
+----------------------------------------------------+
| array_to_string(List([1,2,3,4,5,6,7,8]),Utf8(",")) |
+----------------------------------------------------+
| 1,2,3,4,5,6,7,8 |
+----------------------------------------------------+
array_unionReturns an array of elements that are present in both arrays (all elements from both arrays) without duplicates.
array_union(array1, array2)
> select array_union([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_union([1, 2, 3, 4], [5, 6, 3, 4]); |
+----------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+----------------------------------------------------+
> select array_union([1, 2, 3, 4], [5, 6, 7, 8]);
+----------------------------------------------------+
| array_union([1, 2, 3, 4], [5, 6, 7, 8]); |
+----------------------------------------------------+
| [1, 2, 3, 4, 5, 6, 7, 8] |
+----------------------------------------------------+
arrays_overlapAlias of array_has_any.
arrays_zipReturns an array of structs created by combining the elements of each input array at the same index. If the arrays have different lengths, shorter arrays are padded with NULLs.
arrays_zip(array1, array2[, ..., array_n])
> select arrays_zip([1, 2, 3], ['a', 'b', 'c']);
+---------------------------------------------------+
| arrays_zip([1, 2, 3], ['a', 'b', 'c']) |
+---------------------------------------------------+
| [{c0: 1, c1: a}, {c0: 2, c1: b}, {c0: 3, c1: c}] |
+---------------------------------------------------+
> select arrays_zip([1, 2], [3, 4, 5]);
+---------------------------------------------------+
| arrays_zip([1, 2], [3, 4, 5]) |
+---------------------------------------------------+
| [{c0: 1, c1: 3}, {c0: 2, c1: 4}, {c0: , c1: 5}] |
+---------------------------------------------------+
cardinalityReturns the total number of elements in the array.
cardinality(array)
> select cardinality([[1, 2, 3, 4], [5, 6, 7, 8]]);
+--------------------------------------+
| cardinality(List([1,2,3,4,5,6,7,8])) |
+--------------------------------------+
| 8 |
+--------------------------------------+
emptyReturns 1 for an empty array or 0 for a non-empty array.
empty(array)
> select empty([1]);
+------------------+
| empty(List([1])) |
+------------------+
| 0 |
+------------------+
flattenConverts an array of arrays to a flat array.
The flattened array contains all the elements from all source arrays.
flatten(array)
> select flatten([[1, 2], [3, 4]]);
+------------------------------+
| flatten(List([1,2], [3,4])) |
+------------------------------+
| [1, 2, 3, 4] |
+------------------------------+
generate_seriesSimilar to the range function, but it includes the upper bound.
generate_series(stop)
generate_series(start, stop[, step])
> select generate_series(1,3);
+------------------------------------+
| generate_series(Int64(1),Int64(3)) |
+------------------------------------+
| [1, 2, 3] |
+------------------------------------+
list_any_valueAlias of array_any_value.
list_appendAlias of array_append.
list_catAlias of array_concat.
list_concatAlias of array_concat.
list_containsAlias of array_has.
list_dimsAlias of array_dims.
list_distanceAlias of array_distance.
list_distinctAlias of array_distinct.
list_elementAlias of array_element.
list_emptyAlias of empty.
list_exceptAlias of array_except.
list_extractAlias of array_element.
list_hasAlias of array_has.
list_has_allAlias of array_has_all.
list_has_anyAlias of array_has_any.
list_indexofAlias of array_position.
list_intersectAlias of array_intersect.
list_joinAlias of array_to_string.
list_lengthAlias of array_length.
list_maxAlias of array_max.
list_ndimsAlias of array_ndims.
list_pop_backAlias of array_pop_back.
list_pop_frontAlias of array_pop_front.
list_positionAlias of array_position.
list_positionsAlias of array_positions.
list_prependAlias of array_prepend.
list_push_backAlias of array_append.
list_push_frontAlias of array_prepend.
list_removeAlias of array_remove.
list_remove_allAlias of array_remove_all.
list_remove_nAlias of array_remove_n.
list_repeatAlias of array_repeat.
list_replaceAlias of array_replace.
list_replace_allAlias of array_replace_all.
list_replace_nAlias of array_replace_n.
list_resizeAlias of array_resize.
list_reverseAlias of array_reverse.
list_sliceAlias of array_slice.
list_sortAlias of array_sort.
list_to_stringAlias of array_to_string.
list_unionAlias of array_union.
list_zipAlias of arrays_zip.
make_arrayReturns an array using the specified input expressions.
make_array(expression1[, ..., expression_n])
> select make_array(1, 2, 3, 4, 5);
+----------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3),Int64(4),Int64(5)) |
+----------------------------------------------------------+
| [1, 2, 3, 4, 5] |
+----------------------------------------------------------+
make_listAlias of make_array.
rangeReturns an Arrow array between start and stop with step. The range start..end contains all values with start <= x < end. It is empty if start >= end. Step cannot be 0.
range(stop)
range(start, stop[, step])
> select range(2, 10, 3);
+-----------------------------------+
| range(Int64(2),Int64(10),Int64(3))|
+-----------------------------------+
| [2, 5, 8] |
+-----------------------------------+
> select range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH);
+--------------------------------------------------------------------------+
| range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH) |
+--------------------------------------------------------------------------+
| [1992-09-01, 1992-10-01, 1992-11-01, 1992-12-01, 1993-01-01, 1993-02-01] |
+--------------------------------------------------------------------------+
string_to_arraySplits a string into an array of substrings based on a delimiter. Any substrings matching the optional null_str argument are replaced with NULL.
string_to_array(str, delimiter[, null_str])
NULL.> select string_to_array('abc##def', '##');
+-----------------------------------+
| string_to_array(Utf8('abc##def')) |
+-----------------------------------+
| ['abc', 'def'] |
+-----------------------------------+
> select string_to_array('abc def', ' ', 'def');
+---------------------------------------------+
| string_to_array(Utf8('abc def'), Utf8(' '), Utf8('def')) |
+---------------------------------------------+
| ['abc', NULL] |
+---------------------------------------------+
string_to_listAlias of string_to_array.
named_structReturns an Arrow struct using the specified name and input expressions pairs.
named_struct(expression1_name, expression1_input[, ..., expression_n_name, expression_n_input])
For example, this query converts two columns a and b to a single column with
a struct type of fields field_a and field_b:
> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
> select named_struct('field_a', a, 'field_b', b) from t;
+-------------------------------------------------------+
| named_struct(Utf8("field_a"),t.a,Utf8("field_b"),t.b) |
+-------------------------------------------------------+
| {field_a: 1, field_b: 2} |
| {field_a: 3, field_b: 4} |
+-------------------------------------------------------+
rowAlias of struct.
structReturns an Arrow struct using the specified input expressions optionally named.
Fields in the returned struct use the optional name or the cN naming convention.
For example: c0, c1, c2, etc.
struct(expression1[, ..., expression_n])
For example, this query converts two columns a and b to a single column with
a struct type of fields field_a and c1:
> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
-- use default names `c0`, `c1`
> select struct(a, b) from t;
+-----------------+
| struct(t.a,t.b) |
+-----------------+
| {c0: 1, c1: 2} |
| {c0: 3, c1: 4} |
+-----------------+
-- name the first field `field_a`
select struct(a as field_a, b) from t;
+--------------------------------------------------+
| named_struct(Utf8("field_a"),t.a,Utf8("c1"),t.b) |
+--------------------------------------------------+
| {field_a: 1, c1: 2} |
| {field_a: 3, c1: 4} |
+--------------------------------------------------+
element_atAlias of map_extract.
mapReturns an Arrow map with the specified key-value pairs.
The make_map function creates a map from two lists: one for keys and one for values. Each key must be unique and non-null.
map(key, value)
map(key: value)
make_map(['key1', 'key2'], ['value1', 'value2'])
map: Expression to be used for key. Can be a constant, column, function, or any combination of arithmetic or string operators.
For make_map: The list of keys to be used in the map. Each key must be unique and non-null.map: Expression to be used for value. Can be a constant, column, function, or any combination of arithmetic or string operators.
For make_map: The list of values to be mapped to the corresponding keys.-- Using map function
SELECT MAP('type', 'test');
----
{type: test}
SELECT MAP(['POST', 'HEAD', 'PATCH'], [41, 33, null]);
----
{POST: 41, HEAD: 33, PATCH: NULL}
SELECT MAP([[1,2], [3,4]], ['a', 'b']);
----
{[1, 2]: a, [3, 4]: b}
SELECT MAP { 'a': 1, 'b': 2 };
----
{a: 1, b: 2}
-- Using make_map function
SELECT MAKE_MAP(['POST', 'HEAD'], [41, 33]);
----
{POST: 41, HEAD: 33}
SELECT MAKE_MAP(['key1', 'key2'], ['value1', null]);
----
{key1: value1, key2: }
map_entriesReturns a list of all entries in the map.
map_entries(map)
SELECT map_entries(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[{'key': a, 'value': 1}, {'key': b, 'value': NULL}, {'key': c, 'value': 3}]
SELECT map_entries(map([100, 5], [42, 43]));
----
[{'key': 100, 'value': 42}, {'key': 5, 'value': 43}]
map_extractReturns a list containing the value for the given key or an empty list if the key is not present in the map.
map_extract(map, key)
SELECT map_extract(MAP {'a': 1, 'b': NULL, 'c': 3}, 'a');
----
[1]
SELECT map_extract(MAP {1: 'one', 2: 'two'}, 2);
----
['two']
SELECT map_extract(MAP {'x': 10, 'y': NULL, 'z': 30}, 'y');
----
[]
map_keysReturns a list of all keys in the map.
map_keys(map)
SELECT map_keys(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[a, b, c]
SELECT map_keys(map([100, 5], [42, 43]));
----
[100, 5]
map_valuesReturns a list of all values in the map.
map_values(map)
SELECT map_values(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[1, , 3]
SELECT map_values(map([100, 5], [42, 43]));
----
[42, 43]
digestComputes the binary hash of an expression using the specified algorithm.
digest(expression, algorithm)
> select digest('foo', 'sha256');
+------------------------------------------------------------------+
| digest(Utf8("foo"),Utf8("sha256")) |
+------------------------------------------------------------------+
| 2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae |
+------------------------------------------------------------------+
md5Computes an MD5 128-bit checksum for a string expression.
md5(expression)
> select md5('foo');
+----------------------------------+
| md5(Utf8("foo")) |
+----------------------------------+
| acbd18db4cc2f85cedef654fccc4a4d8 |
+----------------------------------+
sha224Computes the SHA-224 hash of a binary string.
sha224(expression)
> select sha224('foo');
+----------------------------------------------------------+
| sha224(Utf8("foo")) |
+----------------------------------------------------------+
| 0808f64e60d58979fcb676c96ec938270dea42445aeefcd3a4e6f8db |
+----------------------------------------------------------+
sha256Computes the SHA-256 hash of a binary string.
sha256(expression)
> select sha256('foo');
+------------------------------------------------------------------+
| sha256(Utf8("foo")) |
+------------------------------------------------------------------+
| 2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae |
+------------------------------------------------------------------+
sha384Computes the SHA-384 hash of a binary string.
sha384(expression)
> select sha384('foo');
+--------------------------------------------------------------------------------------------------+
| sha384(Utf8("foo")) |
+--------------------------------------------------------------------------------------------------+
| 98c11ffdfdd540676b1a137cb1a22b2a70350c9a44171d6b1180c6be5cbb2ee3f79d532c8a1dd9ef2e8e08e752a3babb |
+--------------------------------------------------------------------------------------------------+
sha512Computes the SHA-512 hash of a binary string.
sha512(expression)
> select sha512('foo');
+----------------------------------------------------------------------------------------------------------------------------------+
| sha512(Utf8("foo")) |
+----------------------------------------------------------------------------------------------------------------------------------+
| f7fbba6e0636f890e56fbbf3283e524c6fa3204ae298382d624741d0dc6638326e282c41be5e4254d8820772c5518a2c5a8c0c7f7eda19594a7eb539453e1ed7 |
+----------------------------------------------------------------------------------------------------------------------------------+
Functions to work with the union data type, also know as tagged unions, variant types, enums or sum types. Note: Not related to the SQL UNION operator
union_extractReturns the value of the given field in the union when selected, or NULL otherwise.
union_extract(union, field_name)
❯ select union_column, union_extract(union_column, 'a'), union_extract(union_column, 'b') from table_with_union;
+--------------+----------------------------------+----------------------------------+
| union_column | union_extract(union_column, 'a') | union_extract(union_column, 'b') |
+--------------+----------------------------------+----------------------------------+
| {a=1} | 1 | |
| {b=3.0} | | 3.0 |
| {a=4} | 4 | |
| {b=} | | |
| {a=} | | |
+--------------+----------------------------------+----------------------------------+
union_tagReturns the name of the currently selected field in the union
union_tag(union_expression)
❯ select union_column, union_tag(union_column) from table_with_union;
+--------------+-------------------------+
| union_column | union_tag(union_column) |
+--------------+-------------------------+
| {a=1} | a |
| {b=3.0} | b |
| {a=4} | a |
| {b=} | b |
| {a=} | a |
+--------------+-------------------------+
arrow_castCasts a value to a specific Arrow data type.
arrow_cast(expression, datatype)
arrow_typeof]> select
arrow_cast(-5, 'Int8') as a,
arrow_cast('foo', 'Dictionary(Int32, Utf8)') as b,
arrow_cast('bar', 'LargeUtf8') as c;
+----+-----+-----+
| a | b | c |
+----+-----+-----+
| -5 | foo | bar |
+----+-----+-----+
> select
arrow_cast('2023-01-02T12:53:02', 'Timestamp(µs, "+08:00")') as d,
arrow_cast('2023-01-02T12:53:02', 'Timestamp(µs)') as e;
+---------------------------+---------------------+
| d | e |
+---------------------------+---------------------+
| 2023-01-02T12:53:02+08:00 | 2023-01-02T12:53:02 |
+---------------------------+---------------------+
arrow_metadataReturns the metadata of the input expression. If a key is provided, returns the value for that key. If no key is provided, returns a Map of all metadata.
arrow_metadata(expression[, key])
> select arrow_metadata(col) from table;
+----------------------------+
| arrow_metadata(table.col) |
+----------------------------+
| {k: v} |
+----------------------------+
> select arrow_metadata(col, 'k') from table;
+-------------------------------+
| arrow_metadata(table.col, 'k')|
+-------------------------------+
| v |
+-------------------------------+
arrow_typeofReturns the name of the underlying Arrow data type of the expression.
arrow_typeof(expression)
> select arrow_typeof('foo'), arrow_typeof(1);
+---------------------------+------------------------+
| arrow_typeof(Utf8("foo")) | arrow_typeof(Int64(1)) |
+---------------------------+------------------------+
| Utf8 | Int64 |
+---------------------------+------------------------+
get_fieldReturns a field within a map or a struct with the given key.
Supports nested field access by providing multiple field names.
Note: most users invoke get_field indirectly via field access
syntax such as my_struct_col['field_name'] which results in a call to
get_field(my_struct_col, 'field_name').
Nested access like my_struct['a']['b'] is optimized to a single call:
get_field(my_struct, 'a', 'b').
get_field(expression, field_name[, field_name2, ...])
> -- Access a field from a struct column
> create table test( struct_col) as values
({name: 'Alice', age: 30}),
({name: 'Bob', age: 25});
> select struct_col from test;
+-----------------------------+
| struct_col |
+-----------------------------+
| {name: Alice, age: 30} |
| {name: Bob, age: 25} |
+-----------------------------+
> select struct_col['name'] as name from test;
+-------+
| name |
+-------+
| Alice |
| Bob |
+-------+
> -- Nested field access with multiple arguments
> create table test(struct_col) as values
({outer: {inner_val: 42}});
> select struct_col['outer']['inner_val'] as result from test;
+--------+
| result |
+--------+
| 42 |
+--------+
versionReturns the version of DataFusion.
version()
> select version();
+--------------------------------------------+
| version() |
+--------------------------------------------+
| Apache DataFusion 42.0.0, aarch64 on macos |
+--------------------------------------------+