All Products
Search

This Product

    Resource Management:Supported functions

    Document Center

    Resource Management:Supported functions

    Last Updated:Jun 17, 2026

    You can use the following functions in advanced search to convert, format, and aggregate resource data.

    to_char(timestamp/timestamptz, text)

    • Return value type: TEXT.

    • Description: Converts a timestamp into a string. By default, the time range is from 1925 to 2282.

    • Format

      • The first parameter specifies the timestamp to be converted, and the second parameter specifies the conversion format.

      • YYYY indicates the year, MM indicates the month, DD indicates the day, HH indicates the hour, MI indicates the minute, and SS indicates the second.

      • You can use the to_char function to convert a timestamp into a string in the 24-hour clock or 12-hour clock. HH24 indicates the 24-hour clock, and HH12 indicates the 12-hour clock. The 12-hour clock is used by default.

    • Examples

      Scenario

      Request

      Response

      Convert the current timestamp into a string in the 24-hour clock. 

      SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');

      20221208

    to_timestamp(text, text)

    • Return value type: TIMESTAMPTZ.

    • Description: Converts a string into a timestamp. By default, the time range is from 1925 to 2282.

    • Format: The first parameter specifies the string to be converted, and the second parameter specifies the conversion format.

    • Examples

      Scenario

      Request

      Response

      Convert a field value of the TEXT type into a date. 

      SELECT TO_TIMESTAMP(properties ->> 'ExpireTime', 'YYYY-MM-DD') FROM resources WHERE resource_type = 'ACS::RDS::DBInstance';

      2001-09-28

    clock_timestamp()

    • Return value type: TIMESTAMPTZ.

    • Description: Returns the current time.

    • Examples

      Scenario

      Request

      Response

      Query the current time. 

      SELECT CLOCK_TIMESTAMP();

      2023-09-02T11:47:19Z

    concat(param1 [,param2]...)

    • Return value type: TEXT.

    • Description: Concatenates all values except NULL.

    • Format: The parameters specify the values to concatenate. Values can be of any data type. The first parameter is required, and other parameters are optional.

    • Examples

      Scenario

      Request

      Response

      Concatenate all values except NULL. 

      SELECT CONCAT('abcde', 2, NULL, 22);

      concat

      --------

      abcde222

    concat_ws(separator, param1 [,param2]...)

    • Return value type: TEXT.

    • Description: Concatenates all values except NULL, using the first parameter as a delimiter.

    • Format: The first parameter specifies a delimiter. The remaining parameters specify the values to concatenate. Values can be of any data type. The first and second parameters are required, and other parameters are optional.

    • Examples

      Scenario

      Request

      Response

      Use a delimiter to concatenate all values, skipping NULL.

      		 
      SELECT concat_ws(',', 'abcde', 2, NULL, 22);

      concat_ws

      --------

      abcde,2,22

    substring()

    • Return value type: TEXT.

    • Description: Extracts a substring from a string.

    • Format: substring(string[FROM start][FOR length]) or substring(string FROM pattern).

      • string: required. The string.

      • start: optional. The start position of the substring. The default value is 1, which indicates the beginning of the string.

      • length: optional. The length of the substring. If you do not specify this parameter, the substring from the start position to the end of the string is extracted.

      • pattern: required. A regular expression. Portable Operating System Interface (POSIX) regular expressions are used in the FROM pattern clause, and SQL regular expressions are used in the SIMILAR pattern clause.

    • Examples

      Scenario

      Request

      Response

      Extract a specified substring from a string. 

      SELECT SUBSTRING('Thomas' FROM 2 FOR 3);

      substring

      -------

      hom

      Extract a substring that matches a POSIX regular expression from a string. 

      SELECT SUBSTRING('Thomas' FROM '...$');

      substring

      --------

      mas

    char_length(string)

    • Return value type: INT.

    • Description: Returns the number of characters in a string.

    • Examples

      Scenario

      Request

      Response

      Query the length of a string. 

      SELECT CHAR_LENGTH('jose');

      4

    length(string)

    • Return value type: INT.

    • Description: Returns the byte length of a string. With UTF-8 encoding, a Chinese character occupies three bytes, and a digit or letter occupies one byte.

    • Examples

      Scenario

      Request

      Response

      Query the length of a string. 

      SELECT LENGTH('jose');

      4

    lower(string)

    • Return value type: TEXT.

    • Description: Converts a string to lowercase.

    • Examples

      Scenario

      Request

      Response

      Convert a string to lowercase letters.

      		 
      SELECT LOWER('TOM');

      tom

    upper(string)

    • Return value type: TEXT.

    • Description: Converts a string to uppercase.

    • Examples

      Scenario

      Request

      Response

      Convert a string to uppercase letters.

      		 
      SELECT UPPER('tom');

      TOM

    starts_with(string, string)

    • Return value type: TEXT.

    • Description: Checks whether a string starts with a given prefix. Returns true if the string starts with the prefix, or false otherwise.

    • Examples

      Scenario

      Request

      Response

      Check whether the string alphabet starts with alph. 

      SELECT starts_with('alphabet', 'alph');

      true

    case

    • Description: Evaluates conditions in order and returns the value for the first true condition.

    • Examples

      Scenario

      Request

      Response

      Convert the status of an Elastic Compute Service (ECS) instance. 

      SELECT
 resource_id,
 CASE
 properties ->> 'Status'
 WHEN 'ECS Pending' THEN 'Creating'
 WHEN 'ECS Running' THEN 'Running'
 WHEN 'ECS Starting' THEN 'Starting'
 WHEN 'ECS Stopping' THEN 'Stopping'
 WHEN 'ECS Stopped' THEN 'Stopped'
 END AS "Status",
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
ORDER BY
 resource_id;
      		 
      resource_id Status
