Type conversion functions
BOOL
Syntax:BOOL( expression )
Converts the expression expression to Boolean type according to the following rules:
| Type | FALSE |
TRUE |
|---|---|---|
Fractional number | Integer |
0, 0.0 |
All others |
String |
Empty string ("") |
All others |
Boolean |
FALSE |
TRUE |
Date | Datetime |
- | TRUE |
DATE
Syntax:DATE( expression [ , timezone ] )
Converts the expression expression to date format.
The date must be in the format YYYY-MM-DD.
If expression is a number, then the timezone option can be used to convert the date to the specified time zone.
DATE_PARSE
Syntax:DATE_PARSE( value )
Converts the value expression to date format. Unlike DATE, it supports multiple formats.
DATETIME
Syntax:DATETIME( expression [ , timezone ] )
Converts the expression expression to date and time format. When converting Date to DateTime, the time is set to '00:00:00'.
The date must be in the format YYYY-MM-DDThh:mm:ss or YYYY-MM-DD hh:mm:ss.
Numeric values are rendered as time in Unix time format or equal to the number of seconds elapsed since 00:00:00 on January 1, 1970, less the adjustments for leap seconds.
The date and time can be converted to the specified time zone when the timezone option is available. The timezone parameter must be specified in Region/Data_Zone format.
DATETIME_PARSE
Syntax:DATETIME_PARSE( value )
Converts the value expression to date and time format. Unlike DATETIME, it supports multiple formats. The expression is processed on the ClickHouse® source side. For more information on the supported formats, see the relevant ClickHouse® documentation.
DB_CAST
Syntax:DB_CAST( expression, native_type [ , param_1 [ , param_2 ] ] )
Converts the expression expression to database's native type native_type.
The following type casts are supported:
| Data source | Data type | Native data type | Parameters for native type | Comment |
|---|---|---|---|---|
ClickHouse |
Date |
Date |
||
ClickHouse |
Date |
Date32 |
||
ClickHouse |
Fractional number |
Float32 |
||
ClickHouse |
Fractional number |
Float64 |
||
ClickHouse |
Fractional number |
Decimal |
Integer, Integer |
|
ClickHouse |
Integer |
Int8 |
||
ClickHouse |
Integer |
Int16 |
||
ClickHouse |
Integer |
Int32 |
||
ClickHouse |
Integer |
Int64 |
||
ClickHouse |
Integer |
UInt8 |
||
ClickHouse |
Integer |
UInt16 |
||
ClickHouse |
Integer |
UInt32 |
||
ClickHouse |
Integer |
UInt64 |
||
ClickHouse |
String |
String |
||
PostgreSQL |
Array of fractional numbers |
double precision[] |
||
PostgreSQL |
Array of fractional numbers |
real[] |
||
PostgreSQL |
Array of fractional numbers |
numeric[] |
Integer, Integer |
|
PostgreSQL |
Array of integers |
smallint[] |
||
PostgreSQL |
Array of integers |
integer[] |
||
PostgreSQL |
Array of integers |
bigint[] |
||
PostgreSQL |
Array of strings |
text[] |
||
PostgreSQL |
Array of strings |
character varying[] |
||
PostgreSQL |
Array of strings |
varchar[] |
||
PostgreSQL |
Fractional number |
double precision |
||
PostgreSQL |
Fractional number |
real |
||
PostgreSQL |
Fractional number |
numeric |
Integer, Integer |
|
PostgreSQL |
Integer |
smallint |
||
PostgreSQL |
Integer |
integer |
||
PostgreSQL |
Integer |
bigint |
||
PostgreSQL |
String |
text |
||
PostgreSQL |
String |
character |
Integer |
Alias: char |
PostgreSQL |
String |
character varying |
Integer |
Alias: varchar |
PostgreSQL |
String |
char |
Integer |
Alias for character |
PostgreSQL |
String |
varchar |
Integer |
Alias for character varying |
FLOAT
Syntax:FLOAT( expression )
Converts the expression expression to fractional number format according to the following rules:
| Type | Value |
|---|---|
Fractional number | Integer |
Original value. |
Date | Datetime |
Unix time corresponding to the date and time. If the value contains time zone data, it's used in the calculation. If the time zone is unknown, the time is set in UTC. |
String |
A number from a decimal string. |
Boolean |
TRUE — 1.0, FALSE — 0.0. |
GEOPOINT
Syntax:GEOPOINT( value_1 [ , value_2 ] )
Generates a Geopoint type value. For the input, it accepts a string, a "geopoint" type value, or coordinates — latitude value_1 and longitude value_2. If a single string is input, it must contain a list of two numbers (latitude and longitude) in JSON syntax.
GEOPOLYGON
Syntax:GEOPOLYGON( value )
Converts the value expression to geopolygon format. At input, the function accepts strings in [[[lat_1,lon_1], [lat_2,lon_2], ..., [lat_N-1,lon_N-1], [lat_N,lon_N]]] format.
INT
Syntax:INT( expression )
Converts the expression expression to integer format according to the following rules:
| Type | Value |
|---|---|
Integer |
Original value. |
Fractional number |
Integer part of the number (rounded down). |
Date | Datetime |
Unix time corresponding to the date and time. If the value contains time zone data, it's used in the calculation. If the time zone is unknown, the time is set in UTC. |
String |
A number from a decimal string. |
Boolean |
TRUE — 1, FALSE — 0. |
STR
Syntax:STR( expression )
Converts the expression expression to string type.
TREE
Syntax:TREE( array )
Converts the array expression to Tree of strings format. Can be used to create tree hierarchies.