- Releases Notes and Announcements
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- SQL Developer Guide
- Python Developer Guide
- ETL Developer Guide
- FAQ
- Contact Us
- Releases Notes and Announcements
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- SQL Developer Guide
- Python Developer Guide
- ETL Developer Guide
- FAQ
- Contact Us
Functions
Note
For the CAST() function, if you convert INTERVAL to a number, the result will be a literal value (which may not be what you expect). For example,
CAST(INTERVAL '1234' MINUTE AS BIGINT) returns 1234 instead of the number of milliseconds represented by INTERVAL.Function | Description |
CAST(value AS type) | Returns a new value being cast to the type specified by type. For example, CAST( hello AS VARCHAR) returns hello converted to VARCHAR type. |
CAN_CAST_TO(str, type) | Checks whether the string str can be converted to the type specified by type, and returns a Boolean value. The return value can be used as a condition in a CASE statement. For example, CAN_CAST_TO('123456', 'INTEGER') returns True, and CAN_CAST_TO('a145', 'DOUBLE') returns False. |
TO_TIMESTAMP(string, simple_format) | Converts string to a timestamp in the simple_format, which is compatible with Java's SimpleDateFormat. UTC+8 is used by default. Example: TO_TIMESTAMP(ts, 'yyyy-MM-dd HH:mm:ss') |
DATE_FORMAT_SIMPLE(timestamp, simple_format) | Converts a field of BIGINT (Long) type (which represents a Unix timestamp in milliseconds) to a string in the simple_format, which is compatible with Java's SimpleDateFormat. For example, DATE_FORMAT_SIMPLE(unix_ts, 'yyyy-MM-dd HH:mm:ss') returns a string similar to "2020-01-01 12:13:14". |
DATE_FORMAT(timestamp, format) | Converts a field of Timestamp type to a string in the format, which is compatible with Java's SimpleDateFormat. For example, DATE_FORMAT(ts, 'yyyy-MM-dd HH:mm:ss') returns a string similar to "2020-01-01 12:13:14". |
TIMESTAMP_TO_LONG(timestamp) or TIMESTAMP_TO_LONG(timestamp, mode) | Converts a TIMESTAMP type parameter to a value of BIGINT (Long) type. If mode is 'SECOND', converts to a Unix timestamp in seconds, for example, 1548403425. If mode is any other value or not specified, converts to a Unix timestamp in milliseconds, for example, 1548403425512. |
Examples
CAST
Feature description: Returns a new value being cast to the type specified by
type.
Syntax: CAST(value AS type)
Example statement: SELECT CAST(var1 AS VARCHAR) FROM TEST;
Test data and result:Test Data (INT var1) | Test Result (VARCHAR) |
58 | '58' |
CAN_CAST_TO
Feature description: Checks whether the string
str can be converted to the type specified by type, and returns a Boolean value. The return value can be used as a condition in a CASE statement.
Syntax: CAN_CAST_TO(str, type)
Example statement: SELECT CAN_CAST_TO(var1,type) FROM Test;
Test data and result:Test Data (VARCHAR var1) | Test Data (VARCHAR type) | Test Result (BOOLEAN) |
123456 | INTEGER | true |
DATE_FORMAT_SIMPLE
Feature description: Converts a field of BIGINT (Long) type (which represents a Unix timestamp in milliseconds) to a string (GMT+8 time zone) using format that is compatible with Java's SimpleDateFormat.
Syntax: DATE_FORMAT_SIMPLE(timestamp, simple_format)
Example statement: SELECT DATE_FORMAT_SIMPLE(unix_ts,'yyyy-MM-dd HH:mm:ss') FROM Test;
Test data and result:
Test Data (unix_ts) | Test Result (VARCHAR) |
1627997937000 | 2021-08-03 21:38:57 |
DATE_FORMAT
Feature description: Converts a field of Timestamp type to a string using format that is compatible with Java's SimpleDateFormat.
Syntax: DATE_FORMAT(timestamp, format)
Example statement: SELECT DATE_FORMAT(timestamp,format) FROM Test;
Test data and result:
Test Data (timestamp) | Test Data (format) | Test Result (VARCHAR) |
2021-01-01 12:13:14 | yyMMdd | 210101 |
2021-01-01 12:13:14 | yyyyMMdd | 20210101 |
Yes
No
Was this page helpful?