Function Name | Parameter Type | Return Type |
AVG(expression) | INT, FLOAT, and DECIMAL | DECIMAL will be returned if the input parameter is of integer type, and FLOAT if float type. The same type as the input parameter will be returned in all other cases. |
COUNT | - | INT |
MAX(expression) | INT and DECIMAL | The return value type is the same as that of the input parameter |
MIN(expression) | INT and DECIMAL | The return value type is the same as that of the input parameter |
SUM(expression) | INT, FLOAT, DOUBLE, and DECIMAL | INT will be returned if the input parameter is of integer type, and FLOAT if float type. The same type as the input parameter will be returned in all other cases. |
COALESCE ( expression, expression, ... )
expression
parameter.COALESCE(1) -- 1COALESCE(1, null) -- 1COALESCE(null, null, 1) -- 1COALESCE(missing, 1) -- 1COALESCE(null, 'string') -- 'string'COALESCE(null) -- nullCOALESCE(null, null) -- nullCOALESCE(missing) -- nullCOALESCE(missing, missing) -- null
NULLIF ( expression1, expression2 )
expression
parameter.NULLIF(1, 2) -- 1NULLIF(1, '1') -- 1NULLIF(1, NULL) -- 1NULLIF(1, 1) -- nullNULLIF(1.0, 1) -- nullNULLIF(missing, null) -- nullNULLIF(missing, missing) -- nullNULLIF([1], [1]) -- nullNULLIF(NULL, 1) -- nullNULLIF(null, null) -- null
CAST ( expression AS data_type )
expression
parameter can be a value, an array, an operator, or an SQL function that can be calculated to a certain value.data_type
parameter is the data type after conversion, such as INT. For the data types currently supported by COS Select, see Data Types.CAST('2007-04-05T14:30Z' AS TIMESTAMP)CAST(0.456 AS FLOAT)
DATE_ADD( date_part, quantity, timestamp )
date_part
parameter specifies the part of the timestamp to be modified, which can be year, month, day, hour, minute, or second.quantity
parameter represents the value to be added, which must be a positive integer.timestamp
parameter represents the timestamp to be modified.DATE_ADD(year, 5, `2010-01-01T`) -- 2015-01-01DATE_ADD(month, 1, `2010T`) -- 2010-02TDATE_ADD(month, 13, `2010T`) -- 2011-02TDATE_ADD(hour, 1, `2017T`) -- 2017-01-01T01:00-00:00DATE_ADD(hour, 1, `2017-01-02T03:04Z`) -- 2017-01-02T04:04ZDATE_ADD(minute, 1, `2017-01-02T03:04:05.006Z`) -- 2017-01-02T03:05:05.006ZDATE_ADD(second, 1, `2017-01-02T03:04:05.006Z`) -- 2017-01-02T03:04:06.006Z
date_part
value of timestamp1 is greater than that of timestamp2, a positive number will be returned; otherwise, a negative number will be returned.DATE_DIFF( date_part, timestamp1, timestamp2 )
date_part
parameter specifies the unit of time which the two timestamps are compared in and can be year, month, day, hour, minute, or second.timestamp1
parameter is the first input timestamp.timestamp2
parameter is the second input timestamp.DATE_DIFF(year, `2010-01-01T`, `2011-01-01T`) -- 1DATE_DIFF(year, `2010T`, `2010-05T`) -- 4DATE_DIFF(month, `2010T`, `2011T`) -- 12DATE_DIFF(month, `2011T`, `2010T`) -- -12DATE_DIFF(day, `2010-01-01T23:00T`, `2010-01-02T01:00T`) -- 0
EXTRACT( date_part FROM timestamp )
date_part
specifies the unit of time to be extracted, which can be year, month, day, hour, minute, or second.timestamp
parameter represents the input timestamp.EXTRACT(YEAR FROM `2010-01-01T`) -- 2010EXTRACT(MONTH FROM `2010T`) -- 1EXTRACT(MONTH FROM `2010-10T`) -- 10EXTRACT(HOUR FROM `2017-01-02T03:04:05+07:08`) -- 3EXTRACT(MINUTE FROM `2017-01-02T03:04:05+07:08`) -- 4EXTRACT(TIMEZONE_HOUR FROM `2017-01-02T03:04:05+07:08`) -- 7EXTRACT(TIMEZONE_MINUTE FROM `2017-01-02T03:04:05+07:08`) -- 8
TO_STRING ( timestamp time_format_pattern )
timestamp
parameter specifies the timestamp to be converted.time_format_pattern
parameter specifies the time format.Format | Description | Sample |
yy | 2-digit year | 98 |
y | 4-digit year | 1998 |
yyyy | Year expressed by 4 digits. If there are less than 4 digits, 0 will be automatically added | 0199 |
M | Month | 1 |
MM | Month expressed by 2 digits. If there are less than 2 digits, 0 will be automatically added | 01 |
MMM | English abbreviation of a month | Jan |
MMMM | Full English name of a month | January |
MMMMM | Initial of a month | J (not applicable to the to_timestamp function) |
d | Day (1-31) in a month | 1 |
dd | Day expressed by 2 digits (1-31) | 01 |
a | Symbol for morning or afternoon (AM/PM) | AM |
h | Hour in 12-hour time | 1 |
hh | Hour expressed by 2 digits in 12-hour time | 01 |
H | Hour in 24-hour time | 1 |
HH | Hour expressed by 2 digits in 24-hour time | 01 |
m | Minute (00-59) | 1 |
mm | Minute expressed by 2 digits in 24-hour time | 01 |
s | Second (00-59) | 1 |
ss | Second expressed by 2 digits in 24-hour time | 01 |
S | Decimal part of the second (accuracy: 0.1; value range: 0.0 - 0.9) | 0 |
SS | Decimal part of the second (accuracy: 0.01; value range: 0.00 - 0.99) | 6 |
SSS | Decimal part of the second (accuracy: 0.001; value range: 0.000 - 0.999) | 60 |
... | ... | ... |
SSSSSSSSS | Decimal part of the second (accuracy: 0.000000001; value range: 0.000000000 - 0.999999999) | 60000000 |
n | Nanosecond | 60000000 |
X | Hour-level offset. If the offset is 0, then this will be "Z" | +01 or Z |
XX or XXXX | Hour- or minute-level offset. If the offset is 0, then this will be "Z" | +0100 or Z |
xxx or xxxxx | Hour- or minute-level offset. If the offset is 0, then this will be "Z" | +01:00 or Z |
x | Hour-level offset | 1 |
xx or xxxx | Hour- or minute-level offset | 0100 |
xxx or xxxxx | Hour- or minute-level offset | 01:00 |
TO_STRING(`1998-07-20T20:18Z`, 'MMMM d, y') -- "July 20, 1998"TO_STRING(`1998-07-20T20:18Z`, 'MMM d, yyyy') -- "Jul 20, 1998"TO_STRING(`1998-07-20T20:18Z`, 'M-d-yy') -- "7-20-69"TO_STRING(`1998-07-20T20:18Z`, 'MM-d-y') -- "07-20-1998"TO_STRING(`1998-07-20T20:18Z`, 'MMMM d, y h:m a') -- "July 20, 1998 8:18 PM"TO_STRING(`1998-07-20T20:18Z`, 'y-MM-dd''T''H:m:ssX') -- "1998-07-20T20:18:00Z"TO_STRING(`1998-07-20T20:18+08:00Z`, 'y-MM-dd''T''H:m:ssX') -- "1998-07-20T20:18:00Z"TO_STRING(`1998-07-20T20:18+08:00`, 'y-MM-dd''T''H:m:ssXXXX') -- "1998-07-20T20:18:00+0800"TO_STRING(`1998-07-20T20:18+08:00`, 'y-MM-dd''T''H:m:ssXXXXX') -- "1998-07-20T20:18:00+08:00"
TO_TIMESTAMP ( string )
string
parameter represents the input time string.TO_TIMESTAMP('2007T') -- `2007T`TO_TIMESTAMP('2007-02-23T12:14:33.079-08:00') -- `2007-02-23T12:14:33.079-08:00`
UTCNOW()
UTCNOW() -- 2019-01-01T14:23:12.123Z
CHAR_LENGTH ( string )
string
parameter specifies the string for character countingCHAR_LENGTH('null') -- 4CHAR_LENGTH('tencent') -- 7
LOWER ( string )
string
parameter specifies the string for which to convert uppercase letters to lowercase letters.LOWER('TENcent') -- 'tencent'
SUBSTRING( string FROM start [ FOR length ] )
string
parameter specifies the string from which to extract a substring.start
parameter represents an index value of the string as the starting position for extraction.length
parameter specifies the length of the substring. If the length of the substring is not specified, the remainder of the string will be extracted.SUBSTRING("123456789", 0) -- "123456789"SUBSTRING("123456789", 1) -- "123456789"SUBSTRING("123456789", 2) -- "23456789"SUBSTRING("123456789", -4) -- "123456789"SUBSTRING("123456789", 0, 999) -- "123456789"SUBSTRING("123456789", 1, 5) -- "12345"
TRIM ( [[LEADING | TRAILING | BOTH remove_chars] FROM] string )
string
parameter specifies the string to be manipulated.LEADING | TRAILING | BOTH
parameter specifies the extra characters to be deleted, which can be before the string (LEADING), after the string (TRAILING), or both (BOTH).remove_chars
parameter specifies the type of extra characters to be deleted. It can be a string containing more than one characters. The TRIM function will delete all extra characters of the corresponding type that are identified by the TRIM function before or after the string
parameter.TRIM(' foobar ') -- 'foobar'TRIM(' \\tfoobar\\t ') -- '\\tfoobar\\t'TRIM(LEADING FROM ' foobar ') -- 'foobar'TRIM(TRAILING FROM ' foobar ') -- 'foobar'TRIM(BOTH FROM ' foobar ') -- 'foobar'TRIM(BOTH '12' FROM '1112211foobar22211122') -- 'foobar'
UPPER ( string )
string
parameter specifies the string to be converted to uppercase letters.UPPER('tenCENT') -- 'TENCENT'
Was this page helpful?