Type conversion functions

BOOL

Syntax:BOOL( expression )

Converts the expression expression to Boolean type according to the following rules:

TypeFALSETRUE
Fractional number | Integer0, 0.0All others
StringEmpty string ("")All others
BooleanFALSETRUE
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.

The date and time can be converted to the specified time zone when the timezone option is available.

DATETIME_PARSE

Syntax:DATETIME_PARSE( value )

Converts the value expression to date and time format. Unlike DATETIME, it supports multiple formats.

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 sourceData typeNative data typeParameters for native typeComment
ClickHouseDateDate
ClickHouseDateDate32
ClickHouseFractional numberFloat32
ClickHouseFractional numberFloat64
ClickHouseFractional numberDecimalInteger, Integer
ClickHouseIntegerInt8
ClickHouseIntegerInt16
ClickHouseIntegerInt32
ClickHouseIntegerInt64
ClickHouseIntegerUInt8
ClickHouseIntegerUInt16
ClickHouseIntegerUInt32
ClickHouseIntegerUInt64
ClickHouseStringString
PostgreSQLArray of fractional numbersdouble precision[]
PostgreSQLArray of fractional numbersreal[]
PostgreSQLArray of fractional numbersnumeric[]Integer, Integer
PostgreSQLArray of integerssmallint[]
PostgreSQLArray of integersinteger[]
PostgreSQLArray of integersbigint[]
PostgreSQLArray of stringstext[]
PostgreSQLArray of stringscharacter varying[]
PostgreSQLArray of stringsvarchar[]
PostgreSQLFractional numberdouble precision
PostgreSQLFractional numberreal
PostgreSQLFractional numbernumericInteger, Integer
PostgreSQLIntegersmallint
PostgreSQLIntegerinteger
PostgreSQLIntegerbigint
PostgreSQLStringtext
PostgreSQLStringcharacterIntegerAlias: char
PostgreSQLStringcharacter varyingIntegerAlias: varchar
PostgreSQLStringcharIntegerAlias for character
PostgreSQLStringvarcharIntegerAlias for character varying

FLOAT

Syntax:FLOAT( expression )

Converts the expression expression to fractional number format according to the following rules:

TypeValue
Fractional number | IntegerOriginal value.
Date | DatetimeUnix 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.
StringA number from a decimal string.
BooleanTRUE1.0, FALSE0.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.

INT

Syntax:INT( expression )

Converts the expression expression to integer format according to the following rules:

TypeValue
IntegerOriginal value.
Fractional numberInteger part of the number (rounded down).
Date | DatetimeUnix 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.
StringA number from a decimal string.
BooleanTRUE1, FALSE0.

STR

Syntax:STR( expression )

Converts the expression expression to string type.

Previous
Next