Type Conversion functions
Convert between different data types.
Type conversion functions allow you to convert values from one data type to another. These functions are essential for data manipulation, ensuring compatibility between different data types, and preparing data for specific operations or analyses.
This section covers a wide range of conversion functions, including:
- Conversions between numeric types (e.g., integers to floats)
- Conversions to and from string types
- Date and time conversions
- Boolean conversions
- Special type conversions (e.g., UUID, IPv4/IPv6)
ClickHouse function reference
toBool
Converts an input value to a value of type Bool. Throws an exception in case of an error.
Syntax:
Arguments:
expr— Expression returning a number or a string.
Supported arguments:
- Values of type (
UInt8,UInt16,UInt32,UInt64,UInt128,UInt256,Int8,Int16,Int32,Int64,Int128,Int256). - Values of type (
Float32,Float64). - Strings “true” or “false” (case-insensitive).
Returns:
- Returns true or false based on evaluation of the argument. Bool.
Example:
Query:
Result:
In this example:
int_valueconverts an integer 1 to true.float_valueconverts a float 1.01 to true.string_valueconverts the string ‘true’ to true.case_insensitive_valueconverts the string ‘FALSE’ to false, demonstrating case-insensitivity.
toInt8
Converts an input value to a value of type Int8. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
Unsupported arguments:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8('0xc0fe');.
Returns:
- 8-bit integer value.
Int8.
- If the input value cannot be represented within the bounds of
Int8(-128 to 127), overflow or underflow of the result occurs. This is not considered an error. For example:SELECT toInt8(128) == -128;. - The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example:
int8_valueshows a direct conversion of an integer toInt8.float_to_int8demonstrates how the function truncates the decimal part.string_to_int8shows conversion from a string representation.
See also:
toInt8OrZero
Converts an input value to a value of type Int8 but returns 0 if the conversion is unsuccessful.
Syntax:
Arguments:
expr(String, numeric, orNull): Value to convert.
Returns:
- Integer value converted from the input.
- 0 if the conversion is unsuccessful.
Type: Int8
Example:
Query:
Result:
In this example:
123is successfully converted to 123- ‘abc’ cannot be converted, so 0 is returned
- NULL is converted to 0
See Also
toInt8OrNull
Converts an input value to a value of type Int8, but returns NULL if the conversion is not possible.
Syntax:
Arguments:
expr(String, number, or NULL): Value to convert.
Returns:
- An
Int8value if the conversion was successful. - NULL if the conversion was not possible.
Example:
Query:
Result:
In this example:
123is successfully converted to Int8.'abc'cannot be converted, so NULL is returned.NULLinput results in NULL output.'42'is successfully parsed and converted to Int8.8.5is truncated to 8 (rounding towards zero).256is outside the range of Int8 (-128 to 127), so NULL is returned.
See Also
toInt8OrDefault
Converts an input value to a value of type Int8, but returns the default value if parsing fails.
Syntax:
Arguments:
expr(numeric orString): Value to convert. Expression returning a number or a string representation of a number.default(Int8, optional): Default value to return if parsing fails.
Returns:
- An 8-bit signed integer value if conversion is successful.
- The
defaultvalue if specified, or 0 if not, when conversion fails.
Example:
Query:
Result:
In this example:
valid_intsuccessfully converts the string ‘8’ to the Int8 value 8.invalid_intfails to convert ‘guacamole’ to an Int8, so it returns the default value -1.
- The function uses rounding towards zero, truncating fractional digits.
- If the input value is outside the range of Int8 (-128 to 127), it will overflow or underflow without raising an error.
- The default value’s type should match the target Int8 type.
See also:
toInt16
Converts an input value to a value of type Int16. Throws an exception in case of an error.
Syntax:
Arguments:
expr— Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
Unsupported arguments:
- String representations of
Float32/64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16('0xc0fe');.
Returns:
- 16-bit integer value.
Int16.
- If the input value cannot be represented within the bounds of
Int16(-32,768 to 32,767), overflow or underflow of the result occurs. This is not considered an error. For example:SELECT toInt16(32768) == -32768;. - The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Result:
In this example, we convert various representations of -16 to Int16. Note how the fractional part is truncated in the second conversion.
See also:
toInt16OrZero
Converts an input value to a value of type Int16 but returns 0 if the conversion is unsuccessful.
Syntax:
Arguments:
x(String,Integer,Float): Value to convert.
Returns:
- Integer value of type
Int16. - 0, if the conversion is unsuccessful.
Example:
Query:
Result:
In this example:
taco_countsuccessfully converts the string ‘16’ to theInt16value 16.spice_levelreturns 0 because ‘spicy’ cannot be converted to anInt16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
See also:
toInt16OrNull
Converts an input value to a value of type Int16 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
x(String,Integer,Float): Value to convert.
Returns:
- Integer value of type
Int16if conversion is successful. - NULL if conversion fails.
Example:
Query:
Result:
In this example:
- ‘16’ is successfully converted to Int16.
- ‘abc’ cannot be converted, so NULL is returned.
- ‘32768’ is out of range for Int16 (which is -32768 to 32767), so NULL is returned.
This function is useful when you want to handle potential conversion errors gracefully without causing query failures. It’s particularly handy when dealing with potentially dirty or unpredictable input data.
See also:
toInt16OrDefault
Like toInt16, this function converts an input value to a value of type Int16 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.default(Int16, optional): The default value to return if parsing to type Int16 is unsuccessful.
Returns:
- 16-bit integer value if successful, otherwise returns the default value if passed or 0 if not.
Int16.
Example:
Result:
In this example:
- For the valid input ‘-16’, the function returns -16.
- For the invalid input ‘abc’, it returns the default value -1.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
See also:
toInt16toInt16OrZerotoInt16OrNull
toInt32
Converts an input value to a value of type Int32. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
Unsupported arguments:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g. SELECT toInt32(‘0xc0fe’);.
If the input value cannot be represented within the bounds of Int32, the result over or under flows. This is not considered an error. For example: SELECT toInt32(2147483648) == -2147483648;
Returns:
- 32-bit integer value.
Int32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Result:
See also:
toInt32OrZero
Converts an input value to a value of type Int32 but returns 0 in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (
U)Int8/16/32/128/256.
Unsupported arguments (return 0):
- String representations of
Float32/64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 32-bit integer value if successful, otherwise 0.
Int32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example:
-32is successfully converted toInt32.'abc'cannot be converted, so 0 is returned.
See also:
toInt32OrNull
Converts an input value to a value of type Int32 but returns NULL in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return NULL):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.
Returns:
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example, ‘-32’ is successfully converted to an Int32 value, while ‘abc’ results in NULL as it cannot be interpreted as a valid integer.
See also:
toInt32OrDefault
Converts an input value to a value of type Int32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression /String) — Expression returning a number or a string representation of a number.default(Int32, optional) — The default value to return if parsing to type Int32 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrDefault('0xc0fe', CAST('-1', 'Int32'));.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 32-bit integer value if successful, otherwise returns the default value if passed or 0 if not.
Int32.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘-32’ is successfully converted to -32
- ‘abc’ cannot be converted, so the default value -1 is returned
- ‘123.45’ is truncated to 123 (rounding towards zero)
- ‘0xc0fe’ is treated as an invalid hexadecimal string, so the default value -1 is returned
See also
toInt64
Converts an input value to a value of type Int64. Throws an exception in case of an error.
Syntax:
Arguments:
expr— Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type (
Float32/Float64).
Unsupported types:
- String representations of (
Float32/Float64) values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64('0xc0fe');.
If the input value cannot be represented within the bounds of Int64, the result over or under flows. This is not considered an error. For example: SELECT toInt64(9223372036854775808) == -9223372036854775808;
Returns:
- 64-bit integer value.
Int64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
See also
toInt64OrZero
Converts an input value to a value of type Int64 but returns 0 in case of an error.
Syntax:
Arguments:
x(String, number, or date/time value): Value to convert.
Returns:
- 64-bit integer value if conversion is successful.
- 0 if conversion fails.
Example:
Query:
Result:
In this example, ‘123’ is successfully converted to 123, while ‘twelve tacos’ fails to convert and returns 0.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
See also:
toInt64OrNull
Converts an input value to a value of type Int64, but returns NULL if the conversion is unsuccessful.
Syntax:
Arguments:
expr(Any): Value to convert. Can be any data type that can be converted toInt64.
Returns:
- A value of type Nullable(
Int64). - NULL if the conversion was unsuccessful.
Example:
Query:
Result:
In this example:
- ‘123’ is successfully converted to
Int64. - ‘12.3’ fails because it’s a float.
- ‘twelve’ fails because it’s not a numeric string.
- ‘9223372036854775807’ is the maximum value for
Int64. - ‘9223372036854775808’ overflows
Int64, so NULL is returned.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
See also:
toInt64OrDefault
Converts an input value to a value of type Int64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression/String): Expression returning a number or a string representation of a number.default(Int64, optional): The default value to return if parsing to type Int64 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toInt64OrDefault('0xc0fe', CAST('-1', 'Int64'));.
If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 64-bit integer value if successful, otherwise returns the default value if passed or 0 if not.
Int64.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘-64’ is successfully converted to -64
- ‘abc’ cannot be converted, so the default value -1 is returned
- ‘9223372036854775808’ is too large for Int64, causing overflow, so the default -1 is returned
See also:
toInt128
Converts an input value to a value of type Int128. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
Unsupported arguments:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128('0xc0fe');.
Returns:
- 128-bit integer value.
Int128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
If the input value cannot be represented within the bounds of Int128, the result over or under flows. This is not considered an error.
Example:
Query:
Result:
This example demonstrates converting various input types to Int128, including a negative integer, a negative float, and a string representation of a negative integer.
See also:
toInt128OrZero
Converts an input value to a value of type Int128 but returns 0 if the conversion is unsuccessful.
Syntax:
Arguments:
expr(Any): Value to convert. Can be any data type that can be converted toInt128.
Returns:
- An
Int128value if conversion is successful. - 0 if the conversion is unsuccessful.
Example:
Query:
Result:
In this example, ‘128’ is successfully converted to an Int128 value, while ‘big taco’ cannot be converted, so 0 is returned.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
If the input value is outside the range of Int128 (-170141183460469231731687303715884105728 to 170141183460469231731687303715884105727), the result will overflow or underflow, which is not considered an error.
See also:
toInt128OrNull
Converts an input value to a value of type Int128 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
x(Any type convertible toInt128): Value to convert.
Returns:
- A value of type Nullable(
Int128). - NULL if the conversion was not successful.
Example:
Query:
Result:
In this example:
- ‘128’ is successfully converted to Int128
- ‘hello’ cannot be converted, so NULL is returned
- ‘340282366920938463463374607431768211456’ is out of range for Int128, so NULL is returned
This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception.
See Also
toInt128OrDefault
Converts an input value to a value of type Int128 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression /String): Expression returning a number or a string representation of a number.default(Int128, optional): The default value to return if parsing to type Int128 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type
Float32/Float64.
Arguments for which the default value is returned:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128OrDefault('0xc0fe', CAST('-1', 'Int128'));.
If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 128-bit integer value if successful, otherwise returns the default value if passed or 0 if not.
Int128.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘-128’ is successfully converted to Int128.
- ‘abc’ cannot be converted, so the default value -1 is returned.
- ‘1.23e45’ is too large for Int128, causing overflow, so the default value -1 is returned.
See also:
toInt256
Converts an input value to a value of type Int256. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
Unsupported arguments:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256('0xc0fe');.
Returns:
- 256-bit integer value.
Int256.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
If the input value cannot be represented within the bounds of Int256, the result over or under flows. This is not considered an error.
Example:
Query:
Result:
See Also
toInt256OrZero
Converts an input value to a value of type Int256 but returns 0 in case of an error.
Syntax:
Arguments:
x(String): Value to convert.
Returns:
- Converted integer value, or 0 if the conversion failed.
- Type:
Int256.
Example:
Query:
Result:
In this example:
'42'is successfully converted to the Int256 value 42.'extra spicy'cannot be converted to an integer, so 0 is returned.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt256OrNull
Converts an input value to a value of type Int256 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
x(Any): Value to convert. Can be any type that is convertible toInt256.
Returns:
- A value of type
Nullable(Int256). - Returns NULL if the conversion was not successful.
Example:
Query:
Result:
In this example:
- ‘256’ is successfully converted to
Int256. - ‘big taco’ cannot be converted, so NULL is returned.
This function is useful when you need to handle potential conversion errors gracefully, without throwing exceptions. It’s particularly handy when dealing with user input or data from external sources where the format might not always be consistent.
toInt256OrDefault
Converts an input value to a value of type Int256 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression /String): Expression returning a number or a string representation of a number.default(Int256, optional): The default value to return if parsing to type Int256 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toInt256OrDefault('0xc0fe', CAST('-1', 'Int256'));.
If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 256-bit integer value if successful, otherwise returns the default value if passed or 0 if not.
Int256.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example, ‘-256’ is successfully converted to Int256, while ‘guacamole’ cannot be parsed, so the default value of -1 is returned.
See also
toUInt8
Converts an input value to a value of type UInt8. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
Unsupported arguments:
- String representations of
Float32/64 values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8('0xc0fe').
Returns:
- 8-bit unsigned integer value.
UInt8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toUInt8(256) == 0.
Example:
Query:
Result:
In this example:
int_valueconverts the integer 8 toUInt8.float_valueconverts the float 8.8 toUInt8, truncating the decimal part.string_valueconverts the string ‘8’ toUInt8.
See also:
toUInt8OrZero
Converts an input value to a value of type UInt8 but returns 0 in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrZero('0xc0fe');.
Returns:
- 8-bit unsigned integer value if successful, otherwise 0.
UInt8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.
Example:
Query:
Result:
In this example:
- ‘42’ is successfully converted to UInt8.
- ‘abc’ is not a valid number, so 0 is returned.
- ‘256’ overflows UInt8 (max value 255), so 0 is returned.
See also:
toUInt8OrNull
Converts an input value to a value of type UInt8 but returns NULL in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return NULL):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrNull('0xc0fe');.
Returns:
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example:
valid_uint8successfully converts the string ‘8’ to UInt8.invalid_uint8returns NULL because ‘abc’ cannot be converted to UInt8.
See also:
toUInt8OrDefault
Converts an input value to a value of type UInt8 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression/String): Expression returning a number or a string representation of a number.default(UInt8, optional): The default value to return if parsing to typeUInt8is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrDefault('0xc0fe', CAST('0', 'UInt8'));.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 8-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
UInt8.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘8’ is successfully converted to UInt8.
- ‘abc’ cannot be converted, so the default value 0 is returned.
- ‘256’ overflows UInt8, so the default value 0 is returned.
See Also
toUInt16
Converts an input value to a value of type UInt16. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
Unsupported arguments:
- String representations of
Float32/64 values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16('0xc0fe');.
Returns:
- 16-bit unsigned integer value.
UInt16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error. For example: SELECT toUInt16(65536) == 0;.
Example:
Query:
Result:
In this example:
int_valueshows direct conversion from an integer.float_valuedemonstrates truncation of fractional part.string_valueillustrates conversion from a string representation.
See also:
toUInt16OrZero
Converts an input value to a value of type UInt16 but returns 0 if the conversion is unsuccessful.
Syntax:
Arguments:
expr(String, integer, or floating-point number): Value to convert.
Returns:
UInt16value if conversion is successful.- 0 if the conversion is unsuccessful.
Example:
Query:
Result:
In this example, ‘16’ is successfully converted to a UInt16 value, while ‘extra_spicy’ cannot be converted and returns 0.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toUInt16OrNull
Converts an input value to a value of type UInt16 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
expr(String, integer, or floating-point number): Value to convert.
Returns:
- A
UInt16value if conversion is successful. - NULL if the conversion fails.
Example:
Query:
Result:
In this example:
- ‘16’ is successfully converted to
UInt16. - ‘taco’ cannot be converted, so NULL is returned.
This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception.
toUInt16OrDefault
Like toUInt16, this function converts an input value to a value of type UInt16 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(String, numeric): Expression returning a number or a string representation of a number.default(UInt16, optional): The default value to return if parsing to type UInt16 is unsuccessful.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/Float64.
Arguments for which the default value is returned:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16OrDefault('0xc0fe', CAST('0', 'UInt16'));.
If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 16-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
UInt16.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘16’ is successfully converted to UInt16.
- ‘abc’ cannot be converted, so the default value 0 is returned.
- ‘300’ is outside the range of UInt8 (0-255) but within UInt16, so it’s converted successfully.
See Also
toUInt32
Converts an input value to a value of type UInt32. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric or string): Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
Unsupported arguments:
- String representations of
Float32/64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g. SELECT toUInt32(‘0xc0fe’);.
If the input value cannot be represented within the bounds of UInt32, the result over or under flows. This is not considered an error. For example: SELECT toUInt32(4294967296) == 0;
Returns:
- 32-bit unsigned integer value.
UInt32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example, we convert various representations of the number 32 to UInt32, which could represent a count of tacos in an order.
See also:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (
U)(Int8/Int16/Int32/Int128/Int256).
Unsupported arguments (return 0):
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrZero(‘0xc0fe’);.
Returns:
- 32-bit unsigned integer value if successful, otherwise 0. (
UInt32).
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
See Also
toUInt32OrNull
Converts an input value to a value of type UInt32 but returns NULL in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return NULL):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrNull('0xc0fe');.
If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.
Returns:
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example:
- ‘32’ is successfully converted to UInt32.
- ‘3.14159’ returns NULL as it’s a float.
- ‘0xDEADBEEF’ returns NULL as it’s a hexadecimal value.
- An empty string returns NULL.
See also:
toUInt32OrDefault
Converts an input value to a value of type UInt32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression/String): Expression returning a number or a string representation of a number.default(UInt32, optional): The default value to return if parsing to type UInt32 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrDefault('0xc0fe', CAST('0', 'UInt32'));.
If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.
Returns:
UInt32: 32-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘32’ is successfully converted to 32
- ‘abc’ cannot be converted, so the default value 0 is returned
- ‘256.7’ is truncated to 256 (rounding towards zero)
See Also
toUInt64
Converts an input value to a value of type UInt64. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
Unsupported types:
- String representations of
Float32/64 values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64('0xc0fe');.
If the input value cannot be represented within the bounds of UInt64, the result over or under flows. This is not considered an error. For example: SELECT toUInt64(18446744073709551616) == 0;
Returns:
- 64-bit unsigned integer value.
UInt64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
See also
toUInt64OrZero
Converts an input value to a value of type UInt64 but returns 0 in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of
UInt8,UInt16,UInt32,UInt128,UInt256,Int8,Int16,Int32,Int128,Int256.
Unsupported arguments (return 0):
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrZero('0xc0fe');.
Returns:
- 64-bit unsigned integer value if successful, otherwise 0.
UInt64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
This example demonstrates converting a valid string representation of a number to UInt64, and an invalid string returning 0.
See Also
toUInt64OrNull
Converts an input value to a value of type UInt64 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
x(any): Value to convert. Can be any type that is convertible toUInt64.
Returns:
- A
UInt64value if the conversion is successful. - NULL if the conversion fails.
Example:
Query:
Result:
In this example:
- ‘42’ is successfully converted to
UInt64. - ‘abc’ cannot be converted, so NULL is returned.
- ‘9223372036854775808’ is too large for
UInt64, so NULL is returned.
This function is useful when you need to handle potential conversion errors gracefully without causing query failures.
toUInt64OrDefault
Converts an input value to a value of type UInt64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression /String): Expression returning a number or a string representation of a number.default(UInt64, optional): The default value to return if parsing to type UInt64 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrDefault('0xc0fe', CAST('0', 'UInt64'));.
If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 64-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
UInt64.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
This example demonstrates converting a valid string ‘64’ to UInt64, and an invalid string ‘abc’ which returns the default value 0.
See also
toUInt128
Converts an input value to a value of type UInt128. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type (
Float32/Float64).
Unsupported arguments:
- String representations of (
Float32/Float64) values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128('0xc0fe');.
Returns:
- 128-bit unsigned integer value.
UInt128.
If the input value cannot be represented within the bounds of UInt128, the result over or under flows. This is not considered an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example, we convert various values to UInt128:
Result:
toUInt128OrZero
Converts an input value to a value of type UInt128 but returns 0 in case of an error.
Syntax:
Arguments:
expr(String): Expression returning a number or a string representation of a number.
Supported arguments:
- String representations of (
U)Int8/16/32/128/256.
Unsupported arguments (return 0):
- String representations of
Float32/64 values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrZero('0xc0fe');.
If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 128-bit unsigned integer value if successful, otherwise 0.
UInt128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example, ‘128’ is successfully converted to UInt128, while ‘guacamole’ returns 0 as it’s an invalid input.
See also:
toUInt128OrNull
Converts an input value to a value of type UInt128 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
expr(any): Value to convert. Can be any data type that can be converted toUInt128.
Returns:
- A
UInt128value if the conversion was successful. - NULL if the conversion failed.
Example:
Query:
Result:
In this example:
- The first value is successfully converted to the maximum UInt128 value.
- The second value ‘taco’ cannot be converted, so NULL is returned.
This function is useful when you want to handle potential conversion errors gracefully without throwing exceptions. It’s particularly handy when dealing with user input or data from external sources where the format might not always be consistent.
toUInt128OrDefault
Like toUInt128, this function converts an input value to a value of type UInt128 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(String, numeric): Expression returning a number or a string representation of a number.default(UInt128, optional): The default value to return if parsing to typeUInt128is unsuccessful.
Supported arguments:
- (
U)Int8/16/32/64/128/256 Float32/64- String representations of (
U)Int8/16/32/128/256
Arguments for which the default value is returned:
- String representations of
Float32/64values, including NaN and Inf - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrDefault('0xc0fe', CAST('0', 'UInt128'));
If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.
Returns:
- 128-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. (
UInt128).
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘128’ is successfully converted to
UInt128 - ‘abc’ cannot be converted, so the default value 0 is returned
- Both results are of type
UInt128
See Also
toUInt256
Converts an input value to a value of type UInt256. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
Unsupported arguments:
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256('0xc0fe');.
Returns:
- 256-bit unsigned integer value.
UInt256.
If the input value cannot be represented within the bounds of UInt256, the result over or under flows. This is not considered an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
This example demonstrates converting various input types (integer, float, and string) to UInt256.
See also:
toUInt256OrZero
Converts an input value to a value of type UInt256 but returns 0 in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrZero('0xc0fe');.
Returns:
- 256-bit unsigned integer value if successful, otherwise 0.
UInt256.
If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
In this example:
- ‘256’ is successfully converted to UInt256.
- ‘abc’ is an invalid input, so it returns 0.
- ‘18446744073709551616’ (2^64) demonstrates overflow behavior, returning the value as-is since it’s within UInt256 range.
See Also
toUInt256OrNull
Converts an input value to a value of type UInt256 but returns NULL in case of an error.
Syntax:
Arguments:
x(String): A String representation of a number.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return NULL):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrNull('0xc0fe');.
Returns:
If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
See Also
toUInt256OrDefault
Converts an input value to a value of type UInt256 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression/String): Expression returning a number or a string representation of a number.default(UInt256, optional): The default value to return if parsing to type UInt256 is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrDefault('0xc0fe', CAST('0', 'UInt256'));.
If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.
Returns:
UInt256: 256-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
In this example:
- ‘256’ is successfully converted to UInt256.
- ‘abc’ cannot be converted, so the default value 0 is returned.
- ‘-256’ is outside the range of UInt256, so the default value 0 is returned.
See Also
toFloat32
Converts an input value to a value of type Float32. Throws an exception in case of an error.
Syntax:
Arguments:
expr— Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type
UInt8,UInt16,UInt32,UInt64,UInt128,UInt256,Int8,Int16,Int32,Int64,Int128,Int256 - Values of type
Float32,Float64 - String representations of
Float32,Float64, including NaN and Inf (case-insensitive)
Unsupported arguments:
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32('0xc0fe');
Returns:
- 32-bit floating point value. Type:
Float32.
Example:
Query:
Result:
In this example:
aconverts a numeric literal toFloat32bconverts a string representation toFloat32cdemonstrates handling of special values like NaN
See Also
toFloat32OrZerotoFloat32OrNulltoFloat32OrDefault
toFloat32OrZero
Converts an input value to a value of type Float32 but returns 0 if an error occurs during the conversion.
Syntax:
Arguments:
x(String, integer, or floating-point number): Value to convert.
Returns:
- A
Float32value if conversion is successful. - 0 if the conversion fails.
Example:
Query:
Result:
In this example:
pisuccessfully converts the string ‘3.14159’ to aFloat32value.failed_conversionreturns 0 because ‘extra_spicy’ cannot be converted to aFloat32.
This function is particularly useful when dealing with potentially dirty data, as it allows queries to continue without throwing exceptions for invalid conversions.
toFloat32OrNull
Converts an input value to a value of type Float32 but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
x(String, integer, or floating-point number): Value to be converted.
Returns:
- A
Float32value if conversion is successful. - NULL if the conversion fails.
Example:
Query:
Result:
In this example:
- ‘42.7’ is successfully converted to a
Float32value. - ‘al pastor’ cannot be converted, so NULL is returned.
This function is useful when you want to handle potential conversion errors gracefully without causing query failures.
Syntax:
Arguments:
expr(Any data type that can be converted toFloat32): Value to convert.default(Float32, optional): Value returned if the conversion fails. If not specified, 0 is returned.
Returns:
- A
Float32value if the conversion succeeds. - The
defaultvalue (or 0 if not specified) if the conversion fails.
Example:
Result:
In this example:
- ‘3.14159’ is successfully converted to a
Float32. - ‘not_a_number’ cannot be converted, so the default value 99.9 is returned.
This function is useful when you need to handle potential conversion errors gracefully, especially when dealing with user input or data from external sources.
toFloat64
Converts an input value to a value of type Float64. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.
Supported arguments:
- Values or string representations of type
UInt8,UInt16,UInt32,UInt64,UInt128,UInt256,Int8,Int16,Int32,Int64,Int128,Int256. - Values or string representations of type
Float32,Float64. - String representations of
Float32,Float64, including NaN and Inf (case-insensitive).
Unsupported arguments:
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64('0xc0fe');.
Returns:
- 64-bit floating point value.
Float64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Result:
See also:
toFloat64OrZero
Converts an input value to a value of type Float64 but returns 0 if an error occurs during the conversion.
Syntax:
Arguments:
x(String, number, or date/time value): Value to convert.
Returns:
Float64value if conversion is successful.- 0 if conversion fails.
Example:
Query:
Result:
This example demonstrates:
- Successful conversion of a valid float string
- Zero returned for an invalid input
- Special handling of ‘inf’ as a valid float value
The function uses rounding towards zero, truncating fractional digits for whole numbers.
See also:
toFloat64— Throws an exception on conversion error.toFloat64OrNull— ReturnsNULLon conversion error.
toFloat64OrNull
Converts an input value to a value of type Float64, but returns NULL if the conversion is unsuccessful.
Syntax:
Arguments:
x(String, number, or date/time value): Value to convert.
Returns:
- A
Float64value if the conversion is successful. NULLif the conversion fails.
Examples
Result:
This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception for invalid input.
See Also
toFloat64toFloat64OrZerotoFloat64OrDefault
toFloat64OrDefault
Converts an input value to a value of type Float64, but returns a default value if the conversion fails.
Syntax:
Arguments:
expr(Any data type that can be converted toFloat64): Value to convert.default(Float64, optional): Value returned if the conversion fails. If not specified, 0 is returned.
Returns:
- A
Float64value if the conversion succeeds. - The
defaultvalue (or 0 if not specified) if the conversion fails.
Examples
Result:
In this example:
- ‘pi’ is successfully converted to a
Float64. - ‘invalid_with_default’ fails to convert and returns the specified default of 99.9.
- ‘invalid_without_default’ fails to convert and returns 0 since no default was specified.
This function is useful when you want to handle potential conversion errors gracefully without causing query failures.
toDate
Converts an input value to the Date data type.
Syntax:
Arguments:
expr(Any): Value to convert. Can be any data type that can be converted to a date.
Returns:
- A value of
Datetype.
Examples
Converting from DateTime:
Result:
Converting from a string:
Result:
Converting from a Unix timestamp:
Result:
- When converting from DateTime or DateTime64, it truncates the time component.
- When converting from a number, if it’s greater than or equal to 65536, it’s interpreted as a Unix timestamp (seconds since epoch).
- The function supports various string formats, including ISO 8601.
See also: toDateTime, toDate32
toDateOrZero
Converts an input value to a value of type Date. This function behaves similarly to toDate, but returns the lower boundary of the Date type (1970-01-01) if an invalid argument is received.
Syntax:
Arguments:
expr— Value to be converted. Can be any data type that can be converted to [Date].
Returns:
- A value of type [
Date]. - Returns 1970-01-01 if the argument cannot be parsed as a valid date.
Example:
Query:
Result:
In this example:
valid_dateshows a correctly parsed dateinvalid_datereturns the minimum Date value because the input is not a valid date stringout_of_range_datealso returns the minimum Date value because the input is out of the valid range for dates
This function is particularly useful when you want to handle potentially invalid date inputs without causing query errors, defaulting to a known value (1970-01-01) for invalid inputs.
toDateOrNull
Converts the input value to the Date data type. This function is similar to toDate, but returns NULL if the conversion is unsuccessful.
Syntax:
Arguments:
expr(Any): Value to be converted. Can be any data type that can be converted to a date.
Returns:
- A value in the
Datedata type if conversion is successful. NULLif the conversion fails.
Example:
Query:
Result:
In this example:
- ‘2023-09-15’ is successfully converted to a Date.
- ‘2023-09-31’ is an invalid date (September has 30 days), so it returns NULL.
- ‘not a date’ cannot be interpreted as a date, so it returns NULL.
This function is particularly useful when dealing with potentially invalid date inputs, as it allows for graceful handling of conversion errors without throwing exceptions.
toDateOrDefault
Converts an input value to a value of type Date. If the conversion is unsuccessful, it returns a default value.
Syntax:
Arguments:
expr(Expression): Expression returning a date or a string representation of a date.default_value(Date, optional): The default value to return if parsing to Date is unsuccessful.
Returns:
- A
Datevalue if the conversion is successful. - The specified default value if provided, or 1970-01-01 if not, when the conversion fails.
Example:
Query:
Result:
In this example:
valid_datesuccessfully converts the string to a Date.invalid_datefails to convert and returns the default date (1970-01-01).invalid_date_with_defaultfails to convert but returns the specified default date.
This function is useful when you need to handle potentially invalid date inputs without causing query errors, providing a fallback value for further processing.
toDateTime
Converts an input value to DateTime.
Syntax:
Arguments:
expr(String,Int,Date,DateTime): The value to convert.time_zone(String, optional): The time zone to use for the conversion.
Returns:
- A date with time.
DateTime.
- If
expris a number, it’s interpreted as a Unix timestamp (number of seconds since the beginning of the Unix Epoch). - If
expris a string, it may be interpreted as a Unix timestamp or as a string representation of date / date with time. - Parsing of short numbers’ string representations (up to 4 digits) is explicitly disabled due to ambiguity.
Example:
Result:
In this example, we convert a string representation of a date and time to a DateTime value. We also convert a Unix timestamp to a DateTime value, specifying the UTC time zone.
toDateTimeOrZero
Converts an input value to a value of type DateTime but returns the lower boundary of DateTime (1970-01-01 00:00:00) if an invalid argument is received.
Syntax:
Arguments:
expr(String): Value to be converted.time_zone(String, optional): Timezone for the returned value.
Returns:
- DateTime value if conversion is successful, otherwise 1970-01-01 00:00:00.
- Type:
DateTime.
Example:
Query:
Result:
In this example:
valid_datetimeshows a correctly parsed DateTime.invalid_datetimeshows the result of an invalid input, which is the lower boundary of DateTime.
This function is useful when you want to ensure that invalid date strings don’t cause errors in your queries, instead defaulting to a known value.
toDateTimeOrNull
Converts an input value to a value of type DateTime but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
expr(any): Value to be converted. Can be any data type that can be converted to DateTime.timezone(String, optional): Timezone for the returned value. If not specified, the server’s timezone is used.
Returns:
- A value of
DateTimetype if the conversion is successful. - NULL if the conversion fails.
Example:
Result:
In this example:
- ‘2023-09-15 14:30:00’ is successfully converted to a DateTime value.
- ‘invalid_date’ cannot be converted, so NULL is returned.
This function is useful when you need to handle potentially invalid date/time inputs without causing query errors.
toDateTimeOrDefault
Converts an input value to a value of type DateTime but returns the default value in case of an error. If no default value is passed then the lower boundary of DateTime is returned in case of an error.
Syntax:
Arguments:
expr(Expression /String): Expression returning a number or a string representation of a number.time_zone(String, optional): Timezone name for the returned value.default_value(DateTime, optional): The default value to return if parsing to type DateTime is unsuccessful.
Returns:
- A date and time value if successful, otherwise returns the default value if passed or the lower boundary of DateTime if not.
DateTime.
Example:
Query:
Result:
In this example:
valid_datetimesuccessfully converts the string to a DateTime value.invalid_datetimereturns the specified default value for an invalid input.
This function is useful when you need to handle potentially invalid datetime inputs without causing query errors, providing a fallback value for further processing.
toDate32
Converts an input value to a value of type Date32. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument has Date type, its borders are taken into account.
Syntax:
Arguments:
expr(String,UInt32, orDate): The value to convert.
Returns:
- A calendar date. Type:
Date32.
Example:
Query:
Result:
Query:
Result:
Query:
Result:
toDate32OrZero
Converts an input value to a value of type Date32 but returns the minimum value of Date32 (1900-01-01) if an invalid argument is received.
Syntax:
Arguments:
expr(Any): Value to be converted. Can be any data type that can be converted to Date32.
Returns:
- A [
Date32] value if the conversion is successful. - 1900-01-01 if the conversion fails.
Example:
Query:
Result:
In this example:
- ‘2023-05-15’ is successfully converted to a Date32 value.
- ‘not a date’ is an invalid input, so it returns the minimum Date32 value.
- ‘1899-12-31’ is out of the valid range for Date32, so it also returns the minimum value.
This function is useful when you want to handle invalid date inputs gracefully in your taco sales analysis, replacing them with a known default value instead of throwing an error.
toDate32OrNull
Converts an input value to a value of type Date32 but returns NULL if an invalid argument is received.
Syntax:
Arguments:
expr(String,DateTime,Date,Date32,Int32,UInt32): Expression returning a number or a string representation of a date. Expression.
Returns:
- A Date32 value if the conversion was successful, otherwise NULL.
- Returns
Date32/Nullable(Date32).
Example:
Query:
Result:
In this example:
- ‘1955-01-01’ is successfully converted to a Date32 value.
- ‘not a date’ cannot be converted, so NULL is returned.
This function is useful when you need to handle potentially invalid date inputs without causing query errors.
toDate32OrDefault
Converts an input value to a value of type Date32 but returns the default value in case of an error. If no default value is passed then the minimum value of Date32 (1900-01-01) is returned in case of an error.
Syntax:
Arguments:
expr(Expression/String): Expression returning a date or a string representation of a date.default(Date32, optional): The default value to return if parsing to type Date32 is unsuccessful.
Returns:
- A
Date32value if successful, otherwise returns the default value if passed or 1900-01-01 if not.
Example:
Query:
Result:
In this example:
valid_datesuccessfully converts ‘1930-01-01’ to a Date32 value.invalid_datefails to parse ‘not_a_date’, so it returns the specified default value ‘2020-01-01’.
If the input value is outside the range of Date32 (1900-01-01 to 2299-12-31), the function returns the lower or upper bound of the Date32 range. This is not considered an error.
toDateTime64
Converts an input value to a value of type DateTime64.
Syntax:
Arguments:
expr(String,UInt32,Float, orDateTime): Expression returning a number or a string representation of a number.scale(numeric): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.
Returns:
- A calendar date and time of day, with sub-second precision.
DateTime64.
Example:
Query:
Result:
Query:
Result:
Query:
Result:
Without the decimal point, the value is still treated as Unix Timestamp in seconds:
Result:
This function is particularly useful when you need to work with timestamps that require sub-second precision, such as in financial applications or high-frequency event logging.
toDateTime64OrZero
Converts an input value to a value of type DateTime64 but returns the minimum value of DateTime64 if an invalid argument is received.
Syntax:
Arguments:
expr(String,UInt32,Float,DateTime): Expression returning a number or a string representation of a number.scale(UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.
Returns:
- A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64: 1970-01-01 00:00:00.000.
DateTime64.
Example:
Query:
Result:
This example demonstrates how the function handles an invalid input by returning the minimum DateTime64 value.
See Also
toDateTime64OrNull
Converts an input value to a value of type DateTime64 but returns NULL if an invalid argument is received.
Syntax:
Arguments:
expr(String,Number): Expression returning a number or a string representation of a number.scale(UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.
Returns:
- A calendar date and time of day, with sub-second precision, otherwise NULL.
DateTime64/Nullable(DateTime64).
Example:
Query:
Result:
This example demonstrates converting a valid date string to DateTime64 and returning NULL for an invalid date string.
See Also
toDateTime64OrDefault
Converts an input value to a value of type DateTime64, but returns either the default value of DateTime64 or the provided default if an invalid argument is received.
Syntax:
Arguments:
expr(String,Expression): Expression returning a number or a string representation of a number.scale(UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.default(DateTime64, optional): Default value to return if an invalid argument is received.
Returns:
- A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64 or the default value if provided.
DateTime64.
Example:
Query:
Result:
This example demonstrates how toDateTime64OrDefault handles invalid input. When given an invalid date string, it returns the minimum DateTime64 value if no default is specified, or the provided default value if one is given.
See Also
toDecimal32
Converts an input value to a value of type Decimal(9, S) with scale of S. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.
Returns:
- Value of type
Decimal(9, S). Decimal32(S).
Example:
Result:
- Supported arguments: Values or string representations of type (
U)Int8/16/32/64/128/256 andFloat32/64. - Unsupported arguments: String representations of
Float32/64 values NaN and Inf (case-insensitive), and binary/hexadecimal values (e.g., ‘0xc0fe’). - An overflow can occur if the value of
exprexceeds the bounds of Decimal32: (-1 * 10^(9 - S), 1 * 10^(9 - S)). - Excessive digits in a fraction are discarded (not rounded).
- Excessive digits in the integer part will lead to an exception.
See also: toDecimal32OrZero, toDecimal32OrNull, toDecimal32OrDefault.
toDecimal32OrZero
Converts an input value to a value of type Decimal(9, S) but returns 0 if an error occurs during the conversion.
Syntax:
Arguments:
expr(String): Value to convert to Decimal(9, S).S(UInt8): Number of decimal places, from 0 to 9.
Returns:
- A value of type Decimal(9, S) if the conversion is successful.
- 0 with S decimal places if the conversion fails.
Type: Decimal(9, S)
Example:
Query:
Result:
In this example:
- The string “-1.111” is successfully converted to a Decimal(9, 5).
- The string “not a number” cannot be converted, so it returns 0.00000.
See Also
toDecimal32OrNull
Converts an input value to a value of type Nullable(Decimal(9, S)) but returns NULL if an error occurs during the conversion.
Syntax:
Arguments:
expr(String): A string representation of a number.S(UInt8): Scale, the number of decimal places. Range: 0 to 9.
Returns:
- A value of type Nullable(Decimal(9, S)) if the conversion is successful.
- NULL if the conversion fails.
Examples
Query:
Result:
- The function supports string representations of (U)Int8/16/32/128/256 and Float32/64 numbers.
- It does not support string representations of ‘NaN’, ‘Inf’, ‘-Inf’, or hexadecimal numbers.
- If the input value exceeds the range of Decimal32 (-1 * 10^(9 - S) to 1 * 10^(9 - S)), the result will overflow or underflow.
- Excess fractional digits are discarded (not rounded).
See Also
toDecimal32OrDefault
Like toDecimal32, this function converts an input value to a value of type Decimal(9, S) but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(String, numeric): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.default(Decimal32(S), optional): The default value to return if parsing to type Decimal32(S) is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32OrDefault('0xc0fe', CAST('-1', 'Decimal32(0)'));.
An overflow can occur if the value of expr exceeds the bounds of Decimal32: ( -1 * 10^(9 - S), 1 * 10^(9 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an error.
Returns:
- Value of type
Decimal(9, S)if successful, otherwise returns the default value if passed or 0 if not.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
See also
toDecimal64
Converts an input value to a value of type Decimal(18, S) with scale of S. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number. Expression.S(UInt8): Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values or string representations of type
Float32/64.
Unsupported arguments:
- Values or string representations of
Float32/64 values NaN and Inf (case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64('0xc0fe', 1);.
An overflow can occur if the value of expr exceeds the bounds of Decimal64: ( -1 * 10^(18 - S), 1 * 10^(18 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an exception.
Returns:
- Value of type Decimal(18, S). Decimal64(S).
Example:
Result:
See also:
toDecimal64OrZero
Converts an input value to a value of type Decimal(18, S) but returns 0 if an error occurs during the conversion.
Syntax:
Arguments:
expr(String): Value to convert to Decimal(18, S).S(UInt8): Number of decimal places, from 0 to 18.
Returns:
- A value of type Decimal(18, S) if the conversion is successful.
- 0 with S decimal places if the conversion fails.
Type: Decimal(18, S)
Example:
Query:
Result:
- The function handles string representations of numbers, including those with decimal points.
- It returns 0 for inputs that cannot be parsed as numbers, such as ‘not a number’ in the example.
- Be cautious with potential loss of precision when converting between different decimal scales.
See also:
toDecimal64OrNull
Converts an input value to a value of type Nullable(Decimal(18, S)) but returns NULL if the conversion fails.
Syntax:
Arguments:
expr(String): A string representation of a number.S(UInt8): Scale, the number of decimal places. Range: [0, 18].
Returns:
- A value of type Nullable(Decimal(18, S)) if successful, otherwise NULL.
Examples
Query:
Result:
- If the input value cannot be represented within the bounds of Decimal64 (-1 * 10^(18 - S), 1 * 10^(18 - S)), the function returns NULL.
- Excessive digits in the fractional part are discarded (not rounded).
- String representations of Float32/64 values NaN and Inf are not supported and will result in NULL.
- String representations of binary and hexadecimal values (e.g., ‘0xc0fe’) are not supported and will result in NULL.
See Also
toDecimal64OrDefault
Converts an input value to a value of type Decimal(18, S) but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(Expression/String): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.default(Decimal64(S), optional): The default value to return if parsing to type Decimal64(S) is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64OrDefault('0xc0fe', 1);.
An overflow can occur if the value of expr exceeds the bounds of Decimal64: ( -1 * 10^(18 - S), 1 * 10^(18 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an error.
Returns:
- Value of type Decimal(18, S) if successful, otherwise returns the default value if passed or 0 if not.
Decimal64(S).
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example:
Query:
Result:
This example demonstrates converting a string representation of a small decimal number to Decimal64 with 18 decimal places, and handling an ‘Inf’ input by returning a default value of -1.
toDecimal128
Converts an input value to a value of type Decimal(38, S) with scale of S. Throws an exception in case of an error.
Syntax:
Arguments:
expr(numeric orString): Expression returning a number or a string representation of a number. Expression.S(UInt8): Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values or string representations of type
Float32/64.
Unsupported arguments:
- Values or string representations of
Float32/64 values NaN and Inf (case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128('0xc0fe', 1);.
An overflow can occur if the value of expr exceeds the bounds of Decimal128: ( -1 * 10^(38 - S), 1 * 10^(38 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an exception.
Returns:
- Value of type Decimal(38, S). Decimal128(S).
Example:
Query:
Result:
See also
toDecimal128OrZero
Converts an input value to a value of type Decimal(38, S) but returns 0 if an error occurs during the conversion.
Syntax:
Arguments:
expr(String): Value to convert to Decimal(38, S).S(UInt8): Scale, the number of decimal places. Range: [0, 38].
Returns:
- A value of type
Decimal(38, S)if the conversion is successful. - 0 with S decimal places if the conversion fails.
Type: Decimal(38, S)
Examples
Query:
Result:
- The function supports string representations of (U)Int8/16/32/64/128/256 and Float32/64.
- It does not support string representations of NaN, Inf, or binary/hexadecimal values (e.g., ‘0xc0fe’).
- If the input value exceeds Decimal128 bounds (-1 * 10^(38 - S), 1 * 10^(38 - S)), overflow occurs without raising an error.
- Excess fractional digits are discarded without rounding.
See Also
toDecimal128OrNull
Converts an input value to a value of type Nullable(Decimal(38, S)) but returns NULL if the conversion fails.
Syntax:
Arguments:
expr(String): A string representation of a number.S(UInt8): Scale, the number of decimal places. Range: 0 to 38.
Supported arguments:
- String representations of (U)Int8/16/32/64/128/256.
- String representations of Float32/64.
Unsupported arguments (return NULL):
- String representations of NaN and Inf.
- Binary and hexadecimal representations (e.g., ‘0xc0fe’).
Returns:
- A value of type Nullable(Decimal(38, S)) if successful, otherwise NULL.
Decimal128(S).
- If the input value exceeds the bounds of Decimal128 (-1 * 10^(38 - S), 1 * 10^(38 - S)), overflow occurs. This is not considered an error.
- Excess fractional digits are truncated (not rounded).
- Excess integer digits will lead to an error.
Example:
Query:
Result:
In this example, we convert a fraction to a Decimal128 and attempt to convert ‘Inf’, which returns NULL.
This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception.
toDecimal128OrDefault
Like toDecimal128, this function converts an input value to a value of type Decimal(38, S) but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.
Syntax:
Arguments:
expr(String): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.default(Decimal128(S), optional): The default value to return if parsing to type Decimal128(S) is unsuccessful.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128OrDefault('0xc0fe', 1);.
An overflow can occur if the value of expr exceeds the bounds of Decimal128: ( -1 * 10^(38 - S), 1 * 10^(38 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an error.
Returns:
- Value of type
Decimal(38, S)if successful, otherwise returns the default value if passed or 0 if not.
Examples
Query:
Result:
See Also
toDecimal256
Converts an input value to a value of type Decimal(76, S) with scale of S. Throws an exception in case of an error.
Syntax:
Arguments:
expr(Expression): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.
Supported arguments:
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values or string representations of type
Float32/64.
Unsupported arguments:
- Values or string representations of
Float32/64 values NaN and Inf (case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256('0xc0fe', 1);.
Returns:
- Value of type
Decimal256(S).
An overflow can occur if the value of expr exceeds the bounds of Decimal256: ( -1 * 10^(76 - S), 1 * 10^(76 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an exception.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
See also
toDecimal256OrZero
Converts an input value to a value of type Decimal(76, S) but returns 0 if an error occurs during the conversion.
Syntax:
Arguments:
expr(String): Value to convert to Decimal256.S(UInt8): Scale, the number of decimal places. Range: [0 : 76].
Returns:
- Value of type Decimal(76, S) if successful, otherwise 0 with S decimal places.
Decimal256(S).
Example:
Query:
Result:
In this example:
- ‘123.456’ is successfully converted to a Decimal256 with 3 decimal places.
- ‘not a number’ cannot be converted, so it returns 0.000.
- If the input value exceeds the range of Decimal256 (±1 * 10^(76 - S)), the result will overflow or underflow.
- Excess fractional digits are truncated, not rounded.
See also:
toDecimal256OrNull
Converts an input value to a value of type Nullable(Decimal(76, S)) but returns NULL in case of an error.
Syntax:
Arguments:
expr(String): A String representation of a number.S(UInt8): Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return NULL):
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256OrNull('0xc0fe', 1);.
Returns:
- Value of type Nullable(Decimal(76, S)) if successful, otherwise NULL. Decimal256(S) / NULL.
An overflow can occur if the value of expr exceeds the bounds of Decimal256: ( -1 * 10^(76 - S), 1 * 10^(76 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an error.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example:
Query:
Result:
See Also
Syntax:
Arguments:
expr(String, numeric): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.default(Decimal256(S), optional): The default value to return if parsing to type Decimal256(S) is unsuccessful.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256OrDefault('0xc0fe', 1);.
An overflow can occur if the value of expr exceeds the bounds of Decimal256: ( -1 * 10^(76 - S), 1 * 10^(76 - S) ). Excessive digits in a fraction are discarded (not rounded). Excessive digits in the integer part will lead to an error.
Returns:
- Value of type
Decimal256(S)if successful, otherwise returns the default value if passed or 0 if not.
Example:
Query:
Result:
In this example, we’re converting the price of a taco from a string to a Decimal256 value:
Result:
In this example, ‘3.99’ is successfully converted to a Decimal256(2) value, while ‘spicy’ cannot be converted, so the default value of 2.50 is returned.
toString
Converts an input value to a String.
Syntax:
Arguments:
x(any data type): Value to convert.
Returns:
- A string representation of the input value.
Examples
Converting various data types to strings:
Result:
Converting DateTime with timezone:
Result:
- When converting dates and times, the function uses the same format as the TabSeparated output format.
- For DateTime values, an optional second argument can specify the timezone for formatting.
- When converting numbers to strings, the function uses the minimum number of characters required to represent the number.
See also: toFixedString, CAST
toFixedString
Converts a String type argument to a FixedString(N) type (a string of fixed length N).
Syntax:
Arguments:
s(String): The input string to convert.N(UInt8): The desired fixed length of the resulting string.
Returns:
- A fixed-length string of type FixedString(N).
Description
If the input string has fewer bytes than N, it is padded with null bytes to the right. If the string has more bytes than N, an exception is thrown.
Example:
Query:
Result:
In this example, ‘Taco Tuesday’ is converted to a FixedString of length 15, with null bytes padding the end.
Be cautious when using this function, as it can lead to data truncation if the input string is longer than the specified length N.
toStringCutToZero
Accepts a String or FixedString argument and returns the String with the content truncated at the first zero byte found.
Syntax:
Arguments:
s(StringorFixedString): The input string.
Returns:
- A string truncated at the first zero byte.
String
Example:
Query:
Result:
This example demonstrates how toStringCutToZero truncates the string ‘salsa verde\0guacamole’ at the null byte, returning only ‘salsa verde’.
toDecimalString
Converts a numeric value to a String with the specified number of fractional digits.
Syntax:
Arguments:
number(numeric): Value to be represented as String. Can beInt,UInt,Float, orDecimal.scale(UInt8): Number of fractional digits.- Maximum scale for
DecimalandInt/UInttypes is 77. - Maximum scale for
Floattypes is 60.
- Maximum scale for
Returns:
- A
Stringrepresentation of the input value with the specified number of fractional digits.
- The number is rounded up or down according to common arithmetic rules if the requested scale is smaller than the original number’s scale.
Example:
Result:
In this example, we convert a Float64 value representing a taco price to a string with 5 decimal places.
reinterpretAsUInt8
Performs byte reinterpretation by treating the input value as a value of type UInt8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,Int8,UInt16,Int16,UInt32,Int32,UInt64,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret asUInt8.
Returns:
- Reinterpreted value
xasUInt8. Type:UInt8.
Example:
Query:
Result:
In this example:
toInt8(257)results in 1 due to overflowreinterpretAsUInt8(x)reinterprets the Int8 value 1 as UInt8, which is also 1
Note that this function performs a low-level reinterpretation and should be used with caution, especially when dealing with negative numbers or values outside the UInt8 range.
reinterpretAsUInt16
Performs byte reinterpretation by treating the input value as a value of type UInt16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt16.
Returns:
- Reinterpreted value
xas UInt16. Type:UInt16.
Example:
Result:
In this example, we’re reinterpreting a UInt8 value as UInt16. The original value 257 is truncated to 1 when converted to UInt8, and then reinterpreted as 1 in UInt16 format.
reinterpretAsUInt32
Performs byte reinterpretation by treating the input value as a value of type UInt32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt32.
Returns:
- Reinterpreted value
xasUInt32. Type:UInt32.
Example:
Query:
Result:
In this example, we start with a UInt16 value of 257, then reinterpret it as a UInt32. The value remains the same, but the type changes to UInt32.
reinterpretAsUInt64
Performs byte reinterpretation by treating the input value as a value of type UInt64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret asUInt64.
Returns:
- Reinterpreted value
xasUInt64. Type:UInt64.
Example:
Result:
In this example, we’re reinterpreting a UInt32 value (which could represent a taco order number) as a UInt64:
Result:
This example shows how the taco order number is reinterpreted from UInt32 to UInt64 without changing its value, but changing its underlying representation.
reinterpretAsUInt128
Performs byte reinterpretation by treating the input value as a value of type UInt128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt128.
Returns:
- Reinterpreted value
xasUInt128. Type:UInt128.
Example:
Result:
In this example, we start with a UInt64 value of 257, then reinterpret it as a UInt128. The numeric value remains the same, but the data type changes to UInt128.
reinterpretAsUInt256
Performs byte reinterpretation by treating the input value as a value of type UInt256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,UInt128,Int8,Int16,Int32,Int64,Int128,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt256.
Returns:
- Reinterpreted value
xasUInt256. Type:UInt256.
Example:
Result:
This example demonstrates converting a UInt128 value to UInt256 using byte reinterpretation.
reinterpretAsInt8
Performs byte reinterpretation by treating the input value as a value of type Int8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to reinterpret as Int8.
Returns:
- Reinterpreted value
xas Int8. Type:Int8.
Example:
Result:
In this example, we start with a UInt8 value of 1 (257 truncated to fit in UInt8) and reinterpret it as Int8. The binary representation remains the same, so the result is still 1.
This function is useful for low-level data manipulation but should be used with caution as it can lead to unexpected results if not used correctly.
reinterpretAsInt16
Performs byte reinterpretation by treating the input value as a value of type Int16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int16.
Returns:
- Reinterpreted value
xas Int16. Type:Int16.
Example:
Query:
Result:
In this example:
- We start with 257, which becomes 1 when converted to Int8 due to overflow.
- This Int8 value of 1 is then reinterpreted as an Int16, preserving the bit pattern but changing the type.
Note that this function performs a low-level reinterpretation and should be used with caution, as it can lead to unexpected results if not used correctly.
reinterpretAsInt32
Performs byte reinterpretation by treating the input value as a value of type Int32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int32.
Returns:
- Reinterpreted value
xas Int32. Type:Int32.
Example:
Result:
In this example, we start with a UInt16 value of 257, then reinterpret it as an Int32. The numeric value remains the same, but the type changes to Int32.
reinterpretAsInt64
Performs byte reinterpretation by treating the input value as a value of type Int64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(Int*,UInt*,Float*,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret asInt64.
Returns:
- Reinterpreted value
xasInt64. Type:Int64.
Example:
Query:
Result:
In this example, we start with an Int32 value of 257, then reinterpret it as an Int64. The numeric value remains the same, but the type changes to Int64.
This function performs a low-level reinterpretation of the bytes representing the input value. It does not perform type conversion in the conventional sense. Use with caution, as the results may be unexpected if you’re not familiar with the binary representation of different data types.
reinterpretAsInt128
Performs byte reinterpretation by treating the input value as a value of type Int128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int128.
Returns:
- Reinterpreted value
xas Int128. [Int128]
Example:
Result:
In this example, we start with an Int64 value of 257, then reinterpret it as an Int128. The numeric value remains the same, but the data type changes to Int128.
reinterpretAsInt256
Performs byte reinterpretation by treating the input value as a value of type Int256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int256.
Returns:
- Reinterpreted value
xas Int256. [Int256]
Example:
Result:
In this example, we start with an Int128 value of 257, then reinterpret it as an Int256. The numeric value remains the same, but the data type changes to Int256.
This function performs a low-level reinterpretation of the bytes representing the input value. It does not perform type conversion in the conventional sense. Use with caution, especially when dealing with floating-point numbers or other non-integer types.
reinterpretAsFloat32
Performs byte reinterpretation by treating the input value as a value of type Float32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to reinterpret as Float32.
Returns:
- Reinterpreted value
xasFloat32.
Example:
Result:
In this example, we first convert 0.2 to Float32, then reinterpret it as UInt32, and finally reinterpret it back to Float32, demonstrating the byte-level manipulation performed by the function.
This function is useful for low-level data manipulation but should be used with caution as it can produce unexpected results if not used correctly.
reinterpretAsFloat64
Performs byte reinterpretation by treating the input value as a value of type Float64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Date,DateTime,UUID,String,FixedString): Value to reinterpret as Float64.
Returns:
- Reinterpreted value
xasFloat64.
Example:
Result:
This example demonstrates how to reinterpret a Float64 value as UInt64 and then back to Float64, preserving the original value.
Be cautious when using this function, as it can lead to unexpected results if misused. It’s primarily useful for low-level data manipulation and should be used with care.
reinterpretAsDate
Accepts a string, fixed string or numeric value and interprets the bytes as a number in host order (little endian). It returns a date from the interpreted number as the number of days since the beginning of the Unix Epoch.
Syntax:
Arguments:
x(numeric,String,FixedString,Date,DateTime,UUID): Number of days since the beginning of the Unix Epoch.
Returns:
- Date. (
Date).
Implementation details
If the provided string isn’t long enough, the function works as if the string is padded with the necessary number of null bytes. If the string is longer than needed, the extra bytes are ignored.
Example:
Query:
Result:
In this example, we’re interpreting the number of days since the Unix Epoch as dates for a taco shop’s opening:
Result:
Here, 18615 days since the Unix Epoch (2020-12-31) represents the original taco shop’s opening date. The string ‘Taco’ is interpreted as bytes, resulting in 2003-05-07 as the franchise opening date.
reinterpretAsDateTime
Performs byte reinterpretation by treating the input value as a value of type DateTime. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to reinterpret as DateTime.
Returns:
- Reinterpreted value as
DateTime.
Example:
Query:
Result:
This example demonstrates how a UInt32 value is reinterpreted as a DateTime, resulting in a specific date and time.
Example:
Query:
Result:
In this whimsical example, we’ve reinterpreted a UInt32 value as a DateTime, perhaps representing the start of a grand taco festival. The result shows both the DateTime value and its string representation.
The reinterpretation of bytes can lead to unexpected results if not used carefully. Always ensure you understand the binary representation of your input data when using this function.
reinterpretAsString
Performs byte reinterpretation by treating the input value as a string. Unlike CAST, this function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output may be meaningless.
Syntax:
Arguments:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to reinterpret as a string.
Returns:
- A string containing bytes representing
x. Type:String.
Example:
Result:
In this example:
- The
DateTimevalue is converted to a 4-byte string representation. - The
Datevalue is converted to a 2-byte string representation.
Note that the output may contain non-printable characters, which are represented here as \0.
The function drops null bytes from the end. For example, a UInt32 type value of 255 will result in a string that is one byte long.
reinterpretAsFixedString
Performs byte reinterpretation by treating the input value as a value of type FixedString. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax:
Arguments:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to reinterpret as FixedString.
Returns:
- Reinterpreted value
xas FixedString.
Example:
Result:
In this example:
- The number 42 is converted to
UInt16. - The resulting 2-byte value is reinterpreted as a FixedString(2).
- The output appears as a single asterisk because 42 in binary is
00101010, which isn’t a printable ASCII character.
The actual bytes stored in the FixedString may not be visible or may appear differently depending on the client’s character encoding.
reinterpretAsUUID
Converts a 16-byte FixedString to a UUID data type.
Syntax:
Arguments:
fixed_string(FixedString(16)): A 16-byte string in big-endian byte order.
Returns:
- The UUID value. (
UUID)
Example:
Converting a string to UUID:
Result:
Converting back and forth between String and UUID:
Result:
- If the input string is shorter than 16 bytes, it’s padded with null bytes.
- If the input string is longer than 16 bytes, extra bytes are ignored.
This function is particularly useful when working with UUIDs stored as binary data or when converting between different UUID representations.
reinterpret
Performs byte reinterpretation of the input value as a value of the specified type. Unlike CAST, this function does not attempt to preserve the original value - if the target type cannot represent the input type, the output may be meaningless.
Syntax:
Arguments:
x(any type): Value to reinterpret.type(String): Target data type, specified as a string.
Returns:
- The input value reinterpreted as the specified type.
Example:
Result:
In this example:
- The negative integer -1 is reinterpreted as an unsigned 8-bit integer, resulting in 255.
- The integer 1 is reinterpreted as a 32-bit float, resulting in a very small number.
- The string ‘Taco’ is reinterpreted as a 32-bit unsigned integer, resulting in a seemingly random number.
Note that the results may vary depending on the system’s endianness and floating-point representation.
CAST
Converts an input value to the specified data type. Unlike the reinterpret function, CAST tries to present the same value using the new data type. If the conversion cannot be done, an exception is raised.
Syntax:
Arguments:
x- A value to convert. May be of any type.T(String) - The name of the target data type.t- The target data type.
Returns:
- Converted value.
If the input value does not fit the bounds of the target type, the result overflows. For example, CAST(-1, 'UInt8') returns 255.
Example:
Result:
Example casting timestamp to various types:
Result:
Conversion to FixedString(N) only works for arguments of type String or FixedString.
Type conversion to Nullable and back is supported.
Example:
Result:
Result:
accurateCast
Converts an input value to the specified data type. Unlike CAST, this function does not allow overflow of numeric types during conversion.
Syntax:
Arguments:
x: Value to convert.T(String): The target data type (as a string).
Returns:
- The value converted to the specified data type.
Example:
Result:
If the input value cannot be represented in the target type, accurateCast throws an exception. For example:
This query will raise an error:
Use accurateCast when you need strict type conversion without silent overflow or truncation.
accurateCastOrNull
Converts an input value to the specified data type, returning NULL if the conversion fails.
Syntax:
Arguments:
x: Input value to convert.T(String): The name of the target data type.
Returns:
- The value converted to the specified data type
T, or NULL if the conversion is not possible.
Example:
Result:
In this example:
5is successfully cast toUInt8-1cannot be cast toUInt8, so NULL is returned- ‘Carne Asada’ cannot be cast to
Int32, so NULL is returned
This function is useful when you want to handle potential casting errors gracefully without throwing exceptions.
accurateCastOrDefault
Converts an input value to the specified data type, but returns a default value if the conversion fails.
Syntax:
Arguments:
x(Any): Value to convert.T(String): The target data type (as a string).default_value(Any, optional): Value to return if the conversion fails. If not specified, returns 0 or an empty value of the target type.
Returns:
- The value converted to the specified data type
Tif successful. - The
default_valueif specified, or 0/empty value of typeTif the conversion fails.
Example:
Result:
In this example:
- ‘123’ is successfully cast to Int32
- ‘abc’ fails to cast, so it returns 0 (default for Int32)
- ‘abc’ fails to cast, but returns -1 as specified
- The function uses rounding towards zero for numeric conversions.
- The default value’s type should match the target type
T.
This function is useful when you need to handle potential conversion errors gracefully in your queries, providing a fallback value for invalid inputs.
toIntervalYear
Converts an input value to an interval of years.
Syntax:
Arguments:
n(numeric): Number of years. Can be any numeric type or a string representation of a number.
Returns:
- An interval of
nyears. Type:IntervalYear.
Example:
Result:
This example adds an interval of 1 year to the given date, resulting in a new date one year later.
toIntervalQuarter
Converts an input value to an interval of quarters of type IntervalQuarter.
Syntax:
Arguments:
n(Int32,UInt32,Int64,UInt64,Float32,Float64, orString): Number of quarters.
Returns:
- An interval of
nquarters. (IntervalQuarter)
Example:
Query:
Result:
In this example, we add an interval of 2 quarters to the date ‘2024-06-15’, resulting in ‘2024-12-15’.
The toIntervalQuarter function is particularly useful when you need to perform date arithmetic involving quarters, such as for financial reporting or seasonal analysis in your taco business data.
toIntervalMonth
Converts an input value to an interval of months of type IntervalMonth.
Syntax:
Arguments:
n(Int32,UInt32,Float32, orString): Number of months. Accepts integer numbers, string representations of integers, or floating-point numbers.
Returns:
- An interval of
nmonths. (IntervalMonth).
Example:
Query:
Result:
In this example:
- We start with June 15, 2024
- Create an interval of 3 months
- Add the interval to the original date, resulting in September 15, 2024
This function is useful for date arithmetic involving months, such as calculating subscription periods or financial quarters.
toIntervalWeek
Converts an input value to an interval of weeks.
Syntax:
Arguments:
n(numeric): Number of weeks. Can be any of:Integer,Float, orStringrepresentation of a number.
Returns:
- An interval of
nweeks. Type:IntervalWeek.
Example:
Result:
In this example, we add a two-week interval to the original date, resulting in a new date two weeks later.
The toIntervalWeek function is useful for date arithmetic operations, especially when working with weekly data or scheduling tasks.
toIntervalDay
Converts an input value to an interval of days.
Syntax:
Arguments:
n(numeric): Number of days. Can be any of:- Integer number
- Float number
- String containing a number
Returns:
- An interval of
ndays. Type:IntervalDay.
Example:
Result:
In this example:
- We start with June 15, 2024
- Add an interval of 5 days
- The result is June 20, 2024
This function is useful for date arithmetic operations, allowing you to easily add or subtract a specific number of days to/from a date.
toIntervalHour
Converts an input value to an interval of hours.
Syntax:
Arguments:
n(numeric): Number of hours. Can be any of these types:Integer,Float, orStringrepresentation of a number.
Returns:
- An interval of
nhours. Type:IntervalHour.
Example:
Result:
In this example, we add 12 hours to the date ‘2024-06-15’, resulting in ‘2024-06-15 12:00:00’.
This function is useful for date and time calculations, especially when you need to add or subtract specific numbers of hours from a timestamp or date.
toIntervalMinute
Converts an input value to an interval of minutes.
Syntax:
Arguments:
n(Int32,UInt32,Int64,UInt64,Float32,Float64,Decimal(P, S),String) — Number of minutes. Integer numbers, string representations of integers, or floating-point numbers are accepted.
Returns:
- An interval of
nminutes. (IntervalMinute)
Example:
Result:
In this example:
- We start with June 15, 2024
- Add an interval of 30 minutes
- The result is June 15, 2024, at 00:30:00
This function is useful for adding precise minute intervals to date or datetime values in queries involving time calculations or scheduling.
toIntervalSecond
Converts an input value to an interval of seconds.
Syntax:
Arguments:
n(Int32,UInt32,Int64,UInt64,Float32,Float64,Decimal(P,S),String) — Number of seconds. Integer numbers, string representations of integers, or floating-point numbers are accepted.
Returns:
- An interval of
nseconds. (IntervalSecond)
Example:
Query:
Result:
In this example:
- We start with June 15, 2024
- Add an interval of 30 seconds
- The result is June 15, 2024, at 30 seconds past midnight
This function is useful for adding precise time intervals to date or datetime values, particularly when working with timestamps or scheduling operations.
toIntervalMillisecond
Converts an input value to an interval of milliseconds.
Syntax:
Arguments:
n(numeric): Number of milliseconds. Integer, floating-point number, or string representation of a number.
Returns:
- An interval of
nmilliseconds. Type:IntervalMillisecond.
Example:
Result:
In this example, we add 500 milliseconds to the base time. The toIntervalMillisecond function converts 500 to an interval, which is then added to the base time.
This function is part of ClickHouse’s interval functions, which are useful for date and time calculations. It allows for precise time adjustments at the millisecond level.
toIntervalMicrosecond
Converts an input value to an interval of microseconds of data type IntervalMicrosecond.
Syntax:
Arguments:
n(UInt*,Int*,Float*,String): Number of microseconds. Integer numbers or string representations thereof, and float numbers.
Returns:
- Interval of n microseconds. IntervalMicrosecond.
Example:
Query:
Result:
In this example:
- We start with a base time of June 15, 2024, at noon.
- We add an interval of 500,000 microseconds (0.5 seconds).
- The result shows the base time plus the interval, which is 0.5 seconds later.
This function is useful for precise time calculations, especially when dealing with high-frequency data or when microsecond-level precision is required.
toIntervalNanosecond
Converts an input value to an interval of nanoseconds of data type IntervalNanosecond.
Syntax:
Arguments:
n(UInt*,Int*,Float*,String): Number of nanoseconds. Integer numbers or string representations thereof, and float numbers.
Returns:
- Interval of n nanoseconds. IntervalNanosecond.
Example:
Query:
Result:
In this example, we add 42 nanoseconds to the base time. The result shows the precise time with nanosecond accuracy.
parseDateTime
Converts a string to a DateTime value according to a specified format string.
Syntax:
Alias:
- TO_TIMESTAMP
- str_to_date
Arguments:
str(String) — The string to be parsed.format(String) — The format string.timezone(String, optional) — Optional timezone name.
Returns:
- A DateTime value parsed from the input string. (
DateTime)
Format Specifiers:
The format string uses MySQL-style specifiers. Common ones include:
%Y- Year (4 digits)%m- Month (01-12)%d- Day of month (01-31)%H- Hour (00-23)%i- Minutes (00-59)%s- Seconds (00-59)
For a full list, refer to the MySQL date format documentation.
Example:
Result:
This function is the inverse operation of formatDateTime.
If the timezone is not specified, the server’s timezone is used.
See also:
parseDateTimeOrZeroparseDateTimeOrNullparseDateTimeInJodaSyntax
parseDateTimeOrZero
Converts a string to a DateTime value according to a specified format. If the conversion fails, it returns a zero DateTime value (1970-01-01 00:00:00) instead of throwing an exception.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.format(String, optional): Format string. Default format is ‘%Y-%m-%d %H:%M:%S’.time_zone(String, optional): Time zone to use for parsing.
Returns:
- DateTime value parsed from the input string.
- If parsing fails, returns 1970-01-01 00:00:00.
Example:
Query:
Result:
This function is useful when you need to handle potentially invalid date strings without causing query errors. It’s particularly handy in ETL processes or when dealing with inconsistent data sources.
parseDateTimeOrNull
Converts a string to a DateTime value according to the specified format. This function is similar to parseDateTime, but returns NULL if the input string cannot be parsed.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.format(String, optional): Format string. Default format is ‘%Y-%m-%d %H:%M:%S’.time_zone(String, optional): Time zone for the returned value.
Returns:
- A
DateTimevalue if the input string can be successfully parsed. NULLif the input string cannot be parsed.
Example:
Query:
Result:
In this example, the first argument is successfully parsed into a DateTime value, while the second argument returns NULL due to invalid format.
This function is useful when you need to handle potentially invalid date strings without causing query errors. It allows for more robust data processing in scenarios where date formats may be inconsistent or unreliable.
parseDateTimeInJodaSyntax
Parses a string into a DateTime value using a format string in Joda-Time syntax.
Syntax:
Arguments:
time_string(String): String containing a date and time to parse.format(String, optional): Format string in Joda-Time syntax. Default:'yyyy-MM-dd HH:mm:ss'.timezone(String, optional): Timezone for the returned value.
Returns:
- Parsed DateTime value. (
DateTime)
Examples
Result:
- This function is the inverse of
formatDateTimeInJodaSyntax. - Supported format specifiers are similar to those in
formatDateTimeInJodaSyntax, except:S: fraction of second (not supported)z: time zone (not supported)Z: time zone offset/id (not supported)
See Also
formatDateTimeInJodaSyntaxparseDateTimeInJodaSyntaxOrZeroparseDateTimeInJodaSyntaxOrNull
parseDateTimeInJodaSyntaxOrZero
Converts a string to a DateTime value according to a format string in Joda syntax. Returns zero DateTime value (1970-01-01 00:00:00) if parsing fails.
Syntax:
Arguments:
time_string(String) — String containing a date and time to convert.format(String) — Format string in Joda syntax. Optional. Default:yyyy-MM-dd HH:mm:ss.time_zone(String) — Time zone. Optional.
Returns:
- DateTime value parsed from the input string according to the specified format.
- 1970-01-01 00:00:00 if parsing fails.
Type: DateTime
Example:
Query:
Result:
In this example, the first value is successfully parsed, while the second returns the zero DateTime due to invalid input.
See Also
parseDateTimeInJodaSyntaxOrNull
Converts a string to a DateTime value according to the specified Joda-style format string. Returns NULL if parsing fails.
Syntax:
Arguments:
str(String): String containing a date and time to convert.format(String): Format string in Joda syntax.timezone(String, optional): Timezone for the returned value.
Returns:
- DateTime value if parsing is successful, otherwise NULL. (
DateTime) / (Null)
Example:
Result:
This function is similar to parseDateTimeInJodaSyntax, but returns NULL instead of throwing an exception when parsing fails.
- The function supports most of the format specifiers from Joda-Time’s DateTimeFormat, but some advanced features like time zone handling (
z,Z) are not supported. - For handling time zones, use the optional
timezoneparameter instead.
See also:
- [parseDateTimeInJodaSyntax]
- [parseDateTimeInJodaSyntaxOrZero]
parseDateTimeBestEffort
Converts a date and time string to a DateTime data type using a best-effort approach.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for parsing the string.
Returns:
- A
DateTimevalue parsed from the input string.
Supported formats
The function attempts to parse various date and time formats, including:
- ISO 8601
- RFC 1123
- ClickHouse’s native formats
- Unix timestamps (9-10 digit strings)
- Various common formats: YYYY-MM-DD hh:mm:ss, DD/MM/YYYY hh:mm:ss, YYYYMMDD, etc.
- Partial date strings: YYYY, YYYYMM, DD hh:mm, etc.
- Strings with time zone offsets: YYYY-MM-DD hh:mm:ss ±h:mm
For partial dates, missing components are filled with default values. If a year is omitted, the current year is used, unless it results in a future date (in which case the previous year is used).
Examples
Parse a standard format:
Result:
Parse a Unix timestamp:
Result:
Parse with a specific time zone:
Result:
- The function is more flexible but potentially slower than stricter parsing functions.
- If parsing fails, the function throws an exception.
See also:
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone. The function parses time_string according to the time zone.
Returns:
time_stringconverted to theDateTime32data type.
Example:
Query:
Result:
This function is particularly useful when dealing with dates in various formats, especially in data import scenarios where the exact format might not be known in advance.
If the year is not specified, it is considered to be equal to the current year. If the resulting DateTime happens to be in the future (even by a second after the current moment), then the current year is substituted by the previous year.
See also:
- [toDate]
- [toDateTime]
parseDateTimeBestEffortUS
Converts a date and time string to a DateTime data type, with a preference for US date formats in ambiguous cases.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for parsing the string.
Returns:
- A
DateTimevalue.
Details
This function behaves similarly to parseDateTimeBestEffort for ISO date formats (e.g., YYYY-MM-DD hh:mm:ss) and other unambiguous formats. However, for ambiguous formats like MM/DD/YYYY or MM-DD-YY, it prefers the US date format (month before day).
As an exception, if the “month” value is greater than 12 and less than or equal to 31, the function reverts to the behavior of parseDateTimeBestEffort. For example, ‘15/08/2020’ is parsed as August 15, 2020.
Example:
Query:
Result:
In this example:
- ‘02/03/2020’ is interpreted as February 3, 2020 (US format)
- ‘2020-02-03’ is unambiguous and parsed as February 3, 2020
- ‘15/08/2020’ is parsed as August 15, 2020 due to the exception rule
This function is particularly useful when dealing with date strings that may come in various formats, especially those originating from US-based systems or users.
parseDateTimeBestEffortOrNull
Converts a string containing a date and time to a DateTime value, using heuristics to parse a wide variety of date/time formats. Returns NULL if it cannot parse the input string.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for the returned value.
Returns:
- A
DateTimevalue if the input can be parsed. NULLif the input cannot be parsed.
Examples
Query:
Result:
Query:
Result:
This function is particularly useful when dealing with dates in various formats or when you want to handle invalid date strings gracefully in your queries.
See also:
- [parseDateTimeBestEffort]
- [parseDateTimeBestEffortOrZero]
- [toDateTime]
parseDateTime32BestEffortOrNull
Converts a string containing a date and time to DateTime32 data type using a best-effort approach. Returns NULL if the conversion is unsuccessful.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
Returns:
- A value of
DateTime32type if conversion is successful, otherwiseNULL.
Example:
Query:
Result:
This function attempts to parse various date and time formats, including:
- ISO 8601
- RFC 1123
- ClickHouse’s native formats
- Common date and time representations
If the input cannot be parsed, it returns NULL instead of throwing an exception.
See Also
parseDateTimeBestEffortOrZero
Converts a string containing a date and time to a DateTime value, using a best-effort approach. If the conversion fails, it returns a zero DateTime value (1970-01-01 00:00:00) instead of throwing an exception.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
Returns:
- A
DateTimevalue if conversion is successful, otherwise 1970-01-01 00:00:00.
Example:
Query:
Result:
In this example, ‘Taco Tuesday’ cannot be parsed as a valid date and time, so it returns the zero DateTime value.
This function is particularly useful when dealing with potentially inconsistent date formats in your data, as it allows processing to continue without throwing exceptions for unparseable values.
parseDateTime32BestEffortOrZero
Converts a string containing a date and time to a DateTime32 value using a best-effort approach. If the conversion fails, it returns a zero date (1970-01-01 00:00:00).
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
Returns:
- A
DateTime32value representing the parsed date and time, or 1970-01-01 00:00:00 if parsing fails.
Example:
Result:
This function attempts to parse various date and time formats, including:
- ISO 8601
- RFC 1123
- ClickHouse’s native format
- Common formats like ‘YYYY-MM-DD hh:mm:ss’, ‘DD/MM/YYYY hh:mm:ss’, etc.
- Unix timestamps
If the parsing fails, it returns the epoch start (1970-01-01 00:00:00) instead of throwing an error.
This function is particularly useful when dealing with potentially inconsistent date formats in your data, as it provides a fallback value rather than causing query failures.
parseDateTimeBestEffortUSOrNull
Converts a string containing a date and time to a DateTime value, preferring US date format (MM/DD/YYYY) in case of ambiguity. Returns NULL if the conversion is unsuccessful.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
Returns:
- A DateTime value if conversion is successful, otherwise NULL. [
DateTime] or [Null]
Examples
Query:
Result:
In this example:
- ‘us_date’ is parsed as January 2, 2023
- ‘ambiguous_date’ is parsed as February 1, 2023 (preferring US format)
- ‘iso_date’ is parsed correctly regardless of format preference
- ‘invalid_date’ returns NULL as it cannot be parsed
- This function behaves similarly to
parseDateTimeBestEffort, but prefers US date format for ambiguous cases. - For unambiguous formats like ISO 8601, the function behaves the same as
parseDateTimeBestEffort. - The function supports various date and time formats, including Unix timestamps.
See also:
- [parseDateTimeBestEffort]
- [parseDateTimeBestEffortOrNull]
- [parseDateTimeBestEffortUS]
parseDateTimeBestEffortUSOrZero
Converts a string containing a date and time to a DateTime value, with a preference for US date formats in ambiguous cases. If the conversion fails, it returns a zero date (1970-01-01 00:00:00).
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for the returned value.
Returns:
- A
DateTimevalue representing the parsed date and time. - Returns 1970-01-01 00:00:00 if parsing fails.
Examples
Query:
Result:
In this example:
us_dateis parsed as February 10, 2023ambiguous_dateis parsed as October 2, 2023 (US format preferred)iso_dateis parsed correctly as February 10, 2023invalid_datereturns the zero date
- This function is particularly useful when working with data that may contain dates in US format (MM/DD/YYYY).
- For unambiguous date formats (like ISO 8601), it behaves similarly to
parseDateTimeBestEffort. - If the month value is greater than 12, it will be interpreted as day-first format.
See also:
- [parseDateTimeBestEffort]
- [parseDateTimeBestEffortOrNull]
- [toDateTime]
parseDateTime64BestEffort
Converts a string containing a date and time to a DateTime64 value, attempting to parse various date/time formats.
Syntax:
Arguments:
time_string(String): String containing a date or date with time to convert.precision(UInt8, optional): Precision of the result. Possible values: 3 (milliseconds), 6 (microseconds). Default: 3.timezone(String, optional): Timezone for the returned value.
Returns:
- A
DateTime64value representing the parsed date and time.
Example:
Result:
This function attempts to parse various date and time formats, including ISO 8601, RFC 1123, and common variations. It’s particularly useful when dealing with dates in different formats from external sources.
The function uses the server’s timezone by default if not specified.
parseDateTime64BestEffortUS
Converts a string containing a date and time to a DateTime64 value, preferring US date format (MM/DD/YYYY) in ambiguous cases. This function is similar to parseDateTime64BestEffort, but with special handling for US-style dates.
Syntax:
Arguments:
time_string(String): String containing a date or date with time to convert.precision(UInt8, optional): Precision for fractional seconds: 3 for milliseconds, 6 for microseconds. Default is 3.timezone(String, optional): Timezone for the returned value.
Returns:
- A
DateTime64value representing the parsed date and time.
Example:
Result:
In this example, ‘01/02/2023’ is interpreted as January 2nd in US format, while ‘02/01/2023’ is still interpreted as February 1st.
- For unambiguous formats like ISO 8601 (YYYY-MM-DD), the function behaves the same as
parseDateTime64BestEffort. - If the month value is greater than 12 and less than or equal to 31, it’s treated as a day, falling back to non-US interpretation.
- The function supports various date and time formats, including Unix timestamps and partial date strings.
See also:
parseDateTime64BestEffortparseDateTime64BestEffortUSOrNullparseDateTime64BestEffortUSOrZero
parseDateTime64BestEffortOrNull
Converts a string containing a date and time to a DateTime64 value, with support for parsing various date and time formats. If the input cannot be parsed, it returns NULL.
Syntax:
Arguments:
time_string(String): String containing a date or date with time to convert.precision(UInt8): Precision of the result (0-9). Default: 3 (milliseconds).timezone(String): Timezone for the returned value. Optional.
Returns:
- A
DateTime64value if parsing is successful, otherwise NULL. (DateTime64orNull)
Example:
Let’s parse some taco order timestamps:
Result:
This function attempts to parse various date and time formats, including ISO 8601, RFC 1123, and some common textual representations. It’s particularly useful when dealing with inconsistent timestamp formats in your taco order data.
parseDateTime64BestEffortOrZero
Converts a string containing a date and time to a DateTime64 value, using a best-effort approach. If the conversion fails, it returns a zero value.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.precision(UInt8, optional): Precision of the result (0-9). Default: 3.timezone(String, optional): Timezone for the returned value.
Returns:
- Parsed DateTime64 value, or 1970-01-01 00:00:00 if parsing fails. (
DateTime64)
Example:
Result:
This function attempts to parse various date and time formats, including:
- ISO 8601
- RFC 1123
- ClickHouse’s native formats
- Common formats like ‘YYYY-MM-DD hh:mm:ss’
If the input cannot be parsed, it returns the epoch start (1970-01-01 00:00:00) instead of throwing an error.
See Also
parseDateTime64BestEffortUSOrNull
Converts a string containing a date and time to a DateTime64 value, preferring US date format (MM/DD/YYYY) in ambiguous cases. Returns NULL if the conversion fails.
Syntax:
Arguments:
time_string(String): String containing a date and time to convert.precision(UInt8, optional): Precision of the result (0-9). Default is 3 (milliseconds).timezone(String, optional): Timezone for the returned value.
Returns:
- A
DateTime64value if conversion is successful, otherwise NULL. [DateTime64] or [Null]
Example:
Result:
This function is particularly useful when dealing with date strings that may be in US format, and you want to handle potential parsing errors by returning NULL instead of throwing an exception.
parseDateTime64BestEffortUSOrZero
Converts a string containing a date and time to a DateTime64 value, preferring US date format (MM/DD/YYYY) in ambiguous cases. Returns zero DateTime64 value (1970-01-01 00:00:00) if parsing fails.
Syntax:
Arguments:
time_string(String): String containing a date or date with time to convert.precision(UInt8, optional): Precision of the result (0-9). Default is 3 (milliseconds).timezone(String, optional): Timezone for the returned value.
Returns:
- DateTime64 value parsed from the input string.
- 1970-01-01 00:00:00 (with specified precision) if parsing fails.
Example:
Query:
Result:
This function is particularly useful when dealing with potentially inconsistent date formats in your data, especially when US date formats are expected. It provides a safe way to parse dates without causing query errors for invalid inputs.
toLowCardinality
Converts an input value to the LowCardinality version of the same data type.
Syntax:
Arguments:
expr(supported data types): Expression resulting in one of the supported data types.
Returns:
- The result of
exprconverted to the LowCardinality version of its type.
Example:
Result:
In this example, the string ‘Carne Asada’ is converted to a LowCardinality(String) type.
To convert data from the LowCardinality data type to its base type, use the CAST function. For example:
Result:
This function is particularly useful for optimizing storage and query performance for columns with a low number of distinct values, such as categories or statuses in a taco order system.
toUnixTimestamp64Milli
Converts a DateTime64 value to an Int64 value representing Unix timestamp with millisecond precision.
Syntax:
Arguments:
value(DateTime64): DateTime64 value with any precision.
Returns:
- Unix timestamp with millisecond precision.
Int64.
The output value is a timestamp in UTC, not in the timezone of the input DateTime64.
Example:
Result:
In this example, we convert a DateTime64 representing May 30, 2023, at 12:00:00.123 PM UTC to its Unix timestamp in milliseconds.
toUnixTimestamp64Micro
Converts a DateTime64 value to an Int64 value representing Unix timestamp with microsecond precision.
Syntax:
Arguments:
value(DateTime64): DateTime64 value with any precision.
Returns:
- Unix timestamp with microsecond precision.
Int64.
- The output value is a timestamp in UTC, not in the timezone of the input DateTime64.
- The function scales the input value up or down as needed, depending on its precision.
Example:
Query:
Result:
In this example, we convert a DateTime64 value to a Unix timestamp with microsecond precision. The result is the number of microseconds since the Unix epoch (1970-01-01 00:00:00 UTC).
toUnixTimestamp64Nano
Converts a DateTime64 value to an Int64 value representing nanoseconds since the Unix epoch.
Syntax:
Arguments:
value(DateTime64): A DateTime64 value with any precision.
Returns:
- The number of nanoseconds since the Unix epoch (1970-01-01 00:00:00 UTC). [
Int64]
The output value is a timestamp in UTC, not in the timezone of the input DateTime64.
Example:
Result:
In this example, we convert a DateTime64 representing a taco order time to nanoseconds since the Unix epoch. The result can be used for precise timestamp comparisons or calculations.
fromUnixTimestamp64Milli
Converts an Int64 Unix timestamp in milliseconds to a DateTime64 value with millisecond precision.
Syntax:
Arguments:
value(Int64): Unix timestamp in milliseconds.timezone(String, optional): Timezone name for the returned value.
Returns:
- DateTime64 value with 3 decimal places (millisecond precision). [
DateTime64(3)]
Example:
Result:
This function converts the Unix timestamp 1234567891011 (milliseconds since epoch) to a DateTime64 value in the UTC timezone.
The input value is treated as a UTC timestamp, not a timestamp in the specified (or default) timezone.
fromUnixTimestamp64Micro
Converts a 64-bit integer representing microseconds since the Unix epoch to a DateTime64 value with microsecond precision.
Syntax:
Arguments:
value(Int64): Unix timestamp in microseconds.timezone(String, optional): Timezone for the returned value.
Returns:
DateTime64value with 6 decimal places (microsecond precision).
Example:
Result:
The input value is treated as a UTC timestamp, not a timestamp in the specified (or default) timezone.
fromUnixTimestamp64Nano
Converts a 64-bit integer representing nanoseconds since the Unix epoch to a DateTime64 value with nanosecond precision.
Syntax:
Arguments:
value(Int64): Unix timestamp in nanoseconds.timezone(String, optional): Timezone for the returned value.
Returns:
DateTime64value with nanosecond (9 decimal places) precision.
- The input
valueis treated as a UTC timestamp, not a timestamp in the given (or implicit) timezone. - If no timezone is specified, the server’s timezone is used.
Example:
Query:
Result:
In this example, the Unix timestamp 1234567891011 nanoseconds (which is about 20 minutes and 34 seconds after the Unix epoch) is converted to a DateTime64 value with nanosecond precision in the UTC timezone.
formatRow
Converts arbitrary expressions into a string using the specified format.
Syntax:
Arguments:
format(String) — Text format (e.g. CSV, TSV).x,y, … (any type) — Expressions to be formatted.
Returns:
A formatted string. For text formats, it’s usually terminated with a newline character.
Example:
Result:
Only row-based formats are supported in this function.
formatRowNoNewline
Converts arbitrary expressions into a string via given format. Similar to formatRow, but trims the last newline character if present.
Syntax:
Arguments:
format(String): Text format. For example, CSV, TSV.x,y, … (any): Expressions to be formatted.
Returns:
- A formatted string without a trailing newline.
Example:
Result:
This function is useful when you need to format data without adding an extra newline at the end, which can be important in certain data processing or output scenarios.
Only row-based formats are supported in this function.