------------+-----------
1 i-xxx				Running

    count

    • Return value type: INT.

    • Description: Returns the number of rows that match the specified conditions.

    • Examples

      Scenario

      Request

      Response

      Calculate the number of Alibaba Cloud resources. 

      SELECT count(*) FROM resources;
      		 
      1123

    max

    • Description: Returns the maximum value of a numeric expression.

    • Examples

      Scenario

      Request

      Response

      Query the latest creation time of resources. 

      SELECT MAX(create_time) FROM resources;

      		 
      2023-09-13T07:32:37Z

    min

    • Description: Returns the minimum value of a numeric expression.

    • Examples

      Scenario

      Request

      Response

      Query the earliest creation time of resources. 

      SELECT MIN(create_time) FROM resources;

      		 
      2021-08-20T02:20:37Z

    jsonb_array_elements(jsonb)

    • Description: Expands a JSONB array to a set of JSONB values.

    • Examples

      Scenario

      Request

      Response

      Query all IP addresses. 

      SELECT
 DISTINCT JSONB_ARRAY_ELEMENTS(ip_addresses) AS ip_address
FROM
 resources
      		 
      ip_address
-----------
 "172.X.X.1"
 "172.X.X.2"
 "172.X.X.3"

    jsonb_array_elements_text(jsonb)

    • Description: Expands a JSONB array to a set of TEXT values.

    • Examples

      Scenario

      Request

      Response

      Query all IP addresses. 

      SELECT
 DISTINCT JSONB_ARRAY_ELEMENTS_TEXT(ip_addresses) AS ip_address
FROM
 resources
      		 
      ip_address
-----------
 172.X.X.1
 172.X.X.2
 172.X.X.3

    jsonb_object_keys(jsonb)

    • Description: Returns a set of keys in the outermost JSONB object.

    • Examples

      Scenario

      Request

      Response

      Query all tag keys. 

      SELECT
 DISTINCT JSONB_OBJECT_KEYS(tags) AS tag_key
FROM
 resources
      		 
      tag_key
------------------
 key_1
 key_2