Cloud Object Storage SQL Functions-User Guide-Help & Documentation-Tencent Cloud

Aggregation function
COS Select supports the following aggregate functions:
Function Name
Parameter Type
Return Type
AVG(expression)
INT，FLOAT，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，DECIMAL
The return value type is the same as that of the input parameter.
MIN(expression)
INT，DECIMAL
The return value type is the same as that of the input parameter.
SUM(expression)
INT，FLOAT，DOUBLE，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.
Conditional Functions
COS Select supports the following condition functions:
COALESCE
The COALESCE function determines the input parameters in sequence and returns the first non-null parameter value. If the input parameters do not include a non-null parameter, the function will return a null value.
Syntax
COALESCE ( expression, expression, ... )
Note
The expression parameter supports passing INT, String, and Float type values, arrays, or nested functions.
Sample
COALESCE(1)                -- 1
COALESCE(1, null)          -- 1
COALESCE(null, null, 1)    -- 1
COALESCE(missing, 1)       -- 1
COALESCE(null, 'string')   -- 'string'
COALESCE(null)             -- null
COALESCE(null, null)       -- null
COALESCE(missing)          -- null
COALESCE(missing, missing) -- null
NULLIF
The NULLIF function determines the difference between two parameters passed in. If the two parameters have the same value, NULL will be returned; otherwise, the value of the first parameter passed in will be returned.
Syntax
NULLIF ( expression1, expression2 )
Note
The expression parameter supports passing INT, String, and Float type values, arrays, or nested functions.
Sample
NULLIF(1, 2)             -- 1
NULLIF(1, '1')           -- 1
NULLIF(1, NULL)          -- 1
NULLIF(1, 1)             -- null
NULLIF(1.0, 1)           -- null
NULLIF(missing, null)    -- null
NULLIF(missing, missing) -- null
NULLIF([1], [1])         -- null
NULLIF(NULL, 1)          -- null
NULLIF(null, null)       -- null
Conversion Functions
COS Select supports the following conversion functions:
CAST
The CAST function converts one instance to another instance. The instance can be either a value or a function that can be calculated to a certain value.
Syntax
CAST ( expression AS data_type )
Note
The expression parameter can be a value, an array, an operator, or a SQL function that can be calculated to a certain value.
The data_type parameter refers to the converted data type, such as INT type. For data types currently supported by COS Select, please refer to Data Types.
Sample
CAST('2007-04-05T14:30Z' AS TIMESTAMP)
CAST(0.456 AS FLOAT)
Date Functions
COS Select supports the following date functions:
DATE_ADD
The DATE_ADD function adds a specified time interval to a part (year, month, day, hour, minute, or second) of a specific timestamp and returns a new timestamp.
Syntax
DATE_ADD( date_part, quantity, timestamp )
Note
The date_part parameter specifies the part of the timestamp to be modified, which can be year, month, day, hour, minute, or second.
The quantity parameter represents the value to be added, which must be a positive integer.
The timestamp parameter represents the timestamp to be modified.
Sample
DATE_ADD(year, 5, 2010-01-01T)                -- 2015-01-01
DATE_ADD(month, 1, 2010T)                     -- 2010-02T 
DATE_ADD(month, 13, 2010T)                    -- 2011-02T
DATE_ADD(hour, 1, 2017T)                      -- 2017-01-01T01:00-00:00
DATE_ADD(hour, 1, 2017-01-02T03:04Z)          -- 2017-01-02T04:04Z
DATE_ADD(minute, 1, 2017-01-02T03:04:05.006Z) -- 2017-01-02T03:05:05.006Z
DATE_ADD(second, 1, 2017-01-02T03:04:05.006Z) -- 2017-01-02T03:04:06.006Z
DATE_DIFF
The DATE_DIFF function compares two valid timestamps and returns the difference between them, which can be displayed in the specified unit of time. If the date_part value of timestamp1 is greater than that of timestamp2, a positive number will be returned; otherwise, a negative number will be returned.
Syntax
DATE_DIFF( date_part, timestamp1, timestamp2 )
Note
The 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.
The timestamp1 parameter is the first input timestamp.
The timestamp2 parameter is the second input timestamp.
Sample
DATE_DIFF(year, 2010-01-01T, 2011-01-01T)            -- 1
DATE_DIFF(year, 2010T, 2010-05T)                     -- 4 
DATE_DIFF(month, 2010T, 2011T)                       -- 12
DATE_DIFF(month, 2011T, 2010T)                       -- -12
DATE_DIFF(day, 2010-01-01T23:00T, 2010-01-02T01:00T) -- 0 
EXTRACT
 The EXTRACT function extracts the specified time unit value from a given timestamp.
