Arithmetic
| Function | Description | Example |
|---|---|---|
| abs(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the absolute value of 'numeric'. | abs(2) = 2 abs(-4.25) = 4.25 |
| acos(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the arc cosine of 'numeric'. | acos(1) = 0.0 acos(0) = 1.5707963267948966 acos(-1) = 3.141592653589793 |
| acosd(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the arc cosine of 'numeric' in units of degrees. | acosd(1) = 0.0 acosd(0) = 90.0 acosd(-1) = 180.0 |
| asin(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the arc sine of 'numeric' in units of degrees. | asin(0.5) = 0.5235987755982989 asin(0) = 0.0 asin(-0.5) = -0.5235987755982989 |
| asind(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the arc sine of 'numeric' in units of degrees. | asind(0.5) = 30.000000000000004 asind(0) = 0.0 asind(-0.5) = -30.000000000000004 |
| atan(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the arc tangent of 'numeric'. | atan(1) = 0.7853981633974483 atan(0) = 0.0 atan(-1) = -0.7853981633974483 |
| atan2(Numeric numeric) | Return "" if 'numericx' or 'numericy' is NULL; Return the arc tangent of a coordinate ('numericx', 'numericy'). | atan2(1, 1) = 0.7853981633974483 atan2(1, 0) = 1.5707963267948966 atan2(0, -1) = 3.141592653589793 |
| atan2d(Numeric numericx, Numeric numericy) | - Return "" if 'numericx' or 'numericy' is NULL; - Return inverse tangent of 'numericy'/'numericx', result in degrees. | atan2d(1, 1) = 45.0 atan2d(1, 0) = 90.0 atan2d(0, -1) = 180.0 |
| atand(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the arc tangent of 'numeric' in units of degrees. | atand(1) = 45.0 atand(0) = 0.0 atand(-1) = -45.0 |
| bin(Integer integer) | - Return "" if 'integer' is NULL; - Return a string representation of 'integer' in binary format. | bin(4) = 100 |
| cbrt(Numeric numeric) | - Return NULL if 'numeric' is NULL; - Return cube root. | cbrt(5) = 1.7099759466766968 cbrt(27) = 3.0 |
| ceil(Numeric numeric) | - Return NULL if 'numeric' is NULL; - Return the smallest number that is greater than or equal to 'numeric'. | ceil(-5.67) = -5.0 ceil(1.23) = 2.0 |
| chr(Integer integer) | - Return NULL if 'integer' is NULL; - Return the ASCII character having the binary equivalent to 'integer'. | chr(97) = "a" chr(353) = "a" |
| cos(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the cosine of 'numeric'. | cos(0) = 1.0 |
| cosd(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the cosine of 'numeric' in units of degrees. | cosd(15) = 0.9659258262890683 |
| cosh(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the hyperbolic cosine of 'numeric'. | cosh(1) = 1.543080634815244 cosh(0) = 1.0 cosh(-1) = 1.543080634815244 |
| cot(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the cotangent of the 'numeric' (in radians). | cot(1) = 0.6420926159343306 cot(0.5) = 1.830487721712452 cot(-1) = -0.6420926159343306 |
| cotd(Numeric numeric) | - Return "" if numeric is NULL; - Return the cotangent of numeric in units of degrees. | cotd(0) = "" cotd(45) = 1.0000000000000002 cotd(-1) = -57.28996163075943 |
| e() | - Return the mathematical constant. | e() = 2.718281828459045 |
| erf(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the error function value of the input value. | erf(3) = 0.9999779095030014 |
| erfc(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return complementary error (1 - erf('numeric'), without loss of precision for large inputs). | erfc(1) = 0.15729920705028488 |
| exp(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return e raised to the power of 'numeric'. | exp(2) = 7.38905609893065 |
| factorial(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the factorial of a non-negative. | factorial(5) = 120 factorial(0) = 1 |
| fibonacci(Numeric numeric) | - Return NULL if 'numeric' is NULL; - Returns the nth Fibonacci number | fibonacci(0) = 0 fibonacci(1) = 1 fibonacci(2) = 1 fibonacci(3) = 2 fibonacci(4) = 3 |
| floor(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the largest number that is less than or equal to 'numeric'. | floor(1.23) = 1.0 floor(-5.67) = -6.0 |
| gcd(Numeric numeric1,Numeric numeric2) | - Return "" if any parameter is NULL; - Return 0 if both inputs are zero; - Return greatest common divisor (the largest positive number that divides both inputs with no remainder). | gcd(3.141,3.846) = 0.003 |
| greatest(Numeric value1 [,Numeric value2,...]) | - Return "" if any parameter is NULL; - Return the greatest value of the list of arguments. | greatest(3.141592653589793, 3, greatest(4, 1)) = 4 |
| hex(String dataStr) | - Return "" if dataStr is NULL; - Return the string obtained by converting the dataStr to hexadecimal if 'dataStr' can be parsed into numeric; - Return the string obtained by converting the ASCII code corresponding to each character to hexadecimal otherwise. | hex(1007) = "3EF" hex('abc') = "616263" |
| lcm(Numeric numeric1,Numeric numeric2) | - Return "" if any parameter is NULL; - Return 0 if either input is zero; - Return least common multiple (the smallest strictly positive number that is an integral multiple of both inputs). | lcm(6,3) = 6 lcm(3.141,3.846) = 4026.762 |
| least(Numeric value1 [, Numeric value2, ...]) | - Return "" if any parameter is NULL; - Return the least value of the list of arguments. | least(3.14, least(7, 2, 1)) = 1 |
| ln(Numeric numeric) | - Return "" if numeric is NULL; - Return the natural logarithm (base e) of numeric. | ln(10) = 2.302585092994046 |
| log(Numeric numeric1 [, Numeric numeric2]) | - Return the natural logarithm of 'numeric1' when called with one argument; - Return the logarithm of 'numeric2' to the base 'numeric1' when called with two arguments. | log(1) = 0.0 log(2,8) = 3.0 |
| log10(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the base 10 logarithm of 'numeric'. | log10(1000) = 3.0 |
| log2(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the base 2 logarithm of 'numeric'. | log2(32) = 5 |
| min_scale(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely. | min_scale(3.1415000000000) = 4 |
| mod(Numeric numeric1, Numeric numeric2) | - Return "" if any parameter is null; - Return the remainder of 'numeric1' divided by 'numeric2'. | mod(3,2) = 1 mod(-3.1415926,100) = -3.1415926 |
| num_nonnulls([Expr expr1, Expr expr2, ...]) | - Return the number of non-null arguments. | num_nonnulls(5, 3, null, null) = 2 |
| num_nulls([Expr expr1, Expr expr2, ...]) | - Return the number of null arguments. | num_nulls(5, null, null, null) = 3 |
| pi() | - Return the mathematical constant PI. | pi() = 3.141592653589793 |
| power(Numeric numeric1, Numeric numeric2) | - Return "" if any parameter is NULL. - Return 'numeric1'.power('numeric2'). | power(4,0.5) = 2.0 |
| radians(Numeric x) | - Return "" if 'x' is NULL; - Return radians of 'x', Convert degrees to radians. | radians(18.97) = 0.33108895910332425 |
| radix_convert(Numeric numeric) | - Return "" if any of its arguments are NULL; - Return the result of converting 'numeric' from 'from_base' to 'to_base'. Note: abs(base) between [2,36].'from_base' is a negative number, 'numeric' is regarded as a signed number. Otherwise, 'numeric' is treated as unsigned. This function works with 64-bit precision. | radix_convert('6E',18,8) = 172 |
| rand(Integer seed) | - Return a pseudo-random double precision value in the range [0.0, 1.0) if seed is NULL; - Return a pseudo-random double precision value in the range [0.0, 1.0) with an initial 'seed' of Integer. | rand(1) rand() |
| rand_integer(Integer INT1, [Integer INT2]) | - Return a pseudorandom integer value in the range [0, 'INT1') if 'INT2' is NULL; - Return a pseudorandom integer value in the range [0, 'INT1') with an initial seed 'INT2'. Note: Two RAND_INTEGER functions will return idential sequences of numbers if they have the same initial seed and bound. | rand_integer(10) rand_integer(88, 89) |
| round(String str) | - Return "" if 'x' is NULL; - Return the nearest integer to 'x', with optional parameter 'y' indicating the number of decimal places to be rounded. | round(3.5) = 4 round(3.14159265358979323846,10) = 3.1415926536 |
| scale(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return scale of the argument (the number of decimal digits in the fractional part). | scale(3.1415000000000) = 13 |
| sign(Numeric x) | - Return "" if 'x' is NULL; - Return -1 if 'x' is a negative number; - Return 0 if 'x' is equal to 0; - Return 1 if 'x' is a positive number. | sign(-3.5) = -1 |
| sin(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the sine of 'numeric'. | sin(0) = 0.0 |
| sind(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the sine of 'numeric' in units of degrees. | sind(15) = 0.25881904510252074 |
| sinh(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the hyperbolic sine of 'numeric'. | sinh(1) = 1.1752011936438014 |
| sqrt(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the square root of 'numeric'. | sqrt(9) = 3.0 |
| tan(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the tangent of 'numeric'. | tan(1) = 1.5574077246549023 |
| tand(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the tangent of 'numeric' in units of degrees. | tand(15) = 0.2679491924311227 |
| tanh(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the hyperbolic tangent of 'numeric'. | tanh(1) = 0.7615941559557649 |
| trim_scale(Numeric numeric) | - Return "" if 'numeric' is NULL; - Return the result of reducing the proportion of values (decimal places) by removing trailing zeros. | trim_scale(3.1415000000000) = 3.1415 |
| truncate(Numeric numeric, Integer cnt) trunc(Numeric numeric, Integer cnt) | - Return "" if any parameter is NULL; - Return the result of truncating 'numeric' values to 'cnt' decimal places. | truncate(42.324, 2) = 42.32 trunc(42.324, -1) = 40 |
Collection
| Function | Description | Example |
|---|---|---|
| array(String value1 [,String value2, ....]) | - Return an array created from a list of values ('value1', 'value2', ....). | array('he',7,'xxd') = [he, 7, xxd] array(array('he',5),'xxd') = return [[he, 5], xxd] array(array('he',5),array('','')) = return [[he, 5], [, ]] |
| array_append(Array array, Object element) | - Return "" if either argument is NULL; - Return the result of appends an element to the end of the 'array'. | array_append(array('he',7,'xxd'), 'cloud') = [he, 7, xxd, cloud] |
| array_concat([Array array1, Array array2, ...]) | - Return "" if any input array is NULL; - Return an array that is the result of concatenating at least one array. | array_concat(array('he',7),array('xxd', 'cloud')) = [he, 7, xxd, cloud] |
| array_contains(Array array, Object needle) | - Return "" if 'array' is NULL; - Return whether the given element exists in 'array'. Note: Checking for null elements in the array is supported. | array_contains(array('he',7,'xxd'), 'cloud') = false array_contains(array('he',-1,''),'') = true |
| array_distinct(Array array) | - Return "" if 'array' is NULL; - Return an array with unique elements. | array_distinct(array('he',-1,'he')) = [he, -1] |
| array_except(Array array1, Array array2) | - Return "" if any parameter is null; - Return an ARRAY that contains the elements from 'array1' that are not in 'array2', without duplicates; - Return an empty ARRAY if no elements remain after excluding the elements in 'array2' from 'array1'. | array_except(array('he',7,'xxd'),array('he')) = [7, xxd] array_except(array('he',7,'xxd'),array('cloud')) = [he, 7, xxd] |
| array_intersect(Array array1, Array array2) | - Return "" if any parameter is null; - Return an ARRAY that contains the elements from 'array1' that are not in 'array2', without duplicates; - Return an empty ARRAY if no elements remain after excluding the elements in 'array2' from 'array1'. | array_intersect(array('he',7,'xxd'),array('he')) = [he] array_intersect(array('he',7,'xxd'),array('cloud')) = [] |
| array_join(Array array, String delimiter[, String nullReplacement]) | - Return "" if any parameter is null; - Return a string indicating that the elements in the given 'array' are concatenated using a 'delimiter', and empty array elements are filled with 'nullReplacement'. - Note: If nullReplacement is not specified, null elements in the array will be omitted from the resulting string. | array_join(array('he',7,'xxd'),'~') = he~7~xxd array_join(array('he',3,''),'~','oo') = he~3~oo |
| array_max(Array array) | - Return "" if any parameter is null; - Return the maximum value from the 'array'. | array_max(array(4,3,56)) = 56 |
| array_min(Array array) | - Return "" if any parameter is null; - Return the minimum value from the 'array'. | array_min(array(4,3,56)) = 3 |
| array_position(Array array,Object element) | - Return "" if any parameter is null; - Return the position of the first occurrence of 'element' in the given 'array' as int (starts from 1); - Return 0 if the given value could not be found in the 'array'. | arrayposition(array('he',7,'xxd'),'he') = 1 array_position(array('he',7,''),'') = 0 |
| array_prepend(Array array,Object element) | - Return "" if 'array' is null; - Return the result of appending an element to the beginning of the array. | array_prepend(array(4,3),3) = [3, 4, 3] |
| array_remove(Array array,Object element) | - Return "" if 'array' is null; - Return the result of removing all elements that equal to 'element' from 'array'. | array_remove(array('he',7,'xxd'),'he') = [7, xxd] |
| array_reverse(Array array) | - Return "" if 'array' is null; - Return an array in reverse order. | array_reverse(array('he',7,'xxd')) = [xxd, 7, he] |
| array_slice(Array array, Integer start_offset[, Integer end_offset]) | - Return "" if 'array' or 'start_offset' is null; - Return a subarray of the input 'array' between 'start_offset' and 'end_offset' inclusive; - Return an empty array if 'start_offset' is after 'end_offset' or both are out of 'array' bounds. Note: If 'end_offset' is omitted then this offset is treated as the length of the 'array'. Positive values are counted from the beginning of the array while negative from the end. | array_slice(array('he',7,'xxd'),1,2) = ['he', 7] array_slice(array('he','xxd','b'),-2,-1) = [3, 'xxd'] |
| array_sort(Array array[,Boolean ascending_order[,Boolean null_first]]) | - Return "" if 'array' is null; - Return the array in sorted order. Note: The function sorts an array, defaulting to ascending order with NULLs at the start when only the array is input. Specifying 'ascending_order' as true orders the array in ascending with NULLs first, and setting it to false orders it in descending with NULLs last. Independently, 'null_first' as true moves NULLs to the beginning, and as false to the end, irrespective of the sorting order. | array_sort(array('he',7,'xxd')) = [7, he, xxd] array_sort(array(3,7,5)) = [3, 5, 7] array_sort(array(,3,7),false,false) = [7, 3, ] array_sort(array(3,7,),true,false) = [3, 7, ] |
| array_union(Array array1[, Array array2, ...]) | - Return "" if any input array is NULL; - Return an array that is the result of concatenating at least one array. | array_concat(array('he',7),array('xxd', 'cloud')) = [he, 7, xxd, cloud] |
| cardinality(Object input) | - Return "" if the input is NULL; - Return the number of elements in array if the input is array; - Return the number of entries in map if the input is map. | cardinality(array('he',7,'xxd')) = 3 cardinality(map('he',7,'xxd',3)) = 2 |
| element(Array array) | - Return "" if 'array' is empty or NULL; - Return the sole element of 'array' (whose cardinality should be one). Note: Throws an exception if array has more than one element. | element(array('he')) = he |
| find_in_set(String str,String strList) | - Return "" if either argument is NULL; - Return 0 if 'str' is not in 'strList' or if 'strList' is the empty string; - Return a value in the range of 1 to N if the string 'str' is in the string list 'strList' consisting of N substrings. Note: strList is a string composed of substrings separated by ',' characters. This function does not work properly if the first argument contains a comma (,) character. | FIND_IN_SET('b','a,b,b,c,d') = 2 FIND_IN_SET('','a,,b,c,d') = 2 |
| map([String value1, String value2, ...]) | - Return "" if the number of parameters is not even; - Return a map created from a list of key-value pairs ((value1, value2), ... ). | Map('he',7,'xxd') = null Map('he',1,'xxd','cloud') = {he=1, xxd=cloud} Map('xxd','cloud',map(1,2),map(3,'apple')) = {xxd=cloud, {1=2}={3=apple}} |
| map_entries(Map map) | - Return "" if 'map' is NULL; - Return an array of all entries in the given 'map'. | map_entries(Map('he',1,'xxd','cloud')) = [he=1, xxd=cloud] map_entries(Map(1,2,'cloud','xxd')) = [xxd=cloud, 1=2] |
| map_from_arrays(Array array_of_keys, Array array_of_values) | - Return "" if any parameter is NULL; - Return a map created from an arrays of keys and values. | map_from_arrays(array('he', 'xxd'),array(1, 3)) = {he=1, xxd=3} map_from_arrays(array('xxd', array('cloud')),array(1, array(2))) = {1=xxd, [2]=[cloud]} |
| map_keys(Map map) | - Return "" if 'map' is NULL; - Return the keys of the 'map' as array. No order guaranteed. | map_keys(Map('he',1,'xxd','cloud')) = [he, xxd] map_keys(Map('xxd','cloud',map(1,2),map(3,'apple'))) = [xxd, {1=2}] |
| map_union([Map map1, Map map2, ...]) | - Return "" if any of maps is null; - Return a map created by merging at least one map.These maps should have a common map type, the following map will overwrite the previous one. | map_union(map('he', 1),map('xxd', 3)) = {he=1, xxd=3} map_union(map('he', 1),map('he', 3)) = {he=3} |
| map_values(Map map) | - Return "" if 'map' is null; - Return the values of the 'map' as array. No order guaranteed. | map_values(Map('he',1,'xxd','cloud')) = [1, cloud] map_values(Map('xxd','cloud',map(1,2),map(3,'apple'))) = [cloud, {3=apple}] |
Compression
| Function | Description | Example |
|---|---|---|
| compress(String string_to_compress [, String compress_type]) | - Return "" if 'string_to_compress' is NULL; - Return "" if 'string_to_compress' is ""; - Return the result as a binary string. Note: This function supports three compression algorithms: deflater, gzip, and zip. 'compress_type' defaults to defer. In addition, in order to output the compressed results in the form of strings, this method uses the ISO_8859_1 character set. | length(compress(replicate(string1,100)),'ISO_8859_1') = 33 length(compress(''),'ISO_8859_1') = 0 |
| uncompress(String string_to_uncompress, String compress_type) | - Return "" if 'string_to_uncompress' is NULL; - Return "" if 'string_to_uncompress' is ""; - Return the result as a string. Note: This function supports three compression algorithms: deflater, gzip and zip. 'compress_type' defaults to defer. | uncompress(compress('inlong')) = "inlong" |
Condition
| Function | Description | Example |
|---|---|---|
| coalesce(String value1 [, String value2, ...]) | - Return "" If all arguments are NULL or ""; - Return the first argument that is not NULL or "". Note: The return type is the least restrictive, common type of all of its arguments. The return type is nullable if all arguments are nullable as well. | coalesce('', 'SQL', 'hh') = "SQL" |
| if(Expr expr1,Expr expr2,Expr expr3) | - Return 'expr2' if 'expr1' returns true; - Return 'expr3' otherwise. | if(1 = 1,true,false) = true |
| ifnull(Expr expr1,Expr expr2) if_null(Expr expr1,Expr expr2) | - Return 'expr1' if 'expr1' returns not NULL; - Return 'expr2' otherwise. | ifnull(null, 3) = 3 ifnull(6,'YES') = 6 |
| isnull(Expr expr) | - Return true if 'expr' is NULL; - Return false otherwise. | isnull(5 + 3) = false isnull(5 / 0) = true |
| nullif(Expr expr1, Expr expr2) null_if(Expr expr1, Expr expr2) | - Return "" if 'expr1' = 'expr2' is true; - Return 'expr1' otherwise. | nullif(5, 3) = 5 |
Encryption
| Function | Description | Example |
|---|---|---|
| dayu_decrypt | ||
| decode(Binary binary,String charsetStr) | - Return "" if any parameter is NULL; - Return the result of encoding 'binary' using the character set specified by 'charsetStr'. Note: 'charsetStr' is one of ('US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). | decode(encode('Hello','UTF-8'),'UTF-8') = "Hello" |
| encode(String strInfo,String charsetStr) | - Return "" if any parameter is NULL; - Return the result of encoding 'strInfo' using the character set specified by 'charsetStr'. - Note: 'charsetStr' is one of ('US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). | decode(encode('Hello','UTF-8'),'UTF-8') = "Hello" |
| from_base64(String base64Str) | - Return "" if 'base64Str' is NULL; - Return the base64-decoded result from 'base64Str'. | from_base64('QXBhY2hlIEluTG9uZw==') = "Apache InLong" |
| md5(String string) | - Return "" if the 'string' is NULL; - Return the MD5 hash value of 'string' in the form of a 32-bit hexadecimal digit string. | md5("") = "d41d8cd98f00b204e9800998ecf8427e" md5("1") = "c4ca4238a0b923820dcc509a6f75849b" |
| sha(String str) | - Return "" if 'str' is NULL; - Return a string of 40 hexadecimal digits (the SHA-1 160 bit). | sha("5") = "ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4" |
| sha2(String str, Integer hash_length) | - Return "" if either argument is NULL or the 'hash_length' is not one of (224,256,384,512); - Return scale of the argument (the number of decimal digits in the fractional part). | sha2("5",224) = "b51d18b551043c1f145f22dbde6f8531faeaf68c54ed9dd79ce24d17" |
| to_base64(String str) | - Return "" if 'str' is NULL; - Return the base64-encoded result from 'str'. | to_base64('app-fun') = "YXBwLWZ1bg==" |
Json
| Function | Description | Example |
|---|---|---|
| json_array_append(String json_doc, String path1, String val1[,String path2, String val2, ...]) | - Return "" if any argument is NULL; - Return the result of appends values to the end of the indicated arrays within a JSON document. | json_array_append(["a", ["b", "c"], "d"],$[0],2,$[1],3) = [["a","2"],["b","c","3"],"d"] |
| json_array_insert(String json_doc, String path1, String val1[, String path2, String val2, ...] ) | - Return "" if any argument is NULL; - Return the 'json_doc' inserted into the array. Note: If multiple groups of parameters are passed in, the parameter subscripts of the latter groups need to be based on the document subscripts after the previous group of parameters are updated. | json_array_append(["a", {"b": [1, 2]}, [3, 4]], $[1], x) = ["a","x",{"b":[1,2]},[3,4]] |
| json_arrays([String value1, String value2, ...]) | - Return a JSON array string constructed from a list of values. | JSON_ARRAYS() = [] JSON_ARRAYS(1, '2') = [1,"2"] JSON_ARRAYS(JSON_ARRAY(1)) = [[1]] |
| json_exists(String json_doc, String path) | - Return true if 'json_doc' satisfies a given 'path' search criterion; - Return false otherwise. | JSON_EXISTS('{"a": true}', '$.a') = true JSON_EXISTS('{"a": true}', '$.b') = false JSON_EXISTS('{"a": [{ "b": 1 }]}', '$.a[0].b') = true |
| json_insert(String json_doc, String path1, String val1[, String path2, String val2, ...] ) | - Return "" if any argument is NULL or the 'json_doc' argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.; - Return the result of inserting data into 'json_doc'. | json_insert({"a": {"b": [1, 2]}, "c": [3, 4]}, $.c[1][1], "2", "$.c[1][1][5]", "1") = {"a":{"b":[1,2]},"c":[3,[4,["2","1"]]]} |
| json_query(String json_doc, String path) | - Return "" if any parameter is NULL; - Return the string parsed from the 'path' in 'json_doc'. | json_query({\"people\": [{\"name\": \"Alice\"}, {\"name\": \"Bob\"}]}, $.people) = [{"name":"Alice"},{"name":"Bob"}] json_query({\"list\": [null, {\"name\": \"John\"}]}, $.list[1].name) = John |
| json_quote(String data) json_string(String data) | - Return "" if data is NULL; - Return a valid JSON string converted from a string (JSON_QUOTE) or any type of data (JSON_STRING). Note: JSON_QUOTE will escape interior quote and special characters (’"’, ‘', ‘/’, ‘b’, ‘f’, ’n’, ‘r’, ’t’) | json_quote('Column1\tColumn2) = \"Column1\tColumn2\" json_string(true) = "true" |
| json_remove(String json_doc, String path1[, String path2, ...]) | - Return "" if any argument is NULL or the 'json_doc' argument is not a valid JSON document or any path argument is not a valid path expression or is $ or contains a * or ** wildcard; - Return the result of removing data from 'json_doc'. | json_remove("{\"name\":\"Charlie\",\"hobbies\":[[\"swimming1\",\"swimming2\"], \"reading\",\"coding\"]}","$.age") = {"hobbies":[["swimming2"],"coding"],"name":"Charlie"} |
| json_replace(String json_doc, String path1, String val1[, String path2, String val2, ...] ) | - Return "" if any argument is NULL or the 'json_doc' argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard; - Return the result of replacing existing values in 'json_doc'. | json_replace("{ \"a\": 1, \"b\": [2, 3]}", "$.a", 10, "$.c", "[true, false]") = {"a": 10, "b": [2, 3]} |
| json_set(String json_doc, String path1, String val1[, String path2, String val2, ...] ) | - Return "" if any argument is NULL or the 'json_doc' argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard; - Return the result of inserting or updating data in 'json_doc'. | json_set({\"name\":\"Alice\"},"$.name","inlong") = {"name":"inlong"} |
| json_unquote(String data) | - Return "" if data is NULL; - Return the 'data' unmodified if the value does not start and end with double quotes or if it starts and ends with double quotes but is not a valid JSON string literal. Note: JSON_UNQUOTE will unescapes escaped special characters ('"', '', '/', 'b', 'f', 'n', 'r', 't') | json_unquote('Hello, World!') = "Hello, World!" json_unquote('Complex string with / and \') = "Complex string with / and \" |
| json_value(String json_doc,String path) | - Return "" if any parameter is NULL; - Return the scalar extracted from JSON string ('json_doc') based on 'path'. | json_value({"a": 1}, $.a) = 1 json_value({\"person\": {\"name\": \"Alice\" ,\"age\": 30}}, $.person.name) = Alice |
String
| Function | Description | Example |
|---|---|---|
| ascii(String str) | - Return "" if 'str' is NULL; - Return the numeric value of the first character of 'str'. | ascii('abc') = 97 ascii('A') = 65 ascii(null) = "" |
| bit_length(String str,[String charset]) | - Return "" if the 'str' is NULL; - Return number of bits in 'str'. Note: Charset is aligned with the JVM by default. | bit_length("hello world") = 88 bit_length("hello 你好","utf-8") = 96 |
| char_length(String str) | - Return "" if 'str' is NULL; - Return the character length of 'str'. | char_length('hello world') = 11 case2: char_length('应龙') = 2 |
| concat(String string1 [, String string2, ...]) | - Return NULL If any parameter is NULL; - Return the string of the connection ('string1', 'string2',...). | CONCAT("AA", "BB", "CC") = "AABBCC" |
| concat_ws(String string1 [, String string2, ...]) | - Return NULL If 'STRING1' is NULL; - Return a string that concatenates ('STRING2', 'STRING3', ...) with a separator STRING1. | concat_ws('-', 'apple', 'banana', 'cloud') = "apple-banana-cloud" concat_ws('-', 'apple', '', 'cloud') = "apple--cloud" concat_ws('-', 'apple', null, 'cloud') = "apple-cloud" |
| contains(String leftStr , String rightStr) | - Return "" if 'leftStr' or rightStr is NULL; - Return True if 'rightStr' is found inside 'leftStr'; - Return False otherwise. | contains('Transform SQL', 'SQL') = true |
| elt(Integer index, Expr expr1[, Expr expr2, ...]) | - Return "" if 'index' is NULL or out of range; - Return the index-th('index' starts from 1) expression. | elt(2, 'a', 'b', 'c') = "b" |
| endswith(String s1, String s2) | - Return "" if either argument is NULL; - Return whether 's2' ends with 's2'. | endswith('Apache InLong', 'Long') = true |
| format(Numeric X,Integer D) | - Return "" if 'X' or 'D' is NULL; - Return the result of formatting the number 'X' to "#,###,###.##" format, rounded to 'D' decimal places. | FORMAT(12332.123456, 4) = "12,332.1235" FORMAT(12332.2,0) = "12,332" |
| initcap(String s1, String s2) init_cap(String s1, String s2) | - Return "" if 'str' is NULL; - Return a new form of 'str' with the first character of each word converted to uppercase and the rest characters to lowercase. | initcap('hello world') = "Hello world" |
| insert(String str,Integer pos,Integer len,String newStr) | - Return "" If any parameter is NULL; - Return the result of replacing the substring of length len with 'newStr' starting from the given position 'pos' in 'str'. Note: If the position is out of the string's bounds, the original string is returned.If the length exceeds the remaining length of the string from the given position, the replacement continues to the end of the string. If any argument is null, the function returns null. | INSERT('12345678', 3, 4, 'word') = '12word78' INSERT('12345678', -1, 4, 'word') = '12345678' INSERT('12345678', 3, 100, 'word') = '12word' |
| is_alpha(String str) | - Return "" If 'str' is NULL; - Return true if all characters in 'str' are letter; - Return false otherwise (Including cases where string is null and ''). | is_alpha('inlong') = true is_alpha('inlong~') = false |
| is_decimal(String str) | - Return "" if 'str' is NULL; - Return true if 'str' can be parsed to a valid numeric; - Return false otherwise (Including cases where string is null and ''). | is_decimal('3he') = false is_decimal('3.5') = true |
| is_digit(String str) | - Return "" If 'str' is NULL; - Return true if all characters in 'str' are digit; - Return false otherwise (Including cases where 'str' is null and ''). | is_digit('3.5') = false |
| left(String str, Integer len) | - Return "" if any parameter is NULL; - Return "" if 'len' is less than or equal to zero; - Return a substring of len starting from the right side of the 'str'. | left('hello world',100) = "hello world" left('hello world',-15) = "" |
| length(String str, String charset) | - Return "" if 'str' is NULL; - Return the byte length of the 'str'. Note: charset defaults to matching with JVM. | length(应龙, utf-8) = 6 length('hello world') = 11 |
| locate(String str1, String str2, Integer pos) instr(String str1, String str2, Integer pos) | - Return "" if any of arguments is NULL' - Return 0 if not found' - Return the position of the first occurrence of 'str1' in 'str2' after position 'pos'. | locate('app', 'apple') = 1 locate('app', 'appapp', 2) = 4 |
| lower(String str) lcase(String str) | - Return "" if 'str' is NULL; - Return the string obtained by converting all letters of 'str' to lowercase letters. | lower("ApPlE") = "apple" |
| lpad(String str1, Integer len, String str2) | - Return null if any of the three parameters is null or 'len' is less than 0; - Return the substring of 'str1' with subscripts in the range of [0, 'len') if 'len' is less than or equal to the length of 'str1'; - Return "" if 'len' is longer than the length of 'str1' and 'str2' is ""; - Return the filled string if 'str2' is not "". | lpad('he',7,'xxd') = "xxdxxhe" lpad('he',1,'') = "h" |
| ltrim(String str) | - Return "" if 'str' is NULL; - Return the string 'str' without leading spaces. | ltrim(' in long ') = "in long " |
| parse_url(String urlStr, String keyword[, String parameter]) | - Return NULL any required parameter is NULL; - Return the specified part from the 'urlStr'. Valid values for 'keyword'. Note: The 'keyword' is one of ('HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'AUTHORITY', 'FILE', 'USERINFO'). Also a value of a particular key in QUERY can be extracted by providing the key as the third argument. | parse_url('http://inlong.apache.org/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') = v1 parse_url('http://inlong.apache.org/p.php?k1=v1&k2=v2#Ref1', 'PROTOCOL') = http parse_url('k1=v1&k2=v2', 'QUERY','k2') = v2 |
| printf(String strfmt [, Object obj, ...]) | - Return a formatted string from printf-style format strings. | printf("User %s has %d points and a balance of %.2f.", "Bob", 1500, 99.99) = " User Bob has 1500 points and a balance of 99.99." |
| regex(String str, String regexp) similar(String str, String regexp) regexp_like(String str, String regexp) | - Return "" if any of the arguments are NULL or invalid; - Return TRUE if any (possibly empty) substring of 'str' matches the Java regular expression 'regexp'. | regexp("The quick brown fox", "quick") = true regexp("The quick brown fox", "cold") = false |
| regex_substr(String source_string, String regexp) | - Return "" if any of the arguments are NULL or regexp if invalid or pattern is not found; - Return the first substring in 'str' that matches 'regexp'. | regex_substr("abc123def", "(\d+)") = 123 |
| regexp_count(Integer INT1, [Integer INT2]) | - Return "" if any of the arguments are NULL or 'regexp' is invalid; - Return the number of times 'str' matches the 'regexp' pattern. Note: 'regexp' must be a Java regular expression. | regexp_count("The quick brown fox quick", "quick") = 2 |
| regexp_extract(String str, String regexp, [Integer extractIndex]) | - Return "" if any of the arguments are NULL or invalid; - Return a string from 'str' which extracted with a specified regexp expression 'regexp' and a regexp match group index 'extractIndex'. Note: 'regexp' must be a Java regular expression. 'extractIndex' indicates which regexp group to extract and starts from 1, also the default value if not specified. 0 means matching the entire 'regexp' expression. In addition, the regexp match group index should not exceed the number of the defined groups. | REGEXP_EXTRACT("abc123def", "(\d+)", 1) = 123 REGEXP_EXTRACT("Name: John, Age: 25, Location: NY", "Name: (\w+), Age: (\d+), Location: (\w+)", 2) = 25 REGEXP_EXTRACT("abc123def", "(\d+)", 2) = null REGEXP_EXTRACT("abc123def", "abcdef", 1) = null |
| regexp_extract_all(String str, String regexp, [Integer extractIndex]) | - Return "" if any of the arguments are NULL or invalid; - Return an array consisting of all substrings from the 'str' that match the 'regexp' expression and correspond to the 'extractIndex' set of regular expressions. Note: 'regexp' must be a Java regular expression. 'extractIndex' indicates which regexp group to extract and starts from 1, also the default value if not specified. 0 means matching the entire 'regexp' expression. | REGEXP_EXTRACT_ALL("abc123def456ghi789", "(\d+)", 0) = [123, 456, 789] REGEXP_EXTRACT_ALL("Name: John, Age: 25, Location: NY", "Name: (\w+), Age: (\d+), Location: (\w+)", 1) = [John] REGEXP_EXTRACT_ALL("Name: John, Age: 25, Location: NY", "Name: (\w+), Age: (\d+), Location: (\w+)", 0) = [Name: John, Age: 25, Location: NY] |
| regexp_instr(String str, String regexp) | - Return "" if any of the arguments are NULL or invalid; - Return the position of the first substring in 'str' that matches 'regexp'. | regexp_instr("abc123def", "(\d+)") = 4 |
| regexp_matches(String source_string, String regexp [,String flags]) | - Return "" if any of the arguments are NULL or invalid; - Return the result of the first match of the specified regular expression 'regexp' from 'source_string'. Note: 'flags' is one of ('g' -> flag can be used when we want to match all the substrings that occur, 'i' -> flag to ignore case for matching, 'x' -> flag to extend syntax (ignoring whitespace and comments in regular expressions), 'm' and 'n' -> flag allows regular expressions to match across multiple lines) | regexp_matches("The quick brown fox", "quick") = [{"quick"}] regexp_matches("foo 123 bar 456", "\d+", "g") = [{"123"},{"456"}] |
| regexp_replace(String source_string, String regexp, String replacement) | - Return "" if any of the arguments are NULL or invalid; - Return a string from 'source_string' with all the substrings that match a regular expression 'regexp' consecutively being replaced with 'replacement'. | regexp_replace('foobarbaz', 'b..', 'X') = "fooXbaz" |
| regexp_split_to_array(String source_string, String regexp, String flags) | - Return "" if any of the arguments are NULL or invalid; - Return a string from 'source_string' with all the substrings that match a regular expression 'regexp' consecutively being replaced with 'replacement'. Note: 'flags' is one of ('g' -> flag can be used when we want to match all the substrings that occur, 'i' -> flag to ignore case for matching, 'x' -> flag to extend syntax (ignoring whitespace and comments in regular expressions), 'm' and 'n' -> flag allows regular expressions to match across multiple lines). | regexp_split_to_array("hello world","\s+") = {hello, world} |
| repeat(String str, Integer times) replicate(String str, Integer times) | - Return "" if any parameter is null; - Return a new string that repeats the 'str' by a certain number of 'times'. | repeat('apple', 2) = "appleapple" |
| replace(String s, String s1, String s2) | - Return "" if any parameter is null; - Return the result of replacing string 's1' with string 's2' in string 's'. | replace('Hello World', '', 'J') = "JHJeJlJlJoJ JWJoJrJlJdJ" |
| reverse(String str) | - Return "" if 'str' is a empty string or NULL; - Return the 'str' with the order of the characters reversed. | reverse('apple') = "elppa" |
| right(String str) | - Return "" if either 'str' or 'len' is NULL; - Return "" if it is less than or equal to zero; - Return a substring of 'len' starting from the right side of the 'str'. | right('hello world',100) = "hello world" |
| rpad(String str) | - Return "" if any of the three parameters is NULL or 'len' is less than 0; - Return the substring of 's1' with subscripts in the range of [0, 'len') if 'len' is less than or equal to the length of 's1'; - Return "" if 's2' is "" and 'len' is longer than the length of 's1'; - Return the result string of padding string 's2' at the end of string 's1' to make the length of the string 'len' if 's2' is not "". | rpad('he',1,'xxd') = "h" rpad('he',7,'') = "" |
| rtrim(String str) | - Return "" if 'str' is NULL; - Return the string 'str' with trailing space characters removed. | rtrim(' in long ') = " in long" |
| soundex(String str) | - Return "" if 'str' is NULL; - Return a four character code representing the sound of 'str'. | soundex('hello world') = "H464" |
| space(Integer N) | - Return "" if 'N' is NULL or less than or equal to 0; - Return a string consisting of 'N' space characters. | space(5) = " " |
| split_index(String str, String delimiter, Integer index) splitindex(String str, String delimiter, Integer index) | - Return "" if the index is negative or any of the arguments is NULL; - Return "" NULL if the index is out of bounds of the split strings; - Return the string at the given 'index' integer(zero-based) after splitting 'str' by 'delimiter'. | split_index('a,b,c', ',', 1) = "b" |
| startswith(String s1, String s2) | - Return "" if either argument is NULL; - Return whether 's2' starts with 's2'. | startswith('Apache InLong', 'A') = true |
| str_to_map(String s1, String pairDelimiter, String keyValueDelimiter) | - Return "" if 'str' is NULL; - Return a map after splitting the 'str' into key/value pairs using 'pairDelimiter'(default is ',') and 'keyValueDelimiter'(default is '='); Note: Both 'pairDelimiter' and 'keyValueDelimiter' are treated as regular expressions.So special characters(e.g. <([{^-=$!|]})?*+.>) need to be properly escaped before using as a delimiter literally. | str_to_map('key1=value1,key2=value2,key3=value3') = {key1=value1, key2=value2, key3=value3} str_to_map("name->John!age->30!city->China" , "!" , "->") = {name=John, age=30, city=China} |
| strcmp(String s1, String s2) | - Return "" if either argument is NULL; - Return 0 if the strings are the same; - Return -1 if 's1' is smaller than 's2' according to the current sort order; - Return 1 otherwise. | strcmp('hello world','banana') = 1 |
| substring(String s1, Integer pos, Integer len) substr(String s1, Integer pos, Integer len) mid(String s1, Integer pos, Integer len) | - Return "" if 'str' is NULL; - Return a substring of 'str' starting from position 'pos' with length 'len' (to the end by default). Note: This function also supports "substring(str FROM pos [ FOR len ])". | substring('apple', 1, 3) = "app" |
| substring_index(String str, String delim, Integer count) | - Return "" if any parameter is NULL; - Return everything to the left of the last count occurrences of 'delim' (counting from the left), if 'count' is positive; - Return everything to the right of the last count occurrences of 'delim' (counting from the right) if 'count' is negative. | SUBSTRING_INDEX('AA. ',' ',1) = "AA." |
| translate(String origin_string, String find_chars, String replace_chars) | - Return "" if any parameter is NULL; - Return the result of replacing all occurrences of both 'find_chars' and 'origin_string' with the characters in 'replace_chars'. | translate(apache@inlong.com, '@', '.') = "apache.inlong.com" translate(hello WorD, 'WD', 'wd') = "hello word" |
| trim(String str) btrim(String str) | - Return "" if 'str' is NULL; - Return the result of deleting spaces before and after the 'str'. | trim(' in long ') = "in long" |
| unhex(String str) | - Return "" if 'str' is NULL; - Return the result of interpreting each pair of characters in the argument as the character corresponding to its hexadecimal number. | unhex("696E6C6F6E67") = "inlong" |
| upper(String s) ucase(String s) | - Return "" if 's' is NULL; - Return the result of converting 's' to uppercase | upper("ApPlE") = "APPLE" |
| url_decode(String str[, String charset]) | - Return "" if 'str' is NULL, or there is an issue with the decoding process(such as encountering an illegal escape pattern), or the encoding scheme is not supported; - Return the result of decoding a given 'str' in 'application/x-www-form-urlencoded' format using the charset(default:UTF-8) encoding scheme. | url_decode('https%3A%2F%2Fapache.inlong.com%2Fsearch%3Fq%3Djava+url+encode') = "https://apache.inlong.com/search?q=java url encode" url_decode('https%3A%2F%2Fapache.inlong.com%2Fsearch%3Fq%3Djava+url+encode','UTF-8') = "https://apache.inlong.com/search?q=java url encode" |
| url_encode(String str[, String charset]) | - Return "" if 'str' is NULL, or there is an issue with the decoding process(such as encountering an illegal escape pattern), or the encoding scheme is not supported; - Return the result of translating 'str' into 'application/x-www-form-urlencoded' format using the charset(default:UTF-8) encoding scheme. | url_encode('https://apache.inlong.com/search?q=java url encode') = "https%3A%2F%2Fapache.inlong.com%2Fsearch%3Fq%3Djava+url+encode" url_encode('https://apache.inlong.com/search?q=java url encode','UTF-8') = "https%3A%2F%2Fapache.inlong.com%2Fsearch%3Fq%3Djava+url+encode" |
| uuid(String s) | - Return a UUID (universally unique identifier) string based on RFC 4122 Type 4 (pseudo-randomly generated) UUID. | uuid() |
Temporal
| Function | Description | Example |
|---|---|---|
| convert_tz(String leftStr , String rightStr) | - Return NULL if any parameter is NULL; - Return the result of converts a datetime 'string1' (with default ISO timestamp format yyyy-MM-dd HH:mm:ss’) from time zone 'string2' to time zone 'string3'. Note: The format of time zone should be either an abbreviation such as “PST”, a full name such as “America/Los_Angeles”, or a custom ID such as “GMT-08:00”. | CONVERT_TZ('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles') = "1969-12-31 16:00:00" |
| date_add(String dateStr,String intervalExprStr) | - Return "" if 'dateStr' is NULL; - Return DATE if 'dateStr' is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts(that is, no time parts); - Return TIME if 'dateStr' is a TIME value and the calculations involve only HOURS, MINUTES,and SECONDS parts (that is, no date parts); - Return DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS, or if the first argument is of type TIME and the unit value uses YEAR, MONTH, or DAY; - Return String otherwise (type VARCHAR). Note: Regarding intervalExpr, please refer to the MySQL official website. | date_add('2020-12-31 23:59:59',INTERVAL 999 DAY) = "2023-09-26 23:59:59" DATE_ADD('1992-12-31 23:59:59', INTERVAL '-1.999999' SECOND_MICROSECOND) = "1992-12-31 23:59:57.000001" |
| date_format(String timestampStr, String formatStr) | - Return "" if any parameter is NULL; - Return a string value that converts a timestamp (in seconds) to a date format string in the specified format. Note: The format string is compatible with Java’s SimpleDateFormat | date_format('2024-08-01 22:56:56', 'yyyy/MM/dd HH:mm:ss') = "2024/08/01 22:56:56" |
| date_sub(String dateStr,String intervalExprStr) datesub(String dateStr,String intervalExprStr) | - Return "" if 'dateStr' is NULL; - Return DATE if the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts(that is, no time parts); - Return TIME if the date argument is a TIME value and the calculations involve only HOURS, MINUTES,and SECONDS parts (that is, no date parts); - Return DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS, or if the first argument is of type TIME and the unit value uses YEAR, MONTH, or DAY; - Return String otherwise (type VARCHAR). Note: Regarding 'intervalExpr', please refer to the MySQL official website. | DATE_SUB('1992-12-31 23:59:59', INTERVAL '-1.999999' SECOND_MICROSECOND) = "1993-01-01 00:00:00.999999" |
| datediff(String dateStr1, String dateStr2) | - Return "" if one of the two parameters is null or ""; - Return "" if one of the two parameters has an incorrect date format; - Return the number of days between the dates 'dateStr1'->'dateStr2'. | datediff('2018-12-10 12:30:00', '2018-12-09 13:30:00') = 1 datediff('2018-12', '2018-12-12') = "" |
| day_name(String dateStr) dayname(String dateStr) | - Return "" if 'dateStr' is null; - Return the name of the day of the week from 'dateStr'. | dayname(2024-02-29) = THURSDAY |
| day_of_month(String dateStr) dayofmonth(String dateStr) | - Return "" if 'dateStr' is null; - Return the day of a month (an integer between 1 and 31) from 'dateStr'. | dayofmonth(2024-02-29) = 29 |
| day_of_week(String dateStr) dayofweek(String dateStr) | - Return "" if 'dateStr' is null; - Return the day of a week (an integer between 1(Sunday) and 7(Saturday)) from 'dateStr'. | dayofweek(2024-02-29) = 5 |
| day_of_year(String dateStr) dayofyear(String dateStr) | - Return "" if 'dateStr' is null; - Return the day of a year (an integer between 1 and 366) from 'dateStr'. | dayofyear(2024-02-29) = 60 |
| from_unix_time(Numeric numeric,String formatStr) form_unixtime(Numeric numeric,String formatStr) | - Return "" if 'numeric' is NULL; - Return a representation of the numeric argument as a value in string. Note: 'formatStr' defaults to 'yyyy-MM-dd HH:mm:ss'. numeric is an internal timestamp value representing seconds since ‘1970-01-01 00:00:00’ UTC, such as produced by the UNIX_TIMESTAMP() function. | from_unix_time(44) = "1970-01-01 08:00:44" |
| hour(String timestamp) | - Return "" if 'timestamp' is null; - Return the hour of a day (an integer between 0 and 23) from SQL 'timestamp'. | hour(2024-08-12 12:23:34) = 12 |
| localdate([String timeZoneStr]) currentdate([String timeZoneStr]) current_date([String timeZoneStr]) curdate([String timeZoneStr]) | - Return the current date in the specified 'timeZoneStr'. | localDate("UTC") = currentDate localDate() = currentDate |
| localtime([String timeZoneStr]) current_time([String timeZoneStr]) | - Return the current time in the specified time zone. | localTime() = currentTime currentTime("UTC") = currentTime |
| minute(String timestamp) | - Return "" if 'timestamp' is null; - Return the minute of an hour (an integer between 0 and 59) from SQL 'timestamp'. | minute(2024-08-12 12:23:34) = 23 |
| month(String dateStr) | - Return "" if 'dateStr' is null; - Return the month of a year (an integer between 1 and 12) from 'dateStr'. | month(2024-08-08) = 8 |
| now() | - Return the current SQL timestamp for the local time zone. | now() |
| quarter(String dateStr) | - Return "" if 'dateStr' is null; - Return the quarter of a year (an integer between 1 and 4) from 'dateStr'. | quarter(2024-08-08) = 3 |
| second(String timestamp) | - Return "" if 'timestamp' is null; - Return the second of a minute (an integer between 0 and 59) from SQL 'timestamp'. | second(2024-08-12 12:23:34) = 34 |
| timediff(String dateStr1, String dateStr2) time_diff(String dateStr1, String dateStr2) | - Return "" if 'dateStr1' or 'dateStr2' is NULL and the conversion types of 'dateStr1' and 'dateStr2' are different; - Return 'dateStr1' - 'dateStr2' expressed as a time value. Note: 'dateStr1' and 'dateStr2' are strings converted to TIME or DATETIME expressions. | timediff('23:59:59.000001','01:01:01.000002') = "22:58:57.999998" |
| timestamp(String unit, String datetime_expr1, String datetime_expr2) | - Return "" if 'datetime_expr1' or 'datetime_expr2' is NULL; - Return the date or datetime expression expr as a datetime value if there is only one parameter; - Return the result of the date or date time expression 'datetime_expr1' plus the time expression 'datetime_expr2' if there are two parameters. | timestamp('2003-12-31 12:00:00.600000','12:00:00') = "2004-01-01 00:00:00.600000" |
| timestamp_add(String unit, Integer cnt, String baseDateStr) timestampadd(String unit, Integer cnt, String baseDateStr) | - Return "" if any parameter is null; - Return the result of adding the integer expression interval to the date or datetime expression 'baseDateStr'. Note: 'unit' is one of (MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR). | timestamp_add('MICROSECOND',3,'1970-01-01 00:00:44') = "1970-01-01 00:00:44.000003" |
| timestamp_diff(String unit, String datetime_expr1, String datetime_expr2) timestampdiff(String unit, String datetime_expr1, String datetime_expr2) | - Return "" if any parameter is null; - Return 'datetime_expr2' − 'datetime_expr1', where 'datetime_expr1' and 'datetime_expr2' are date or datetime expressions. Note: 'unit' is one of (MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR). | timestampdiff('MONTH','2003-02-01','2003-05-01') = 3 |
| to_date(String str [,String format]) | - Return "" if 'str' is NULL; - Return the result of converting the date string 'str' to a date in the format 'format'(default is 'yyyy-MM-dd'). | to_date('20240815', 'yyyyMMdd') = "2024-08-15" |
| to_timestamp(String str [,String format]) | - Return "" if 'str' is NULL; - Return the result of converting the date and time string 'str' to the 'format' (by default: yyyy-MM-dd HH:mm:ss if not specified) under the 'UTC+0' time zone to a timestamp. | to_timestamp('1970/01/01 00:00:44', 'yyyy/MM/dd HH:mm:ss') = "1970-01-01 00:00:44.0" |
| unix_timestamp([String dateStr [, String format]]) | - Return current Unix timestamp in seconds if no parameter is specified; - Return the result of converting the date and time string 'dateStr' to the format 'format' (by default: yyyy-MM-dd HH:mm:ss if not specified) to Unix timestamp (in seconds) if there is a parameter specified | unix_timestamp('1970/01/01 08:00:44', 'yyyy/MM/dd HH:mm:ss') = "1970/01/01 08:00:44" |
| week(String dateStr) | - Return "" if 'dateStr' is null; - Return the week of a year (an integer between 1 and 53) from 'dateStr'. | week(2024-02-29) = 9 |
| year(String dateStr) | - Return "" if 'dateStr' is null; - Return the year from SQL date. | year(2024-08-08) = 2024 |