CAST #

Convert the input value to the specified data type. Unlike reinterpret functions, CAST attempts to use the new data type to represent the same value. If the conversion cannot be completed, an exception is raised.
Syntax

CAST(x, T) 
CAST(x AS t)

Parameters

• x— The value to convert. Can be any type.
• T— The name of the target data type. String type.
• t— Target data type.

Return value
The converted value.

Example

SELECT CAST(toInt8(-1), 'UInt8') AS cast_int_to_uint, CAST(1.5 AS Decimal(3,2)) AS cast_float_to_decimal;

┌─cast_int_to_uint─┬─cast_float_to_decimal─┐ 
│ 255              │ 1.50                  │ 
└──────────────────┴───────────────────────┘

SELECT'2016-06-15 23:00:00' AS timestamp,CAST(timestamp AS DateTime) AS datetime,CAST(timestamp AS Date) AS date,CAST(timestamp, 'String') AS string,CAST(timestamp, 'FixedString(22)') AS fixed_string;

┌─timestamp───────────┬─datetime────────────┬─date───────┬─string──────────────┬─fixed_string────────┐ 
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘

Conversion to FixedString(N) is only applicable to parameters of String or FixedString type.
Supports type conversion to Nullable and back.

SELECT toTypeName(number) FROM numbers(2);

┌─toTypeName(number)─┐ 
│ UInt64             │ 
│ UInt64             │ 
└────────────────────┘

SELECT toTypeName(CAST(number, 'Nullable(UInt64)')) FROM numbers(2);

┌─toTypeName(CAST(number, 'Nullable(UInt64)'))─┐ 
│ Nullable(UInt64)                             │ 
│ Nullable(UInt64)                             │ 
└──────────────────────────────────────────────┘

reinterpretAsDate #

These functions accept a string and interpret the bytes at the beginning of the string as a number in host order (little endian). If the string is not long enough, these functions work as if the string is padded with the necessary quantity of empty bytes. If the string is longer than needed, the extra bytes are ignored. The date is interpreted as the total days since the start of the Unix epoch.
Syntax

reinterpretAsDate(fixed_string)

Parameters

• fixed_string— String represented by bytes.

Return value

• Datetime.

Example

SELECT reinterpretAsDate(reinterpretAsString(toDate('2019-01-01')));

┌─reinterpretAsDate(reinterpretAsString(toDate('2019-01-01')))─┐ 
│ 2019-01-01                                                   │ 
└──────────────────────────────────────────────────────────────┘

reinterpretAsDateTime #

This function accepts a string and interprets the bytes at the beginning of the string in host byte order (little endian) as a numerical value. If the string is not long enough, these functions work as if the string is padded with the necessary quantity of empty bytes. If the string is longer than needed, the extra bytes are ignored. Returns a datetime, where the time value is interpreted as the seconds calculated from the start of the Unix epoch (usually refers to January 1, 1970, 00:00:00 UTC).
Syntax

reinterpretAsDateTime(fixed_string)

Parameters

• fixed_string— String represented by bytes.

Return value

• Date and time. Type: DateTime.

Example

SELECT reinterpretAsDateTime(reinterpretAsString(toDateTime('2019-01-01 00:00:00')));

┌─reinterpretAsDateTime(reinterpretAsString(toDateTime('2019-01-01 00:00:00')))─┐ 
│ 2019-01-01 00:00:00                                                           │ 
└───────────────────────────────────────────────────────────────────────────────┘

reinterpretAsFixedString #

This function accepts a number, date, or datetime and returns a FixedString containing the bytes representing the corresponding value in host order (little endian). Empty bytes are removed from the end. For example, a UInt32 type value of 255 is a FixedString one byte long.
Syntax

reinterpretAsFixedString(x)

Parameters

• x— Number, Date, or Date with Time.

Return value

• Fixed String.

Example

SELECT reinterpretAsFixedString(toDate('2019-01-01'));

┌─reinterpretAsFixedString(toDate('2019-01-01'))─┐ 
│ �E                                             │ 
└────────────────────────────────────────────────┘

reinterpretAsString #

This function receives a Number, Date, or Date with Time, and returns a String containing the bytes representing the corresponding value in host order (little-endian). Empty bytes are removed from the end. For example, a UInt32 type value of 255 is a String with a length of 1 byte.
Syntax

reinterpretAsString(value)

Param

• value— Number, Date, or Date with Time

Return value

• String represented by bytes.

Example

SELECT reinterpretAsString(toDate('2019-01-01'));

┌─reinterpretAsString(toDate('2019-01-01'))─┐ 
│ �E                                        │ 
└───────────────────────────────────────────┘

toDate #

Convert String, Date, DateTime, or UInt* Number to Date type.
Syntax

