| // Licensed to the Apache Software Foundation (ASF) under one or more |
| // contributor license agreements. See the NOTICE file distributed with |
| // this work for additional information regarding copyright ownership. |
| // The ASF licenses this file to You under the Apache License, Version 2.0 |
| // (the "License"); you may not use this file except in compliance with |
| // the License. You may obtain a copy of the License at |
| // |
| // http://www.apache.org/licenses/LICENSE-2.0 |
| // |
| // Unless required by applicable law or agreed to in writing, software |
| // distributed under the License is distributed on an "AS IS" BASIS, |
| // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| // See the License for the specific language governing permissions and |
| // limitations under the License. |
| = Supported Operators and Functions |
| |
| NOTE: For more information on functions supported by Apache Calcite, see the link:https://calcite.apache.org/docs/reference.html#operators-and-functions[product documentation,window=_blank]. |
| |
| == Aggregate Functions |
| |
| === AVG |
| |
| [source,sql] |
| ---- |
| AVG( [ ALL | DISTINCT ] numeric) |
| ---- |
| |
| Returns the average (arithmetic mean) of numeric across all input values. When used, the type of data will be changed in the following way: |
| |
| [cols="1,1,1", opts="header"] |
| |=== |
| |
| |Input type| Result type| Minimum scale |
| |`DECIMAL`, `BIGINT`, `INTEGER`, `SMALLINT`, `TINYINT`| `DECIMAL`| 16 |
| |`DOUBLE`, `REAL`| `DOUBLE`| |
| |=== |
| |
| === COUNT |
| |
| [source,sql] |
| ---- |
| COUNT( [ ALL | DISTINCT ] value [, value ]*) |
| ---- |
| |
| Returns the number of input rows for which value is not null (wholly not null if value is composite). |
| |
| === MAX |
| |
| [source,sql] |
| ---- |
| MAX( [ ALL | DISTINCT ] value) |
| ---- |
| |
| Returns the maximum value across all input values. |
| |
| === MIN |
| |
| [source,sql] |
| ---- |
| MIN( [ ALL | DISTINCT ] value) |
| ---- |
| |
| Returns the minimum value across all input values. |
| |
| === SUM |
| |
| [source,sql] |
| ---- |
| SUM( [ ALL | DISTINCT ] numeric) |
| ---- |
| |
| Returns the sum of numeric across all input values. |
| |
| === ANY_VALUE |
| |
| [source,sql] |
| ---- |
| ANY_VALUE( [ ALL | DISTINCT ] value) |
| ---- |
| |
| Returns one of the values of value across all input values; this is NOT specified in the SQL standard. |
| |
| === EVERY |
| |
| [source,sql] |
| ---- |
| EVERY(condition) |
| ---- |
| |
| Returns TRUE if all of the values of condition are TRUE. |
| |
| === SOME |
| |
| [source,sql] |
| ---- |
| SOME(condition) |
| ---- |
| |
| Returns TRUE if one or more of the values of condition is TRUE. |
| |
| == JSON Functions |
| |
| === JSON_TYPE |
| |
| [source,sql] |
| ---- |
| JSON_TYPE(jsonValue) |
| ---- |
| |
| Returns a string value indicating the type of jsonValue. |
| |
| === FORMAT JSON |
| |
| Indicates that the value is formatted as JSON. |
| |
| === JSON_VALUE |
| |
| [source,sql] |
| ---- |
| JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] ) |
| ---- |
| |
| Extract an SQL scalar from a jsonValue using JSON path expression path. |
| |
| === JSON_QUERY |
| |
| [source,sql] |
| ---- |
| JSON_QUERY(jsonValue, path [ RETURNING type ] [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] ) |
| ---- |
| |
| Extract a JSON object or JSON array from jsonValue using the path JSON path expression. |
| |
| === JSON_TYPE |
| |
| [source,sql] |
| ---- |
| JSON_TYPE(jsonValue) |
| ---- |
| |
| Returns a string value indicating the type of `jsonValue`. |
| |
| === JSON_EXISTS |
| |
| [source,sql] |
| ---- |
| JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] ) |
| ---- |
| |
| Whether a jsonValue satisfies a search criterion described using JSON path expression path. |
| |
| === JSON_DEPTH |
| |
| [source,sql] |
| ---- |
| JSON_DEPTH(jsonValue) |
| ---- |
| |
| Returns an integer value indicating the depth of jsonValue. |
| |
| === JSON_KEYS |
| |
| [source,sql] |
| ---- |
| JSON_KEYS(jsonValue [, path ]) |
| ---- |
| |
| Returns a string indicating the keys of a JSON jsonValue. |
| |
| === JSON_PRETTY |
| |
| [source,sql] |
| ---- |
| JSON_PRETTY(jsonValue) |
| ---- |
| |
| Returns a pretty-printing of jsonValue. |
| |
| === JSON_LENGTH |
| |
| [source,sql] |
| ---- |
| JSON_LENGTH(jsonValue [, path ]) |
| ---- |
| |
| Returns a integer indicating the length of jsonValue. |
| |
| === JSON_REMOVE |
| |
| [source,sql] |
| ---- |
| JSON_REMOVE(jsonValue, path [, path ]) |
| ---- |
| |
| Removes data from jsonValue using a series of path expressions and returns the result. |
| |
| === JSON_STORAGE_SIZE |
| |
| [source,sql] |
| ---- |
| JSON_STORAGE_SIZE(jsonValue) |
| ---- |
| |
| Returns the number of bytes used to store the binary representation of jsonValue. |
| |
| === JSON_OBJECT |
| |
| [source,sql] |
| ---- |
| JSON_OBJECT( jsonKeyVal [, jsonKeyVal ]* [ nullBehavior ] ) |
| ---- |
| |
| Construct JSON object using a series of key-value pairs. |
| |
| === JSON_ARRAY |
| |
| [source,sql] |
| ---- |
| JSON_ARRAY( [ jsonVal [, jsonVal ]* ] [ nullBehavior ] ) |
| ---- |
| |
| Construct a JSON array using a series of values. |
| |
| === IS JSON VALUE |
| |
| [source,sql] |
| ---- |
| jsonValue IS JSON [ VALUE ] |
| ---- |
| |
| Whether jsonValue is a JSON value. |
| |
| === IS JSON OBJECT |
| |
| [source,sql] |
| ---- |
| jsonValue IS JSON OBJECT |
| ---- |
| |
| Whether jsonValue is a JSON object. |
| |
| === IS JSON ARRAY |
| |
| [source,sql] |
| ---- |
| jsonValue IS JSON ARRAY |
| ---- |
| |
| Whether jsonValue is a JSON array. |
| |
| === IS JSON SCALAR |
| |
| [source,sql] |
| ---- |
| jsonValue IS JSON SCALAR |
| ---- |
| |
| Whether jsonValue is a JSON scalar value. |
| |
| == Regular Expression Functions |
| |
| === POSIX REGEX CASE INSENSITIVE |
| |
| [source,sql] |
| ---- |
| value 1 POSIX REGEX CASE INSENSITIVE value 2 |
| ---- |
| |
| Case-sensitive POSIX regular expression. |
| |
| === POSIX REGEX CASE SENSITIVE |
| |
| [source,sql] |
| ---- |
| value 1 POSIX REGEX CASE SENSITIVE value 2 |
| ---- |
| |
| Case-sensitive POSIX regular expression. |
| |
| |
| === REGEXP_REPLACE |
| |
| [source,sql] |
| ---- |
| REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [, matchType]]]) |
| ---- |
| |
| Replaces all substrings of string that match regexp with rep at the starting pos in expr (if omitted, the default is 1), occurrence specifies which occurrence of a match to search for (if omitted, the default is 1), matchType specifies how to perform matching |
| |
| [source,sql] |
| ---- |
| REGEXP_REPLACE(string, regexp) |
| ---- |
| |
| Replaces all substrings of value that match regexp with an empty string and returns modified value. |
| |
| == Numeric Functions |
| |
| === MOD |
| |
| [source,sql] |
| ---- |
| MOD(numeric1, numeric2) |
| ---- |
| |
| Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative. |
| |
| === EXP |
| |
| [source,sql] |
| ---- |
| EXP(numeric) |
| ---- |
| |
| Returns e raised to the power of numeric. |
| |
| === POWER |
| |
| [source,sql] |
| ---- |
| POWER(numeric1, numeric2) |
| ---- |
| |
| Returns numeric1 raised to the power of numeric2. |
| |
| === LN |
| |
| [source,sql] |
| ---- |
| LN(numeric) |
| ---- |
| |
| Returns the natural logarithm (base e) of numeric. |
| |
| === LOG10 |
| |
| [source,sql] |
| ---- |
| LOG10(numeric) |
| ---- |
| |
| Returns the base 10 logarithm of numeric. |
| |
| === ABS |
| |
| [source,sql] |
| ---- |
| ABS(numeric) |
| ---- |
| |
| Returns the absolute value of numeric. |
| |
| === RAND |
| |
| [source,sql] |
| ---- |
| RAND([seed]) |
| ---- |
| |
| Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with seed. |
| |
| === RAND_INTEGER |
| |
| [source,sql] |
| ---- |
| RAND_INTEGER([seed, ] numeric) |
| ---- |
| |
| Generates a random integer between 0 and numeric - 1 inclusive, optionally initializing the random number generator with seed. |
| |
| === ACOS |
| |
| [source,sql] |
| ---- |
| ACOS(numeric) |
| ---- |
| |
| Returns the arc cosine of numeric. |
| |
| === ASIN |
| |
| [source,sql] |
| ---- |
| ASIN(numeric) |
| ---- |
| |
| Returns the arc sine of numeric. |
| |
| === ATAN |
| |
| [source,sql] |
| ---- |
| ATAN(numeric) |
| ---- |
| |
| Returns the arc tangent of numeric. |
| |
| === ATAN2 |
| |
| [source,sql] |
| ---- |
| ATAN2(numeric, numeric) |
| ---- |
| |
| Returns the arc tangent of the numeric coordinates. |
| |
| === SQRT |
| |
| [source,sql] |
| ---- |
| SQRT(numeric) |
| ---- |
| |
| Returns the square root of numeric. |
| |
| === CBRT |
| |
| [source,sql] |
| ---- |
| CBRT(numeric) |
| ---- |
| |
| Returns the cube root of numeric. |
| |
| === COS |
| |
| [source,sql] |
| ---- |
| COS(numeric) |
| ---- |
| |
| Returns the cosine of numeric. |
| |
| === COSH |
| |
| [source,sql] |
| ---- |
| COSH(numeric) |
| ---- |
| |
| Returns the hyperbolic cosine of numeric. |
| |
| === COT |
| |
| [source,sql] |
| ---- |
| COT(numeric) |
| ---- |
| |
| Returns the cotangent of numeric. |
| |
| === DEGREES |
| |
| [source,sql] |
| ---- |
| DEGREES(numeric) |
| ---- |
| |
| Converts numeric from radians to degrees. |
| |
| === RADIANS |
| |
| [source,sql] |
| ---- |
| RADIANS(numeric) |
| ---- |
| |
| Converts numeric from degrees to radians. |
| |
| === ROUND |
| |
| [source,sql] |
| ---- |
| ROUND(numeric1 [, integer2]) |
| ---- |
| |
| Rounds numeric1 to optionally integer2 (if not specified 0) places right to the decimal point. |
| |
| === SIGN |
| |
| [source,sql] |
| ---- |
| SIGN(numeric) |
| ---- |
| |
| Returns the signum of numeric. |
| |
| === SIN |
| |
| [source,sql] |
| ---- |
| SIN(numeric) |
| ---- |
| |
| Returns the sine of numeric. |
| |
| === SINH |
| |
| [source,sql] |
| ---- |
| SINH(numeric) |
| ---- |
| |
| Returns the hyperbolic sine of numeric. |
| |
| === TAN |
| |
| [source,sql] |
| ---- |
| TAN(numeric) |
| ---- |
| |
| Returns the tangent of numeric. |
| |
| === TANH |
| |
| [source,sql] |
| ---- |
| TANH(numeric) |
| ---- |
| |
| Returns the hyperbolic tangent of numeric. |
| |
| === TRUNCATE |
| |
| [source,sql] |
| ---- |
| TRUNCATE(numeric1 [, integer2]) |
| ---- |
| |
| Truncates numeric1 to optionally integer2 (if not specified 0) places right to the decimal point. |
| |
| === PI |
| |
| [source,sql] |
| ---- |
| PI() |
| ---- |
| |
| Returns a value that is closer than any other value to Pi. |
| |
| == String Functions |
| |
| === UPPER |
| |
| [source,sql] |
| ---- |
| UPPER(string) |
| ---- |
| |
| Returns a character string converted to upper case. |
| |
| === LOWER |
| |
| [source,sql] |
| ---- |
| LOWER(string) |
| ---- |
| |
| Returns a character string converted to lower case. |
| |
| === INITCAP |
| |
| [source,sql] |
| ---- |
| INITCAP(string) |
| ---- |
| |
| Returns string with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. |
| |
| === TO_BASE64 |
| |
| [source,sql] |
| ---- |
| TO_BASE64(string) |
| ---- |
| Converts the string to base-64 encoded form and returns a encoded string |
| |
| === FROM_BASE64 |
| |
| [source,sql] |
| ---- |
| FROM_BASE64(string) |
| ---- |
| |
| Returns the decoded result of a base-64 string as a string. |
| |
| === MD5 |
| |
| [source,sql] |
| ---- |
| MD5(string) |
| ---- |
| |
| Calculates an MD5 128-bit checksum of string and returns it as a hex string. |
| |
| === SHA1 |
| |
| [source,sql] |
| ---- |
| SHA1(string) |
| ---- |
| |
| Calculates a SHA-1 hash value of string and returns it as a hex string. |
| |
| === SUBSTRING |
| |
| [source,sql] |
| ---- |
| SUBSTRING(string FROM integer) |
| ---- |
| |
| Returns a substring of a character string starting at a given point. |
| |
| [source,sql] |
| ---- |
| SUBSTRING(string FROM integer FOR integer) |
| ---- |
| |
| Returns a substring of a character string starting at a given point with a given length. |
| |
| [source,sql] |
| ---- |
| SUBSTRING(binary FROM integer) |
| ---- |
| |
| Returns a substring of binary starting at a given point. |
| |
| [source,sql] |
| ---- |
| SUBSTRING(binary FROM integer FOR integer) |
| |
| ---- |
| |
| Returns a substring of binary starting at a given point with a given length. |
| |
| === LEFT |
| |
| [source,sql] |
| ---- |
| LEFT(string, length) |
| ---- |
| |
| Returns the leftmost length characters from the string. |
| |
| === RIGHT |
| |
| [source,sql] |
| ---- |
| RIGHT(string, length) |
| ---- |
| |
| Returns the rightmost length characters from the string. |
| |
| === REPLACE |
| |
| [source,sql] |
| ---- |
| REPLACE(char, search_string [, replace_string]) |
| ---- |
| |
| Replaces search_string with replace_string. |
| |
| === TRANSLATE |
| |
| [source,sql] |
| ---- |
| TRANSLATE(expr, fromString, toString) |
| ---- |
| Returns expr with all occurrences of each character in fromString replaced by its corresponding character in toString. Characters in expr that are not in fromString are not replaced. |
| |
| |
| === CHR |
| |
| [source,sql] |
| ---- |
| CHR(integer) |
| ---- |
| |
| Returns the character whose UTF-8 code is integer. |
| |
| === CHAR_LENGTH |
| |
| [source,sql] |
| ---- |
| CHAR_LENGTH(string) |
| ---- |
| |
| Returns the number of characters in a character string. |
| |
| === CHARACTER_LENGTH |
| |
| [source,sql] |
| ---- |
| CHARACTER_LENGTH(string) |
| ---- |
| |
| Returns the number of characters in a character string. |
| |
| === || |
| |
| [source,sql] |
| ---- |
| string || string |
| ---- |
| |
| Concatenates two character strings. |
| |
| === CONCAT |
| |
| [source,sql] |
| ---- |
| CONCAT(string, string) |
| ---- |
| |
| Concatenates two strings, returns null only when both string arguments are null, otherwise treats null as empty string. |
| |
| [source,sql] |
| ---- |
| CONCAT(string [, string ]*) |
| ---- |
| |
| Concatenates one or more strings, returns null if any of the arguments is null. |
| |
| [source,sql] |
| ---- |
| CONCAT(string [, string ]*) |
| ---- |
| |
| Concatenates one or more strings, null is treated as empty string. |
| |
| === OVERLAY |
| |
| [source,sql] |
| ---- |
| OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ]) |
| ---- |
| |
| Replaces a substring of string1 with string2. |
| |
| [source,sql] |
| ---- |
| OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ]) |
| ---- |
| |
| Replaces a substring of binary1 with binary2. |
| |
| === POSITION |
| |
| [source,sql] |
| ---- |
| POSITION(substring IN string) |
| ---- |
| |
| Returns the position of the first occurrence of substring in string. |
| |
| [source,sql] |
| ---- |
| POSITION(substring IN string FROM integer) |
| ---- |
| |
| Returns the position of the first occurrence of substring in string starting at a given point (not standard SQL). |
| |
| [source,sql] |
| ---- |
| POSITION(binary1 IN binary2) |
| ---- |
| |
| Returns the position of the first occurrence of binary1 in binary2. |
| |
| [source,sql] |
| ---- |
| POSITION(binary1 IN binary2 FROM integer) |
| ---- |
| |
| Returns the position of the first occurrence of binary1 in binary2 starting at a given point (not standard SQL). |
| |
| === ASCII |
| |
| [source,sql] |
| ---- |
| ASCII(string) |
| ---- |
| |
| Returns the ASCII code of the first character of string; if the first character is a non-ASCII character, returns its Unicode code point; returns 0 if string is empty. |
| |
| === REPEAT |
| |
| [source,sql] |
| ---- |
| REPEAT(string, integer) |
| ---- |
| |
| Returns a string consisting of string repeated of integer times; returns an empty string if integer is less than 1. |
| |
| === SPACE |
| |
| [source,sql] |
| ---- |
| SPACE(integer) |
| ---- |
| Returns a string of integer spaces; returns an empty string if integer is less than 1. |
| |
| |
| === STRCMP |
| |
| [source,sql] |
| ---- |
| STRCMP(string, string) |
| ---- |
| |
| Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one. |
| |
| === SOUNDEX |
| |
| [source,sql] |
| ---- |
| SOUNDEX(string) |
| ---- |
| |
| * Returns the phonetic representation of string; throws if string is encoded with multi-byte encoding such as UTF-8; or |
| * Returns the phonetic representation of string; return original string if string is encoded with multi-byte encoding such as UTF-8 |
| |
| === DIFFERENCE |
| |
| [source,sql] |
| ---- |
| DIFFERENCE(string, string) |
| ---- |
| |
| Returns a measure of the similarity of two strings, namely the number of character positions that their SOUNDEX values have in common: 4 if the SOUNDEX values are same and 0 if the SOUNDEX values are totally different. |
| |
| === REVERSE |
| |
| [source,sql] |
| ---- |
| REVERSE(string) |
| ---- |
| |
| Returns string with the order of the characters reversed. |
| |
| === TRIM |
| |
| [source,sql] |
| ---- |
| TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2) |
| ---- |
| |
| Removes the longest string containing only the characters in string1 from the start/end/both ends of string1. |
| |
| === LTRIM |
| |
| [source,sql] |
| ---- |
| LTRIM(string) |
| ---- |
| |
| Returns string with all blanks removed from the start. |
| |
| === RTRIM |
| |
| [source,sql] |
| ---- |
| RTRIM(string) |
| ---- |
| |
| Returns string with all blanks removed from the end. |
| |
| === SUBSTR |
| |
| [source,sql] |
| ---- |
| SUBSTR(string, position [, substringLength ]) |
| ---- |
| |
| Returns a portion of string, beginning at character position, substringLength characters long. SUBSTR calculates lengths using characters as defined by the input character set. |
| |
| === LENGTH |
| |
| [source,sql] |
| ---- |
| LENGTH(string) |
| ---- |
| |
| Equivalent to CHAR_LENGTH(string). |
| |
| === OCTET_LENGTH |
| |
| [source,sql] |
| ---- |
| OCTET_LENGTH(binary) |
| ---- |
| |
| Returns the number of bytes in binary. |
| |
| === LIKE |
| |
| [source,sql] |
| ---- |
| string1 LIKE string2 [ ESCAPE string3 ] |
| ---- |
| |
| Whether string1 matches pattern string2. |
| |
| === SIMILAR TO |
| |
| [source,sql] |
| ---- |
| string1 SIMILAR TO string2 [ ESCAPE string3 ] |
| ---- |
| |
| Whether string1 matches regular expression string2. |
| |
| == Date/Time Functions |
| |
| === EXTRACT |
| |
| [source,sql] |
| ---- |
| EXTRACT(timeUnit FROM datetime) |
| ---- |
| |
| Extracts and returns the value of a specified datetime field from a datetime value expression. |
| |
| === FLOOR |
| |
| [source,sql] |
| ---- |
| FLOOR(datetime TO timeUnit) |
| ---- |
| |
| Rounds datetime down to timeUnit. |
| |
| === CEIL |
| |
| [source,sql] |
| ---- |
| CEIL(datetime TO timeUnit) |
| ---- |
| |
| Rounds datetime up to timeUnit. |
| |
| === TIMESTAMPDIFF |
| |
| [source,sql] |
| ---- |
| TIMESTAMPDIFF(timeUnit, datetime, datetime2) |
| ---- |
| |
| Returns the (signed) number of timeUnit intervals between datetime and datetime2. Equivalent to (datetime2 - datetime) timeUnit. |
| |
| === LAST_DAY |
| |
| [source,sql] |
| ---- |
| LAST_DAY(date) |
| ---- |
| |
| Returns the date of the last day of the month in a value of datatype DATE; For example, it returns DATE’2020-02-29’ for both DATE’2020-02-10’ and TIMESTAMP’2020-02-10 10:10:10’. |
| |
| === DAYNAME |
| |
| [source,sql] |
| ---- |
| DAYNAME(datetime) |
| ---- |
| |
| Returns the name of the day of the week based on the datetime value. |
| |
| === MONTHNAME |
| |
| [source,sql] |
| ---- |
| MONTHNAME(date) |
| ---- |
| |
| Returns the name, in the connection’s locale, of the month in datetime; for example, it returns ‘二月’ for both DATE ‘2020-02-10’ and TIMESTAMP ‘2020-02-10 10:10:10’. |
| |
| === DAYOFMONTH |
| |
| [source,sql] |
| ---- |
| DAYOFMONTH(date) |
| ---- |
| |
| Equivalent to EXTRACT(DAY FROM date). Returns an integer between 1 and 31. |
| |
| === DAYOFWEEK |
| |
| [source,sql] |
| ---- |
| DAYOFWEEK(date) |
| ---- |
| |
| Equivalent to EXTRACT(DOW FROM date). Returns an integer between 1 and 7. |
| |
| === DAYOFYEAR |
| |
| [source,sql] |
| ---- |
| DAYOFYEAR(date) |
| ---- |
| |
| Equivalent to EXTRACT(DOY FROM date). Returns an integer between 1 and 366. |
| |
| === YEAR |
| |
| [source,sql] |
| ---- |
| YEAR(date) |
| ---- |
| |
| Equivalent to EXTRACT(YEAR FROM date). Returns an integer. |
| |
| === QUARTER |
| |
| [source,sql] |
| ---- |
| QUARTER(date) |
| ---- |
| |
| Equivalent to EXTRACT(QUARTER FROM date). Returns an integer between 1 and 4. |
| |
| === MONTH |
| |
| [source,sql] |
| ---- |
| MONTH(date) |
| ---- |
| |
| Equivalent to EXTRACT(MONTH FROM date). Returns an integer between 1 and 12. |
| |
| === WEEK |
| |
| [source,sql] |
| ---- |
| WEEK(date) |
| ---- |
| |
| Equivalent to EXTRACT(WEEK FROM date). Returns an integer between 1 and 53. |
| |
| === HOUR |
| |
| [source,sql] |
| ---- |
| HOUR(date) |
| ---- |
| |
| Equivalent to EXTRACT(HOUR FROM date). Returns an integer between 0 and 23. |
| |
| === MINUTE |
| |
| [source,sql] |
| ---- |
| MINUTE(date) |
| ---- |
| |
| Equivalent to EXTRACT(MINUTE FROM date). Returns an integer between 0 and 59. |
| |
| === SECOND |
| |
| [source,sql] |
| ---- |
| SECOND(date) |
| ---- |
| |
| Equivalent to EXTRACT(SECOND FROM date). Returns an integer between 0 and 59. |
| |
| === TIMESTAMP_SECONDS |
| |
| [source,sql] |
| ---- |
| TIMESTAMP_SECONDS(integer) |
| ---- |
| |
| Returns the TIMESTAMP that is integer seconds after 1970-01-01 00:00:00. |
| |
| === TIMESTAMP_MILLIS |
| |
| [source,sql] |
| ---- |
| TIMESTAMP_MILLIS(integer) |
| ---- |
| |
| Returns the TIMESTAMP that is integer milliseconds after 1970-01-01 00:00:00. |
| |
| === TIMESTAMP_MICROS |
| |
| [source,sql] |
| ---- |
| TIMESTAMP_MICROS(integer) |
| ---- |
| |
| Returns the TIMESTAMP that is integer microseconds after 1970-01-01 00:00:00. |
| |
| === UNIX_SECONDS |
| |
| [source,sql] |
| ---- |
| UNIX_SECONDS(timestamp) |
| ---- |
| |
| Returns the number of seconds since 1970-01-01 00:00:00. |
| |
| === UNIX_MILLIS |
| |
| [source,sql] |
| ---- |
| UNIX_MILLIS(timestamp) |
| ---- |
| |
| Returns the number of milliseconds since 1970-01-01 00:00:00. |
| |
| === UNIX_MICROS |
| |
| [source,sql] |
| ---- |
| UNIX_MICROS(timestamp) |
| ---- |
| |
| Returns the number of microseconds since 1970-01-01 00:00:00. |
| |
| === UNIX_DATE |
| |
| [source,sql] |
| ---- |
| UNIX_DATE(date) |
| ---- |
| |
| Returns the number of days since 1970-01-01 |
| |
| === DATE_FROM_UNIX_DATE |
| |
| [source,sql] |
| ---- |
| DATE_FROM_UNIX_DATE(integer) |
| ---- |
| |
| Returns the DATE that is integer days after 1970-01-01. |
| |
| === DATE |
| |
| [source,sql] |
| ---- |
| DATE(timestamp) |
| ---- |
| |
| Extracts the DATE from a timestamp. |
| |
| [source,sql] |
| ---- |
| DATE(timestampLtz) |
| ---- |
| |
| Extracts the DATE from timestampLtz (an instant; BigQuery’s TIMESTAMP type), assuming UTC. |
| |
| [source,sql] |
| ---- |
| DATE(timestampLtz, timeZone) |
| ---- |
| |
| Extracts the DATE from timestampLtz (an instant; BigQuery’s TIMESTAMP type) in timeZone. |
| |
| [source,sql] |
| ---- |
| DATE(string) |
| ---- |
| |
| Equivalent to CAST(string AS DATE). |
| |
| [source,sql] |
| ---- |
| DATE(year, month, day) |
| ---- |
| |
| Returns a DATE value for year, month, and day (all of type INTEGER). |
| |
| === CURRENT_TIMESTAMP |
| |
| [source,sql] |
| ---- |
| CURRENT_TIMESTAMP |
| ---- |
| |
| Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH LOCAL TIME ZONE. |
| |
| === CURRENT_DATE |
| |
| [source,sql] |
| ---- |
| CURRENT_DATE |
| ---- |
| |
| Returns the current date in the session time zone, in a value of datatype DATE. |
| |
| === LOCALTIME |
| |
| [source,sql] |
| ---- |
| LOCALTIME |
| ---- |
| |
| Returns the current date and time in the session time zone in a value of datatype TIME. |
| |
| [source,sql] |
| ---- |
| LOCALTIME(precision) |
| ---- |
| |
| Returns the current date and time in the session time zone in a value of datatype TIME, with precision digits of precision. |
| |
| === LOCALTIMESTAMP |
| |
| [source,sql] |
| ---- |
| LOCALTIMESTAMP |
| ---- |
| |
| Returns the current date and time in the session time zone in a value of datatype TIMESTAMP. |
| |
| [source,sql] |
| ---- |
| LOCALTIMESTAMP(precision) |
| ---- |
| |
| Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with precision digits of precision. |
| |
| == Other Functions |
| |
| === CAST |
| |
| [source,sql] |
| ---- |
| CAST(value AS type) |
| ---- |
| |
| Converts a value to a given type. Casts between integer types truncate towards 0. |
| |
| === COALESCE |
| |
| [source,sql] |
| ---- |
| COALESCE(value, value [, value ]*) |
| ---- |
| |
| Provides a value if the first value is null. For example, COALESCE(NULL, 5) returns 5. |
| |
| === GREATEST |
| |
| [source,sql] |
| ---- |
| GREATEST(expr [, expr ]*) |
| ---- |
| |
| Returns the greatest of the expressions. |
| |
| === NULLIF |
| |
| [source,sql] |
| ---- |
| NULLIF(value, value) |
| ---- |
| |
| Returns NULL if the values are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5. |
| |
| === NVL |
| |
| [source,sql] |
| ---- |
| NVL(value1, value2) |
| ---- |
| |
| Returns value1 if value1 is not null, otherwise value2. |
| |
| === CASE |
| |
| [source,sql] |
| ---- |
| CASE value |
| WHEN value1 [, value11 ]* THEN result1 |
| [ WHEN valueN [, valueN1 ]* THEN resultN ]* |
| [ ELSE resultZ ] |
| END |
| ---- |
| |
| Simple case. |
| |
| [source,sql] |
| ---- |
| CASE |
| WHEN condition1 THEN result1 |
| [ WHEN conditionN THEN resultN ]* |
| [ ELSE resultZ ] |
| END |
| ---- |
| |
| Searched case. |
| |
| === DECODE |
| |
| [source,sql] |
| ---- |
| DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) |
| ---- |
| |
| Compares value to each valueN value one by one; if value is equal to a valueN, returns the corresponding resultN, else returns default, or NULL if default is not specified. |
| |
| === LEAST |
| |
| [source,sql] |
| ---- |
| LEAST(expr [, expr ]* ) |
| ---- |
| |
| Returns the least of the expressions. |
| |
| === COMPRESS |
| |
| [source,sql] |
| ---- |
| COMPRESS(string) |
| ---- |
| |
| Compresses a string using zlib compression and returns the result as a binary string. |
| |
| === TYPEOF |
| |
| [source,sql] |
| ---- |
| TYPEOF value |
| ---- |
| |
| Returns the type of the specified value. |
| |
| === RAND_UUID |
| |
| [source,sql] |
| ---- |
| RAND_UUID |
| ---- |
| |
| Generates a random UUID. |
| |
| === SYSTEM_RANGE |
| |
| [source,sql] |
| ---- |
| SYSTEM_RANGE(start, end[, increment] |
| ---- |
| |
| Returns a range from the table, with an optional increment. |
