This topic describes the SQL functions that are supported when you use mapping tables created for analytical stores to query data by executing the SELECT statement, including time series functions, time functions, aggregate functions, mathematical functions, and string functions.
Time series functions
Function | Output type | Description |
time_bin(_time,interval) | Datetime | Buckets data by time when this function is used with group by. For example, you can use The _time parameter specifies the time series time column _time. The interval parameter is of the String type and specifies the interval at which data bucketing is performed. The interval parameter can be specified by using abbreviations of various time units. Examples: |
tag_value_at(_tags,tag_key) | Varchar | Returns the value corresponding to the specified key in the time series tags. The _tags parameter specifies the time series tag column _tags, and the tag_key parameter specifies a key in the time series tags. |
Time functions
When you use the analytical store feature in the Tablestore console, the output values of the Datetime type are in the system time zone (UTC+8 for China, which is Beijing time). When you use Tablestore SDKs to use the analytical store feature, the output values of the Datetime type are converted to UTC time. You can convert time zones based on your business requirements. This topic uses Beijing time, which is the UTC+8 time zone, as an example.
Function | Output type | Description |
from_unixtime(unix_timestamp) | Datetime | Converts a Unix timestamp to a value of the Datetime type, converts the time zone of the value, and returns the value. For example, the return value of The unix_timestamp parameter specifies a Unix timestamp accurate to seconds. |
from_unixtime(unix_timestamp,fsp) | Datetime | Converts a Unix timestamp to a value of the Datetime type, converts the time zone of the value, and returns the value rounded to fsp decimal places. For example, the return value of The unix_timestamp parameter specifies a Unix timestamp accurate to seconds. The fsp parameter specifies the precision, with a maximum value of 6. If the value of fsp is greater than 6, an exception is thrown. If the value of fsp is less than 0, the value is not rounded. |
from_unixtime_millis(unix_timestamp_millis) | Datetime | Converts a Unix timestamp to a value of the Datetime type, converts the time zone of the value, and returns the value. For example, the return value of The unix_timestamp_millis parameter specifies a Unix timestamp accurate to milliseconds. |
from_unixtime_micros(unix_timestamp_micros) | Datetime | Converts a Unix timestamp to a value of the Datetime type, converts the time zone of the value, and returns the value. For example, the return value of The unix_timestamp_micros parameter specifies a Unix timestamp accurate to microseconds. |
unix_timestamp() | Double | Returns the current time's Unix timestamp accurate to seconds in the current time zone. |
unix_timestamp_millis() | Double | Returns the current time's Unix timestamp rounded to the nearest second accurate to milliseconds in the current time zone. For example, the return value of This function is equivalent to Important The timestamp returned by this function has millisecond digits, but the precision is in seconds, not milliseconds. |
unix_timestamp_millis(datetime) | Double | Returns the Unix timestamp accurate to milliseconds in the time zone of the datetime. The datetime parameter specifies the time of Datetime type. |
unix_timestamp_millis(datestr) | Double | Returns the Unix timestamp accurate to milliseconds for the date corresponding to datestr in its time zone. For example, the return value of The datestr parameter is a time string that must explicitly specify the year, month, and day. The value range is from Important
|
unix_timestamp_micros() | Double | Returns the current time's Unix timestamp at the microsecond level in the current time zone. The precision is in seconds. For example, the return value of This function is equivalent to Important The timestamp returned by this function has microsecond digits, but the precision is in seconds, not microseconds. |
unix_timestamp_micros(datetime) | Double | Returns the Unix timestamp at the microsecond level in the time zone of the datetime. The datetime parameter is a time of Datetime type. |
unix_timestamp_micros(datestr) | Double | Returns the Unix timestamp accurate to microseconds for the date and time corresponding to datestr in its time zone. For example, the return value of The value of the datestr parameter is a time string in which the year, month, and day must be explicitly specified. The value range is from Important
|
timediff(expr1,expr2) | Time | Calculates the difference between the expr1 and expr2 parameters. The expr parameters can be of Datetime type, Time type, or String type. |
addtime(expr1,expr2) | Same as expr1 | Calculates the sum of the expr1 and expr2 parameters. The expr1 parameter can be of Datetime type or Time type, and the expr2 parameter can be only of Time type. |
date(datetime) | Date | Returns the date part of datetime. The value of the datetime parameter is a time of Datetime type. |
hour(expr) | BigInt | Returns the hour part in the value of the expr parameter. The value of the expr parameter can be of Datetime type or Time type. |
minute(expr) | BigInt | Returns the minute part in the value of the expr parameter. The value of the expr parameter can be of Datetime type or Time type. |
second(expr) | BigInt | Returns the second part in the value of the expr parameter. The value of the expr parameter can be of Datetime type or Time type. |
microsecond(expr) | BigInt | Returns the microsecond part in the value of the expr parameter. The value of the expr parameter can be of Datetime type or Time type. |
month(expr) | BigInt | Returns the month part in the value of the expr parameter. The value of the expr parameter can be of Datetime type or Date type. |
year(expr) | BigInt | Returns the year part in the value of the expr parameter. The value of the expr parameter can be of Datetime type or Date type. |
week(expr) | BigInt | Returns the week of the year the date specified by the expr parameter falls in. The value of the expr parameter can be of Datetime type or Date type. |
week(expr,mode) | BigInt | Returns the week of the year the date specified by the expr parameter falls in. The method used to calculate the week of the year is specified by the mode parameter. The value of the expr parameter can be of Datetime type or Date type. The following figure shows the value range of the mode parameter (compatible with MySQL).
|
weekday(expr) | BigInt | Returns the index value corresponding to the day of the week for a given date. A return value of 0 indicates Monday, 1 indicates Tuesday, and so on, with 6 indicating Sunday. The value of the expr parameter can be of Datetime type or Date type. |
weekofyear(expr) | BigInt | Returns the week of the year for a given date. The value range is from 1 to 53. WEEKOFYEAR is equivalent to WEEK(date,3). The value of the expr parameter can be of Datetime type or Date type. |
dayofmonth(expr) | BigInt | Returns the day of the month for a given date. The return value ranges from 1 to 31. The value of the expr parameter can be of Datetime type or Date type. |
dayofweek(expr) | BigInt | Returns the index value corresponding to the day of the week for a given date. A return value of 1 indicates Sunday, 2 indicates Monday, and so on, with 7 indicating Saturday. The value of the expr parameter can be of Datetime type or Date type. |
dayofyear(expr) | BigInt | Returns the day of the year for a given date. The return value ranges from 1 to 366. The value of the expr parameter can be of Datetime type or Date type. |
monthname(expr) | Varchar | Returns the full name of the month for the date specified by the expr parameter. The value of the expr parameter can be of Datetime type or Date type. |
dayname(expr) | Varchar | Returns the full name of the day of the week for the date specified by the expr parameter. The value of the expr parameter can be of Datetime type or Date type. |
Aggregate functions
Function | Output type | Description |
count(expr) | Bigint | Returns the number of rows in which the value of the specified column is not NULL among the rows retrieved by the SELECT statement. The result is a Bigint value. |
sum(expr) | Same type as expr | Returns the sum of the expr field. If the query result is empty, NULL is returned. |
avg(expr) | Double | Returns the average value of the expr field. If the query result is empty, NULL is returned. |
min(expr) | Same type as expr | Returns the minimum value of the expr field. If the values of the expr field are strings, the output is the lexicographically smallest string. |
max(expr) | Same type as expr | Returns the maximum value of the expr field. If the values of the expr field are strings, the output is the lexicographically largest string. |
max_by(expr1,expr2) | Same type as expr1 | Returns the value of the expr1 field in the row in which the value of the expr2 field is the largest. If the value of the expr1 field in the row in which the value of the expr2 field is the largest is NULL, NULL is returned. The values of the expr1 and expr2 fields can be strings at the same time. |
min_by(expr1,expr2) | Same type as expr1 | Returns the value of the expr1 field in the row in which the value of the expr2 field is the smallest. If the value of the expr1 field in the row in which the value of the expr2 field is the smallest is NULL, NULL is returned. The values of the expr1 and expr2 fields can be strings at the same time. |
bit_and(expr) | Bigint | Returns the result of a bitwise AND operation on the expr field. If the query result is empty, a 64-bit integer with all bits set to 1 is returned. |
bit_or(expr) | Bigint | Returns the result of a bitwise OR operation on the expr field. If the query result is empty, a 64-bit integer with all bits set to 0 is returned. |
bit_xor(expr) | Bigint | Returns the result of a bitwise XOR operation on the expr field. If the query result is empty, a 64-bit integer with all bits set to 0 is returned. |
std(expr) | Double | Returns the population standard deviation of the expr field. std, stddev, and stddev_pop are three equivalent functions. If the query result is empty, NULL is returned. |
stddev(expr) | Double | |
stddev_pop(expr) | Double | |
stddev_samp(expr) | Double | Returns the sample standard deviation of the expr field. If the query result is empty, NULL is returned. |
var_pop(expr) | Double | Returns the population variance of the expr field. var_pop and variance are two equivalent functions. If the query result is empty, NULL is returned. |
variance(expr) | Double | |
var_samp(expr) | Double | Returns the sample variance of the expr field. If the query result is empty, NULL is returned. |
Mathematical functions
Function | Output type | Description |
mod(x,y) | Same type as x | Returns the remainder of x divided by y. If y is 0, 0 is returned. |
abs(x) | Same type as x | Returns the absolute value of x. |
ceil(x) | Same type as x | Returns the smallest integer value not less than x. |
floor(x) | Same type as x | Returns the largest integer value not greater than x. |
round(x) | Same type as x | Returns the integer value of x rounded to the nearest integer. |
round(x,y) | Same type as x | Returns the value of x rounded to y decimal places. |
acos(x) | Double | Returns the arccosine of x, that is, the value of y when If the value of x is not in the range of -1 to 1, NULL is returned. |
asin(x) | Double | Returns the arcsine of x, that is, the value of y when If the value of x is not in the range of -1 to 1, NULL is returned. |
atan(x) | Double | Returns the arctangent of x, that is, the value of y when |
atan2(x,y) | Double | Returns the arctangent of two variables x and y. The process is similar to that when you calculate the arctangent of |
cos(x) | Double | Returns the cosine of x. The value of x is expressed in radians format. |
sin(x) | Double | Returns the sine of x. The value of x is expressed in radians format. |
tan(x) | Double | Returns the tangent of x. The value of x is expressed in radians format. |
exp(x) | Double | Returns the value of e (the base of natural logarithms) raised to the power of x. |
pow(x,y) | Double | Returns the value of x raised to the power of y. |
radians(x) | Double | Returns the parameter x converted from degrees to radians. Important π radians equals 180 degrees. |
sqrt(x) | Double | Returns the square root of x. |
sign(x) | Bigint | Returns -1, 0, or 1 depending on whether the value of the x parameter is negative, zero, or positive. |
pi() | Double | Returns the value of Pi, i.e., the value of π. |
ln(x) | Double | Returns the natural logarithm of x, i.e., the logarithm of x to the base e. If x is less than or equal to 0, NULL is returned. |
log2(x) | Double | Returns the binary logarithm of x. If x is less than or equal to |
log10(x) | Double | Returns the logarithm of x to the base 10. If x is less than or equal to |
String functions
Function | Output type | Description |
length(str) | Bigint | Returns the length of the string in bytes. |
concat(str1,str2...) | Varchar | Returns the result of string concatenation. You can specify multiple strings. |
to_base64(str) | Varchar | Returns the Base64-encoded result of the string specified by the str parameter. |
hex(str) | Bigint | Returns the hexadecimal string representation of the value of the str parameter, where each byte of each character in the value of the str parameter is converted to two hexadecimal digits. |
hex(str) | Varchar | Returns the hexadecimal string representation of x. For example, the return value of |
instr(str1,substr) | Bigint | Returns the position of the first occurrence of the value of the substr parameter in the string specified by the str parameter. |
lower(str) | Varchar | Returns the lowercase of the string specified by the str parameter. |
ltrim(str) | Varchar | Returns the string obtained after leading spaces are removed from the value of the str parameter. |
lpad(str,len,padstr) | Varchar | Pads the value of the str parameter on the left with the value of the padstr parameter until its length matches the value specified by the len parameter, and returns the resulting padded string. If the length of the value of the str parameter is greater than the value specified by the len parameter, the return value is truncated to the length specified by the len parameter. For example, the return value of |
rtrim(str) | Varchar | Returns the string obtained after trailing spaces are removed from the value of the str parameter. |
rpad(str,len,padstr) | Varchar | Pads the value of the str parameter on the right with the value of the padstr parameter until its length matches the value specified by the len parameter, and returns the resulting padded string. If the length of the value of the str parameter is greater than the value specified by the len parameter, the return value is truncated to the length specified by the len parameter. |
trim(str,len,padstr) | Varchar | Returns the string obtained after leading and trailing spaces are removed from the value of the str parameter. |
substr(str,pos1,pos2) | Varchar | Extracts a specific number of characters specified by the pos2 parameter from the value of the str parameter and returns the extraction result. The extraction starts from the character specified by the pos1 parameter. If the pos2 parameter is not specified in the function, all characters starting from the character specified by the pos1 parameter are extracted as the returned string. |
replace(str,from_str,to_str) | Varchar | Returns the string obtained after all values of the from_str parameter in the value of the str parameter are replaced by the value of the to_str parameter. The search for the value of the from_str parameter is case-sensitive. |
reverse(str) | Varchar | Returns the result obtained after the value of the str parameter is inverted. |
upper(str) | Varchar | Returns the value of the str parameter in uppercase. |