Type conversion functions

BOOLBOOL

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

DATEDATE

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_PARSEDATE_PARSE

Syntax:DATE_PARSE( value )

Converts the value expression to date format. Unlike DATE, it supports multiple formats.

DATETIMEDATETIME

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_PARSEDATETIME_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_CASTDB_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

FLOATFLOAT

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.

GEOPOINTGEOPOINT

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.

GEOPOLYGONGEOPOLYGON

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.

INTINT

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.

STRSTR

Syntax:STR( expression )

Converts the expression expression to string type.

TREETREE

Syntax:TREE( array )

Converts the array expression to Tree of strings format. Can be used to create tree hierarchies.