toDate(time)

Param

• time— String, Date, DateTime, or UInt* Number.

Return value

• Date

Example

SELECT toDate('2019-01-01');

┌─cast_int_to_uint─┬─cast_float_to_decimal─┐ 
│ 255              │ 1.50                  │ 
└──────────────────┴───────────────────────┘

SELECT toDate(1);

┌─toDate(1)──┐ 
│ 1970-01-02 │ 
└────────────┘

SELECT toDate(toDateTime('2019-01-01 00:00:00'));

┌─toDate(toDateTime('2019-01-01 00:00:00'))─┐ 
│ 2019-01-01                                │ 
└───────────────────────────────────────────┘

toDecimal (32|64) #

Convert value to Decimal data type with precision S. value can be Number or String. S (scale) Param specifies the number of decimal places.
Syntax

toDecimal32(value, S) 
toDecimal64(value, S)

Param

• value can be Number or String.
• S (scale) Param specifies the number of decimal places.
• SETTINGS output_format_decimal_trailing_zeros: Optional, used to control whether trailing zeros in decimals are displayed. When set to 1, all trailing zeros within the specified decimal places are forcibly retained. When set to 0, meaningless trailing zeros are automatically omitted.

Return value

• Decimal

Example

SELECT toDecimal32(1, 2) SETTINGS output_format_decimal_trailing_zeros = 1

┌─toDecimal32(1, 2)─┐ 
│ 1.00              │ 
└───────────────────┘

SELECT toDecimal32('1', 2) SETTINGS output_format_decimal_trailing_zeros = 1

┌─toDecimal32('1', 2)─┐ 
│ 1.00                │ 
└─────────────────────┘

toDecimal(32|64)OrNull #

Convert an input string to a Nullable(Decimal(P,S)) data type value.
To obtain NULL as the value when a parsing error occurs for the input value, use these functions instead of the standard function toDecimal*().
Syntax

toDecimal32OrNull(expr, S) 
toDecimal64OrNull(expr, S)

Param

• expr— Expression returning a value of String data type. ByteHouse requires the text form of decimal numbers. For example, '1.111'.
• S— Number of decimal places in the result value.

Return value
Value of data type Nullable(Decimal(P,S)). This value contains:

• If ByteHouse interprets the input string as a number, the value is a number with decimals and S decimal places.
• If ByteHouse cannot interpret the input string as a number, or the entered number contains more than S decimal places, NULL is returned.

Example

SELECT toDecimal32OrNull(toString(-1.111), 5) AS val, toTypeName(val);

┌─val──────┬─toTypeName(toDecimal32OrNull(toString(-1.111), 5))─┐ 
│ -1.11100 │ Nullable(Decimal(9, 5))                            │ 
└──────────┴────────────────────────────────────────────────────┘

SELECT toDecimal32OrNull(toString(-1.111), 2) AS val, toTypeName(val); 
sql

┌─val──┬─toTypeName(toDecimal32OrNull(toString(-1.111), 2))─┐ 
│ ᴺᵁᴸᴸ │ Nullable(Decimal(9, 2))                            │ 
└──────┴────────────────────────────────────────────────────┘

toDecimal(32|64)OrZero #

Convert the input value to Decimal(P,S) data type.
To obtain 0 as the value when a parsing error occurs for the input value, use these functions instead of the standard function toDecimal*().
Syntax

toDecimal32OrZero( expr, S) 
toDecimal64OrZero( expr, S)

Param

• expr— Expression data type. ByteHouse requires the text representation of decimal numbers. For example, '1.111'.
• S— Number of decimal places in the resulting value.

Return value
Value of data type Nullable(Decimal(P,S)). This value contains:

• If ClickHouse interprets the input string as a number, the result is a number with decimal places.
• If ClickHouse cannot interpret the input string as a number, or the input number contains more than S decimal places, the result is 0 with decimal places.

Example

SELECT toDecimal32OrZero(toString(-1.111), 5) AS val, toTypeName(val);

┌─val──────┬─toTypeName(toDecimal32OrZero(toString(-1.111), 5))─┐ 
│ -1.11100 │ Decimal(9, 5)                                      │ 
└──────────┴────────────────────────────────────────────────────┘

SELECT toDecimal32OrZero(toString(-1.111), 2) AS val, toTypeName(val);

┌─val──┬─toTypeName(toDecimal32OrZero(toString(-1.111), 2))─┐ 
│ 0.00 │ Decimal(9, 2)                                      │ 
└──────┴────────────────────────────────────────────────────┘

toFixedString #

Converts a String type parameter to FixedString(N) type (a string with a fixed length of N). N must be a constant.
If the number of bytes in the string is less than N, pad with zero bytes on the right. If the number of bytes in the string is greater than N, an exception is thrown.
Syntax