Syntax
EXTRACT( date_part FROM timestamp )
Note
The parameter date_part specifies the unit of time to be extracted, which can be year, month, day, hour, minute, or second.
The timestamp parameter represents the input timestamp.
Sample
EXTRACT(YEAR FROM 2010-01-01T)                           -- 2010
EXTRACT(MONTH FROM 2010T)                                -- 1 
EXTRACT(MONTH FROM 2010-10T)                             -- 10
EXTRACT(HOUR FROM 2017-01-02T03:04:05+07:08)             -- 3
EXTRACT(MINUTE FROM 2017-01-02T03:04:05+07:08)           -- 4
EXTRACT(TIMEZONE_HOUR FROM 2017-01-02T03:04:05+07:08)    -- 7
EXTRACT(TIMEZONE_MINUTE FROM 2017-01-02T03:04:05+07:08)  -- 8
TO_STRING
The TO_STRING function converts a timestamp to a string of time in the specified format.
Syntax
TO_STRING ( timestamp time_format_pattern )
Note
The timestamp parameter specifies the timestamp to be converted.
The 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
Second 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 (ns)
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
Sample
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
 The TO_TIMESTAMP function can convert a time string into a timestamp.
Syntax
TO_TIMESTAMP ( string )
Note
The string parameter is the input time string.
Sample
TO_TIMESTAMP('2007T')                         -- 2007T
TO_TIMESTAMP('2007-02-23T12:14:33.079-08:00') -- 2007-02-23T12:14:33.079-08:00
UTCNOW
 The UTCNOW function can return the current UTC timestamp.
Syntax
UTCNOW()
Sample
UTCNOW() -- 2019-01-01T14:23:12.123Z
String Function
COS Select supports the following string functions:
CHAR_LENGTH, CHARACTER_LENGTH
Both CHAR_LENGTH and CHARACTER_LENGTH can compute the number of characters in a string, and they have the same semantics.
Syntax
CHAR_LENGTH ( string )
Note
The string parameter specifies the string for which the number of characters needs to be calculated.
Sample
CHAR_LENGTH('null')      -- 4
CHAR_LENGTH('tencent')   -- 7
LOWER
 The LOWER function can convert all uppercase letters in the specified string to lowercase letters, while all non-uppercase letters remain unchanged.
Syntax
LOWER ( string )
Note
The string parameter specifies a string that requires converting uppercase letters to lowercase letters.
Sample
LOWER('TENcent') -- 'tencent'
SUBSTRING
 The SUBSTRING function can return a substring of a given string. You can specify an index, and the SUBSTRING function will start from this index, extract the remaining content of the original string according to the specified substring length, and return the result.
Note
If the input string contains only 1 character, and the index is set to be greater than 1, the SUBSTRING function will automatically switch it to 1.
Syntax
SUBSTRING( string FROM start [ FOR length ] )
Note
The string parameter specifies the string from which to extract a substring.
The start parameter represents an index value of the string as the starting position for extraction.
The 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.
Sample
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
 The TRIM function can remove all characters before the first character or after the last character of the specified string. The default character to remove is " ".
Syntax
TRIM ( [[LEADING | TRAILING | BOTH remove_chars] FROM] string )
Note
The string parameter specifies the string to be manipulated.
LEADING | TRAILING | BOTH parameter specifies the need to remove excess characters from the beginning (LEADING), end (TRAILING), or both (BOTH) of the string.
The 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.
Sample
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
 The UPPER function can convert all lowercase characters to uppercase characters, while non-lowercase characters will remain unchanged.
Syntax
UPPER ( string )
Note
The string parameter specifies the string to be converted to uppercase characters.
Sample
UPPER('tenCENT') -- 'TENCENT'
﻿

Function Name	Parameter Type	Return Type
AVG(expression)	INT，FLOAT，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，DECIMAL	The return value type is the same as that of the input parameter.
MIN(expression)	INT，DECIMAL	The return value type is the same as that of the input parameter.
SUM(expression)	INT，FLOAT，DOUBLE，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.

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	Second 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 (ns)	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

SQL Functions

On this page:

Aggregation function

Conditional Functions

COALESCE

Syntax

Sample

NULLIF

Syntax

Sample

Conversion Functions

CAST

Syntax

Sample

Date Functions

DATE_ADD

Syntax

Sample

DATE_DIFF

Syntax

Sample

EXTRACT

Syntax

Sample

TO_STRING

Syntax

Sample

TO_TIMESTAMP

Syntax

Sample

UTCNOW

Syntax

Sample

String Function

CHAR_LENGTH, CHARACTER_LENGTH

Syntax

Sample

LOWER

Syntax

Sample

SUBSTRING

Syntax

Sample

TRIM

Syntax

Sample

UPPER

Syntax

Sample