toFixedString(s, N)

Parameters

• s: The string to be converted to a fixed string. Type: String.
• N: Length N, type: constant.

Return value

• Fixed string

Example

SELECT toFixedString('1234', 5)

┌─toFixedString('1234', 5)─┐ 
│ 1234                     │ 
└──────────────────────────┘

toInt(8|16|32|64) #

Convert the input value to Int data type.
Syntax

toInt8(expr) 
toInt16(expr) 
toInt32(expr) 
toInt64(expr)

Parameters

• expr— Expression that returns a number or a decimal number string. Binary, octal, and hexadecimal number representations are not supported. Leading zeros are removed.

Return value

• Integer value of data type Int8, Int16, Int32, Int64.

The function uses rounding towards zero>#Rounding_towards_zero, which means it truncates the decimal part of the number.
The function behavior for NaN and Inf parameters is undefined.
Usually, converting values from one data type to another is unsafe and may cause data loss. If you try to convert a value from a larger data type to a smaller one, or between different data types, data loss may occur.
Example

SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8);

┌─toInt64(nan)─────────┬─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐ 
│ -9223372036854775808 │ 32          │ 16            │ 8           │ 
└──────────────────────┴─────────────┴───────────────┴─────────────┘

toInt(8|16|32|64)OrNull #

Receives a String type parameter and tries to parse it as Int (8|16|32|64). If parsing fails, returns NULL.
Syntax

toInt8OrNull(expr) 
toInt16OrNull(expr) 
toInt32OrNull(expr) 
toInt64OrNull(expr)

Parameters

• expr— Expression that returns a number or a decimal number string. Binary, octal, and hexadecimal number representations are not supported. Leading zeros are removed.

Return value

• Integer value of data type Int8, Int16, Int32, Int64.

The function uses rounding towards zero>#Rounding_towards_zero, which means it truncates the fractional part of the number.
The function behavior for NaN and Inf parameters is undefined.
When converting values from one data type to another, you should keep in mind that this is generally an unsafe operation and may result in data loss. Data loss may occur if you attempt to convert values from a larger data type to a smaller data type, or when converting values between different data types.
Example

SELECT toInt64OrNull('123123'), toInt8OrNull('123qwe123');

┌─toInt64OrNull('123123')─┬─toInt8OrNull('123qwe123')─┐ 
│                  123123 │                      ᴺᵁᴸᴸ │ 
└─────────────────────────┴───────────────────────────┘

toInt(8|16|32|64)OrZero #

This function receives a string parameter and tries to parse it as Int (8 | 16 | 32 | 64). If parsing fails, it returns 0.
Syntax

toInt8OrZero(expr) 
toInt16OrZero(expr) 
toInt32OrZero(expr) 
toInt64OrZero(expr)

Parameter

• expr— The expression returns a number or a string representing a decimal number. Binary, octal, and hexadecimal number representations are not supported. Leading zeros are removed.

Return value

• An integer value of data type Int8, Int16, Int32, or Int64.

The function uses rounding towards zero>#Rounding_towards_zero, which means it truncates the fractional part of the number.
The function behavior for NaN and Inf parameters is undefined.
When converting values from one data type to another, you should keep in mind that this is generally an unsafe operation and may result in data loss. Data loss may occur if you attempt to convert values from a larger data type to a smaller data type, or when converting values between different data types.
Example

SELECT toInt64OrZero('123123'), toInt8OrZero('123qwe123');

┌─toInt64OrZero('123123')─┬─toInt8OrZero('123qwe123')─┐ 
│ 123123                  │ 0                         │ 
└─────────────────────────┴───────────────────────────┘

toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second) #

Converts a numeric parameter to an interval data type.
Syntax

toIntervalSecond(number) 
toIntervalMinute(number) 
toIntervalHour(number) 
toIntervalDay(number) 
toIntervalWeek(number) 
toIntervalMonth(number) 
toIntervalQuarter(number) 
toIntervalYear(number)

Parameter

• number— Interval duration. Positive integer.

Return value

• A value of Interval data type.

Example

WITH 
    toDate('2019-01-01') AS date,INTERVAL 1 WEEK AS interval_week, 
    toIntervalWeek(1) AS interval_to_week 
SELECT date + interval_week,date + interval_to_week;

┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┐ 
│ 2019-01-08                │ 2019-01-08                   │ 
└───────────────────────────┴──────────────────────────────┘

toLowCardinality #

Converts the input parameter to the LowCardinality version of the same data type.
To convert data from the LowCardinality data type, use the CAST function. For example, CAST(x as String).
Syntax

toLowCardinality(expr)

Parameter

• expr— The expression produces one of the supported data types.

Return value

• The result of expr. expr type LowCardinality(expr_result_type)

Example

SELECT toLowCardinality('1');

┌─toLowCardinality('1')─┐ 
│ 1                     │ 
└───────────────────────┘

toString #

Functions for converting between numbers, strings (but not FixedString), dates, and datetime values.
All of these functions accept one parameter.
When converting to or from a string, the value's format or parsing rules are the same as the TabSeparated format (and almost all other text formats). If the string cannot be parsed, an exception is thrown and the request is canceled.
When converting a date to a number or vice versa, the date corresponds to the number of days since the start of the Unix epoch.
When converting a datetime value to a number or vice versa, the datetime value corresponds to the number of seconds since the start of the Unix epoch.
The date and datetime formats for the toDate/toDateTime functions are defined as follows:

YYYY-MM-DD 
YYYY-MM-DD hh:mm:ss

The exception is that when converting from UInt32, Int32, UInt64, or Int64 number types to Date, if the number is greater than or equal to 65536, then the number is interpreted as a Unix timestamp (instead of number of days) and rounded to a date. This allows support for the common "toDate(unix_timestamp)" usage. Otherwise, an error occurs and the more cumbersome "toDate(toDateTime(unix_timestamp))" must be used.
Conversion between date and datetime values is performed naturally: by adding zero time or removing the time.
Conversion between number types uses the same assignment rules as between different number types in C++.
Additionally, the toString function for the DateTime parameter can accept a second string parameter, which contains the timezone name. Example: Asia/Yekaterinburg In this case, the time is formatted according to the specified timezone.
Syntax

toString(value)

Parameter

• value— number, string, date, and datetime value

Return value

• String

Example

SELECT 
    now() AS now_local, 
    toString(now(), 'Asia/Yekaterinburg') AS now_yekat;

┌─now_local───────────┬─now_yekat───────────┐ 
│ 2021-08-18 15:25:59 │ 2021-08-18 12:25:59 │ 
└─────────────────────┴─────────────────────┘

toStringCutToZero #

Accepts a string or FixedString parameter. Returns a string truncated at the first zero byte found.
Syntax

toStringCutToZero(s)

Parameter

• s— string or FixedString.

Return value

• truncated string

Example

SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut;

┌─s───┬─s_cut─┐ 
│ foo │ foo   │ 
└─────┴───────┘

SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut;

┌─s──────┬─s_cut─┐ 
│ foobar │ foo   │ 
└────────┴───────┘

toUInt8 #

Converts the input value to the UInt data type. This series of functions includes:
Syntax

toUInt8(expr) 
toUInt16(expr) 
toUInt32(expr) 
toUInt64(expr)

Parameter

• expr— an expression that returns a number or a decimal number string. Binary, octal, and hexadecimal number representations are not supported. Leading zeros are removed.

Return value

• Integer values of data types Int8, Int16, Int32, and Int64.

The function uses rounding towards zero #Rounding_towards_zero, which means it truncates the decimal part of the number.
The function's behavior for negative parameters, as well as NaN and Inf parameters, is undefined. If you pass a string containing a negative number (for example, '-32'), ByteHouse will raise an exception.
When you convert values from one data type to another, you should remember that, in most cases, this is an unsafe operation and may result in data loss. Data loss may occur if you try to convert values from a larger data type to a smaller data type, or between different data types.
Example

SELECT toUInt64(nan), toUInt32(-32), toUInt16('16'), toUInt8(8.8);

┌─toUInt64(nan)───────┬─toUInt32(-32)─┬─toUInt16('16')─┬─toUInt8(8.8)─┐ 
│ 9223372036854775808 │ 4294967264    │ 16             │ 8            │ 
└─────────────────────┴───────────────┴────────────────┴──────────────┘

toUnixTimestamp #

For DateTime parameters: converts the value to a number of type UInt32—a Unix timestamp.
For string parameters: according to the timezone (optional second parameter, default is the server timezone), converts the input string to date and time and returns the corresponding Unix timestamp.
Syntax

toUnixTimestamp(datetime) 
toUnixTimestamp(str, [timezone]) 
sql

Parameters

• datetime: date and time
• str: date and time string
• timezone: optional, timezone

Return value

• Returns unix timestamp. Type: UInt32.

Example

SELECT toUnixTimestamp('2017-11-05 08:07:47', 'Asia/Tokyo') AS unix_timestamp

┌─unix_timestamp─┐ 
│ 1509836867     │ 
└────────────────┘

SELECT toUnixTimestamp(toDateTime('2017-11-05 08:07:47', 'Asia/Tokyo')) AS unix_timestamp

┌─unix_timestamp─┐ 
│ 1509836867     │ 
└────────────────┘