Type Conversion Functions
Common Issues of Numeric Conversionsβ
When you convert a value from one to another data type, you should remember that in common case, it is an unsafe operation that can lead to a data loss. A data loss can occur if you try to fit value from a larger data type to a smaller data type, or if you convert values between different data types.
ClickHouse has the same behavior as C++ programs.
toInt(8|16|32|64|128|256)β
Converts an input value to the Int data type. This function family includes:
toInt8(expr)
β Results in theInt8
data type.toInt16(expr)
β Results in theInt16
data type.toInt32(expr)
β Results in theInt32
data type.toInt64(expr)
β Results in theInt64
data type.toInt128(expr)
β Results in theInt128
data type.toInt256(expr)
β Results in theInt256
data type.
Arguments
expr
β Expression returning a number or a string with the decimal representation of a number. Binary, octal, and hexadecimal representations of numbers are not supported. Leading zeroes are stripped.
Returned value
Integer value in the Int8
, Int16
, Int32
, Int64
, Int128
or Int256
data type.
Functions use rounding towards zero, meaning they truncate fractional digits of numbers.
The behavior of functions for the NaN and Inf arguments is undefined. Remember about numeric convertions issues, when using the functions.
Example
Query:
SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8);
Result:
ββββββββββtoInt64(nan)ββ¬βtoInt32(32)ββ¬βtoInt16('16')ββ¬βtoInt8(8.8)ββ
β -9223372036854775808 β 32 β 16 β 8 β
ββββββββββββββββββββββββ΄ββββββββββββββ΄ββββββββββββββββ΄ββββββββββββββ
toInt(8|16|32|64|128|256)OrZeroβ
It takes an argument of type String and tries to parse it into Int (8 | 16 | 32 | 64 | 128 | 256). If failed, returns 0.
Example
Query:
SELECT toInt64OrZero('123123'), toInt8OrZero('123qwe123');
Result:
ββtoInt64OrZero('123123')ββ¬βtoInt8OrZero('123qwe123')ββ
β 123123 β 0 β
βββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββ
toInt(8|16|32|64|128|256)OrNullβ
It takes an argument of type String and tries to parse it into Int (8 | 16 | 32 | 64 | 128 | 256). If failed, returns NULL.
Example
Query:
SELECT toInt64OrNull('123123'), toInt8OrNull('123qwe123');
Result:
ββtoInt64OrNull('123123')ββ¬βtoInt8OrNull('123qwe123')ββ
β 123123 β α΄Ία΅α΄Έα΄Έ β
βββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββ
toInt(8|16|32|64|128|256)OrDefaultβ
It takes an argument of type String and tries to parse it into Int (8 | 16 | 32 | 64 | 128 | 256). If failed, returns the default type value.
Example
Query:
SELECT toInt64OrDefault('123123', cast('-1' as Int64)), toInt8OrDefault('123qwe123', cast('-1' as Int8));
Result:
ββtoInt64OrDefault('123123', CAST('-1', 'Int64'))ββ¬βtoInt8OrDefault('123qwe123', CAST('-1', 'Int8'))ββ
β 123123 β -1 β
βββββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββ
toUInt(8|16|32|64|256)β
Converts an input value to the UInt data type. This function family includes:
toUInt8(expr)
β Results in theUInt8
data type.toUInt16(expr)
β Results in theUInt16
data type.toUInt32(expr)
β Results in theUInt32
data type.toUInt64(expr)
β Results in theUInt64
data type.toUInt256(expr)
β Results in theUInt256
data type.
Arguments
expr
β Expression returning a number or a string with the decimal representation of a number. Binary, octal, and hexadecimal representations of numbers are not supported. Leading zeroes are stripped.
Returned value
Integer value in the UInt8
, UInt16
, UInt32
, UInt64
or UInt256
data type.
Functions use rounding towards zero, meaning they truncate fractional digits of numbers.
The behavior of functions for negative agruments and for the NaN and Inf arguments is undefined. If you pass a string with a negative number, for example '-32'
, ClickHouse raises an exception. Remember about numeric convertions issues, when using the functions.
Example
Query:
SELECT toUInt64(nan), toUInt32(-32), toUInt16('16'), toUInt8(8.8);
Result:
ββββββββtoUInt64(nan)ββ¬βtoUInt32(-32)ββ¬βtoUInt16('16')ββ¬βtoUInt8(8.8)ββ
β 9223372036854775808 β 4294967264 β 16 β 8 β
βββββββββββββββββββββββ΄ββββββββββββββββ΄βββββββββββββββββ΄βββββββββββββββ
toUInt(8|16|32|64|256)OrZeroβ
toUInt(8|16|32|64|256)OrNullβ
toUInt(8|16|32|64|256)OrDefaultβ
toFloat(32|64)β
toFloat(32|64)OrZeroβ
toFloat(32|64)OrNullβ
toFloat(32|64)OrDefaultβ
toDateβ
Alias: DATE
.
toDateOrZeroβ
toDateOrNullβ
toDateOrDefaultβ
toDateTimeβ
toDateTimeOrZeroβ
toDateTimeOrNullβ
toDateTimeOrDefaultβ
toDate32β
Converts the argument to the Date32 data type. If the value is outside the range returns the border values supported by Date32
. If the argument has Date type, borders of Date
are taken into account.
Syntax
toDate32(expr)
Arguments
Returned value
- A calendar date.
Type: Date32.
Example
- The value is within the range:
SELECT toDate32('1955-01-01') AS value, toTypeName(value);
βββββββvalueββ¬βtoTypeName(toDate32('1925-01-01'))ββ
β 1955-01-01 β Date32 β
ββββββββββββββ΄βββββββββββββββββββββββββββββββββββββ
- The value is outside the range:
SELECT toDate32('1924-01-01') AS value, toTypeName(value);
βββββββvalueββ¬βtoTypeName(toDate32('1925-01-01'))ββ
β 1925-01-01 β Date32 β
ββββββββββββββ΄βββββββββββββββββββββββββββββββββββββ
- With
Date
-type argument:
SELECT toDate32(toDate('1924-01-01')) AS value, toTypeName(value);
βββββββvalueββ¬βtoTypeName(toDate32(toDate('1924-01-01')))ββ
β 1970-01-01 β Date32 β
ββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββ
toDate32OrZeroβ
The same as toDate32 but returns the min value of Date32 if invalid argument is received.
Example
Query:
SELECT toDate32OrZero('1924-01-01'), toDate32OrZero('');
Result:
ββtoDate32OrZero('1924-01-01')ββ¬βtoDate32OrZero('')ββ
β 1925-01-01 β 1925-01-01 β
ββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββ
toDate32OrNullβ
The same as toDate32 but returns NULL
if invalid argument is received.
Example
Query:
SELECT toDate32OrNull('1955-01-01'), toDate32OrNull('');
Result:
ββtoDate32OrNull('1955-01-01')ββ¬βtoDate32OrNull('')ββ
β 1955-01-01 β α΄Ία΅α΄Έα΄Έ β
ββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββ
toDate32OrDefaultβ
Converts the argument to the Date32 data type. If the value is outside the range returns the lower border value supported by Date32
. If the argument has Date type, borders of Date
are taken into account. Returns default value if invalid argument is received.
Example
Query:
SELECT
toDate32OrDefault('1930-01-01', toDate32('2020-01-01')),
toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'));
Result:
ββtoDate32OrDefault('1930-01-01', toDate32('2020-01-01'))ββ¬βtoDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))ββ
β 1930-01-01 β 2020-01-01 β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
toDecimal(32|64|128|256)β
Converts value
to the Decimal data type with precision of S
. The value
can be a number or a string. The S
(scale) parameter specifies the number of decimal places.
toDecimal32(value, S)
toDecimal64(value, S)
toDecimal128(value, S)
toDecimal256(value, S)
toDecimal(32|64|128|256)OrNullβ
Converts an input string to a Nullable(Decimal(P,S)) data type value. This family of functions include:
toDecimal32OrNull(expr, S)
β Results inNullable(Decimal32(S))
data type.toDecimal64OrNull(expr, S)
β Results inNullable(Decimal64(S))
data type.toDecimal128OrNull(expr, S)
β Results inNullable(Decimal128(S))
data type.toDecimal256OrNull(expr, S)
β Results inNullable(Decimal256(S))
data type.
These functions should be used instead of toDecimal*()
functions, if you prefer to get a NULL
value instead of an exception in the event of an input value parsing error.
Arguments
expr
β Expression, returns a value in the String data type. ClickHouse expects the textual representation of the decimal number. For example,'1.111'
.S
β Scale, the number of decimal places in the resulting value.
Returned value
A value in the Nullable(Decimal(P,S))
data type. The value contains:
- Number with
S
decimal places, if ClickHouse interprets the input string as a number. NULL
, if ClickHouse canβt interpret the input string as a number or if the input number contains more thanS
decimal places.
Examples
Query:
SELECT toDecimal32OrNull(toString(-1.111), 5) AS val, toTypeName(val);
Result:
βββββvalββ¬βtoTypeName(toDecimal32OrNull(toString(-1.111), 5))ββ
β -1.111 β Nullable(Decimal(9, 5)) β
ββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT toDecimal32OrNull(toString(-1.111), 2) AS val, toTypeName(val);
Result:
βββvalββ¬βtoTypeName(toDecimal32OrNull(toString(-1.111), 2))ββ
β α΄Ία΅α΄Έα΄Έ β Nullable(Decimal(9, 2)) β
ββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββ
toDecimal(32|64|128|256)OrDefaultβ
Converts an input string to a Decimal(P,S) data type value. This family of functions include:
toDecimal32OrDefault(expr, S)
β Results inDecimal32(S)
data type.toDecimal64OrDefault(expr, S)
β Results inDecimal64(S)
data type.toDecimal128OrDefault(expr, S)
β Results inDecimal128(S)
data type.toDecimal256OrDefault(expr, S)
β Results inDecimal256(S)
data type.
These functions should be used instead of toDecimal*()
functions, if you prefer to get a default value instead of an exception in the event of an input value parsing error.
Arguments
expr
β Expression, returns a value in the String data type. ClickHouse expects the textual representation of the decimal number. For example,'1.111'
.S
β Scale, the number of decimal places in the resulting value.
Returned value
A value in the Decimal(P,S)
data type. The value contains:
- Number with
S
decimal places, if ClickHouse interprets the input string as a number. - Default
Decimal(P,S)
data type value, if ClickHouse canβt interpret the input string as a number or if the input number contains more thanS
decimal places.
Examples
Query:
SELECT toDecimal32OrDefault(toString(-1.111), 5) AS val, toTypeName(val);
Result:
βββββvalββ¬βtoTypeName(toDecimal32OrDefault(toString(-1.111), 5))ββ
β -1.111 β Decimal(9, 5) β
ββββββββββ΄ββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT toDecimal32OrDefault(toString(-1.111), 2) AS val, toTypeName(val);
Result:
ββvalββ¬βtoTypeName(toDecimal32OrDefault(toString(-1.111), 2))ββ
β 0 β Decimal(9, 2) β
βββββββ΄ββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
toDecimal(32|64|128|256)OrZeroβ
Converts an input value to the Decimal(P,S) data type. This family of functions include:
toDecimal32OrZero( expr, S)
β Results inDecimal32(S)
data type.toDecimal64OrZero( expr, S)
β Results inDecimal64(S)
data type.toDecimal128OrZero( expr, S)
β Results inDecimal128(S)
data type.toDecimal256OrZero( expr, S)
β Results inDecimal256(S)
data type.
These functions should be used instead of toDecimal*()
functions, if you prefer to get a 0
value instead of an exception in the event of an input value parsing error.
Arguments
expr
β Expression, returns a value in the String data type. ClickHouse expects the textual representation of the decimal number. For example,'1.111'
.S
β Scale, the number of decimal places in the resulting value.
Returned value
A value in the Nullable(Decimal(P,S))
data type. The value contains:
- Number with
S
decimal places, if ClickHouse interprets the input string as a number. - 0 with
S
decimal places, if ClickHouse canβt interpret the input string as a number or if the input number contains more thanS
decimal places.
Example
Query:
SELECT toDecimal32OrZero(toString(-1.111), 5) AS val, toTypeName(val);
Result:
βββββvalββ¬βtoTypeName(toDecimal32OrZero(toString(-1.111), 5))ββ
β -1.111 β Decimal(9, 5) β
ββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT toDecimal32OrZero(toString(-1.111), 2) AS val, toTypeName(val);
Result:
βββvalββ¬βtoTypeName(toDecimal32OrZero(toString(-1.111), 2))ββ
β 0.00 β Decimal(9, 2) β
ββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββ
toStringβ
Functions for converting between numbers, strings (but not fixed strings), dates, and dates with times. All these functions accept one argument.
When converting to or from a string, the value is formatted or parsed using the same rules as for the TabSeparated format (and almost all other text formats). If the string canβt be parsed, an exception is thrown and the request is canceled.
When converting dates to numbers or vice versa, the date corresponds to the number of days since the beginning of the Unix epoch. When converting dates with times to numbers or vice versa, the date with time corresponds to the number of seconds since the beginning of the Unix epoch.
The date and date-with-time formats for the toDate/toDateTime functions are defined as follows:
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
As an exception, if converting from UInt32, Int32, UInt64, or Int64 numeric types to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date. This allows support for the common occurrence of writing βtoDate(unix_timestamp)β, which otherwise would be an error and would require writing the more cumbersome βtoDate(toDateTime(unix_timestamp))β.
Conversion between a date and date with time is performed the natural way: by adding a null time or dropping the time.
Conversion between numeric types uses the same rules as assignments between different numeric types in C++.
Additionally, the toString function of the DateTime argument can take a second String argument containing the name of the time zone. Example: Asia/Yekaterinburg
In this case, the time is formatted according to the specified time zone.
Example
Query:
SELECT
now() AS now_local,
toString(now(), 'Asia/Yekaterinburg') AS now_yekat;
Result:
ββββββββββββnow_localββ¬βnow_yekatββββββββββββ
β 2016-06-15 00:11:21 β 2016-06-15 02:11:21 β
βββββββββββββββββββββββ΄ββββββββββββββββββββββ
Also see the toUnixTimestamp
function.
toFixedString(s, N)β
Converts a String type argument to a FixedString(N) type (a string with fixed length N). N must be a constant. If the 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.
toStringCutToZero(s)β
Accepts a String or FixedString argument. Returns the String with the content truncated at the first zero byte found.
Example
Query:
SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut;
Result:
ββsββββββββββββββ¬βs_cutββ
β foo\0\0\0\0\0 β foo β
βββββββββββββββββ΄ββββββββ
Query:
SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut;
Result:
ββsβββββββββββ¬βs_cutββ
β foo\0bar\0 β foo β
ββββββββββββββ΄ββββββββ
reinterpretAsUInt(8|16|32|64)β
reinterpretAsInt(8|16|32|64)β
reinterpretAsFloat(32|64)β
reinterpretAsDateβ
reinterpretAsDateTimeβ
These functions accept a string and interpret the bytes placed at the beginning of the string as a number in host order (little endian). If the string isnβt long enough, the functions work 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. A date is interpreted as the number of days since the beginning of the Unix Epoch, and a date with time is interpreted as the number of seconds since the beginning of the Unix Epoch.
reinterpretAsStringβ
This function accepts a number or date or date with time, and returns a string containing bytes representing the corresponding value in host order (little endian). Null bytes are dropped from the end. For example, a UInt32 type value of 255 is a string that is one byte long.
reinterpretAsFixedStringβ
This function accepts a number or date or date with time, and returns a FixedString containing bytes representing the corresponding value in host order (little endian). Null bytes are dropped from the end. For example, a UInt32 type value of 255 is a FixedString that is one byte long.
reinterpretAsUUIDβ
Accepts 16 bytes string and returns UUID containing bytes representing the corresponding value in network byte order (big-endian). If the string isn't long enough, the function works as if the string is padded with the necessary number of null bytes to the end. If the string longer than 16 bytes, the extra bytes at the end are ignored.
Syntax
reinterpretAsUUID(fixed_string)
Arguments
fixed_string
β Big-endian byte string. FixedString.
Returned value
- The UUID type value. UUID.
Examples
String to UUID.
Query:
SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')));
Result:
ββreinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))ββ
β 08090a0b-0c0d-0e0f-0001-020304050607 β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Going back and forth from String to UUID.
Query:
WITH
generateUUIDv4() AS uuid,
identity(lower(hex(reverse(reinterpretAsString(uuid))))) AS str,
reinterpretAsUUID(reverse(unhex(str))) AS uuid2
SELECT uuid = uuid2;
Result:
ββequals(uuid, uuid2)ββ
β 1 β
βββββββββββββββββββββββ
reinterpret(x, T)β
Uses the same source in-memory bytes sequence for x
value and reinterprets it to destination type.
Syntax
reinterpret(x, type)
Arguments
x
β Any type.type
β Destination type. String.
Returned value
- Destination type value.
Examples
Query:
SELECT reinterpret(toInt8(-1), 'UInt8') as int_to_uint,
reinterpret(toInt8(1), 'Float32') as int_to_float,
reinterpret('1', 'UInt32') as string_to_int;
Result:
ββint_to_uintββ¬βint_to_floatββ¬βstring_to_intββ
β 255 β 1e-45 β 49 β
βββββββββββββββ΄βββββββββββββββ΄ββββββββββββββββ
CAST(x, T)β
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 can not be done then an exception is raised.
Several syntax variants are supported.
Syntax
CAST(x, T)
CAST(x AS t)
x::t
Arguments
x
β A value to convert. May be of any type.T
β The name of the target data type. String.t
β The target data type.
Returned value
- Converted value.
note
If the input value does not fit the bounds of the target type, the result overflows. For example, CAST(-1, 'UInt8')
returns 255
.
Examples
Query:
SELECT
CAST(toInt8(-1), 'UInt8') AS cast_int_to_uint,
CAST(1.5 AS Decimal(3,2)) AS cast_float_to_decimal,
'1'::Int32 AS cast_string_to_int;
Result:
ββcast_int_to_uintββ¬βcast_float_to_decimalββ¬βcast_string_to_intββ
β 255 β 1.50 β 1 β
ββββββββββββββββββββ΄ββββββββββββββββββββββββ΄βββββββββββββββββββββ
Query:
SELECT
'2016-06-15 23:00:00' AS timestamp,
CAST(timestamp AS DateTime) AS datetime,
CAST(timestamp AS Date) AS date,
CAST(timestamp, 'String') AS string,
CAST(timestamp, 'FixedString(22)') AS fixed_string;
Result:
ββtimestampββββββββββββ¬ββββββββββββdatetimeββ¬βββββββdateββ¬βstringβββββββββββββββ¬βfixed_stringβββββββββββββββ
β 2016-06-15 23:00:00 β 2016-06-15 23:00:00 β 2016-06-15 β 2016-06-15 23:00:00 β 2016-06-15 23:00:00\0\0\0 β
βββββββββββββββββββββββ΄ββββββββββββββββββββββ΄βββββββββββββ΄ββββββββββββββββββββββ΄ββββββββββββββββββββββββββββ
Conversion to FixedString(N) only works for arguments of type String or FixedString.
Type conversion to Nullable and back is supported.
Example
Query:
SELECT toTypeName(x) FROM t_null;
Result:
ββtoTypeName(x)ββ
β Int8 β
β Int8 β
βββββββββββββββββ
Query:
SELECT toTypeName(CAST(x, 'Nullable(UInt16)')) FROM t_null;
Result:
ββtoTypeName(CAST(x, 'Nullable(UInt16)'))ββ
β Nullable(UInt16) β
β Nullable(UInt16) β
βββββββββββββββββββββββββββββββββββββββββββ
See also
- cast_keep_nullable setting
accurateCast(x, T)β
Converts x
to the T
data type.
The difference from cast(x, T) is that accurateCast
does not allow overflow of numeric types during cast if type value x
does not fit the bounds of type T
. For example, accurateCast(-1, 'UInt8')
throws an exception.
Example
Query:
SELECT cast(-1, 'UInt8') as uint8;
Result:
ββuint8ββ
β 255 β
βββββββββ
Query:
SELECT accurateCast(-1, 'UInt8') as uint8;
Result:
Code: 70. DB::Exception: Received from localhost:9000. DB::Exception: Value in column Int8 cannot be safely converted into type UInt8: While processing accurateCast(-1, 'UInt8') AS uint8.
accurateCastOrNull(x, T)β
Converts input value x
to the specified data type T
. Always returns Nullable type and returns NULL if the casted value is not representable in the target type.
Syntax
accurateCastOrNull(x, T)
Parameters
x
β Input value.T
β The name of the returned data type.
Returned value
- The value, converted to the specified data type
T
.
Example
Query:
SELECT toTypeName(accurateCastOrNull(5, 'UInt8'));
Result:
ββtoTypeName(accurateCastOrNull(5, 'UInt8'))ββ
β Nullable(UInt8) β
ββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT
accurateCastOrNull(-1, 'UInt8') as uint8,
accurateCastOrNull(128, 'Int8') as int8,
accurateCastOrNull('Test', 'FixedString(2)') as fixed_string;
Result:
ββuint8ββ¬βint8ββ¬βfixed_stringββ
β α΄Ία΅α΄Έα΄Έ β α΄Ία΅α΄Έα΄Έ β α΄Ία΅α΄Έα΄Έ β
βββββββββ΄βββββββ΄βββββββββββββββ
accurateCastOrDefault(x, T[, default_value])β
Converts input value x
to the specified data type T
. Returns default type value or default_value
if specified if the casted value is not representable in the target type.
Syntax
accurateCastOrDefault(x, T)
Parameters
x
β Input value.T
β The name of the returned data type.default_value
β Default value of returned data type.
Returned value
- The value converted to the specified data type
T
.
Example
Query:
SELECT toTypeName(accurateCastOrDefault(5, 'UInt8'));
Result:
ββtoTypeName(accurateCastOrDefault(5, 'UInt8'))ββ
β UInt8 β
βββββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT
accurateCastOrDefault(-1, 'UInt8') as uint8,
accurateCastOrDefault(-1, 'UInt8', 5) as uint8_default,
accurateCastOrDefault(128, 'Int8') as int8,
accurateCastOrDefault(128, 'Int8', 5) as int8_default,
accurateCastOrDefault('Test', 'FixedString(2)') as fixed_string,
accurateCastOrDefault('Test', 'FixedString(2)', 'Te') as fixed_string_default;
Result:
ββuint8ββ¬βuint8_defaultββ¬βint8ββ¬βint8_defaultββ¬βfixed_stringββ¬βfixed_string_defaultββ
β 0 β 5 β 0 β 5 β β Te β
βββββββββ΄ββββββββββββββββ΄βββββββ΄βββββββββββββββ΄βββββββββββββββ΄βββββββββββββββββββββββ
toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second)β
Converts a Number type argument to an Interval data type.
Syntax
toIntervalSecond(number)
toIntervalMinute(number)
toIntervalHour(number)
toIntervalDay(number)
toIntervalWeek(number)
toIntervalMonth(number)
toIntervalQuarter(number)
toIntervalYear(number)
Arguments
number
β Duration of interval. Positive integer number.
Returned values
- The value in
Interval
data type.
Example
Query:
WITH
toDate('2019-01-01') AS date,
INTERVAL 1 WEEK AS interval_week,
toIntervalWeek(1) AS interval_to_week
SELECT
date + interval_week,
date + interval_to_week;
Result:
ββplus(date, interval_week)ββ¬βplus(date, interval_to_week)ββ
β 2019-01-08 β 2019-01-08 β
βββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββ
parseDateTimeBestEffortβ
parseDateTime32BestEffortβ
Converts a date and time in the String representation to DateTime data type.
The function parses ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, ClickHouseβs and some other date and time formats.
Syntax
parseDateTimeBestEffort(time_string [, time_zone])
Arguments
time_string
β String containing a date and time to convert. String.time_zone
β Time zone. The function parsestime_string
according to the time zone. String.
Supported non-standard formats
- A string containing 9..10 digit unix timestamp.
- A string with a date and a time component:
YYYYMMDDhhmmss
,DD/MM/YYYY hh:mm:ss
,DD-MM-YY hh:mm
,YYYY-MM-DD hh:mm:ss
, etc. - A string with a date, but no time component:
YYYY
,YYYYMM
,YYYY*MM
,DD/MM/YYYY
,DD-MM-YY
etc. - A string with a day and time:
DD
,DD hh
,DD hh:mm
. In this caseYYYY-MM
are substituted as2000-01
. - A string that includes the date and time along with time zone offset information:
YYYY-MM-DD hh:mm:ss Β±h:mm
, etc. For example,2020-12-12 17:36:00 -5:00
.
For all of the formats with separator the function parses months names expressed by their full name or by the first three letters of a month name. Examples: 24/DEC/18
, 24-Dec-18
, 01-September-2018
.
Returned value
time_string
converted to theDateTime
data type.
Examples
Query:
SELECT parseDateTimeBestEffort('12/12/2020 12:12:57')
AS parseDateTimeBestEffort;
Result:
ββparseDateTimeBestEffortββ
β 2020-12-12 12:12:57 β
βββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffort('Sat, 18 Aug 2018 07:22:16 GMT', 'Asia/Istanbul')
AS parseDateTimeBestEffort;
Result:
ββparseDateTimeBestEffortββ
β 2018-08-18 10:22:16 β
βββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffort('1284101485')
AS parseDateTimeBestEffort;
Result:
ββparseDateTimeBestEffortββ
β 2015-07-07 12:04:41 β
βββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffort('2018-12-12 10:12:12')
AS parseDateTimeBestEffort;
Result:
ββparseDateTimeBestEffortββ
β 2018-12-12 10:12:12 β
βββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffort('10 20:19');
Result:
ββparseDateTimeBestEffort('10 20:19')ββ
β 2000-01-10 20:19:00 β
βββββββββββββββββββββββββββββββββββββββ
See Also
parseDateTimeBestEffortUSβ
This function is similar to parseDateTimeBestEffort, the only difference is that this function prefers US date format (MM/DD/YYYY
etc.) in case of ambiguity.
Syntax
parseDateTimeBestEffortUS(time_string [, time_zone])
Arguments
time_string
β String containing a date and time to convert. String.time_zone
β Time zone. The function parsestime_string
according to the time zone. String.
Supported non-standard formats
- A string containing 9..10 digit unix timestamp.
- A string with a date and a time component:
YYYYMMDDhhmmss
,MM/DD/YYYY hh:mm:ss
,MM-DD-YY hh:mm
,YYYY-MM-DD hh:mm:ss
, etc. - A string with a date, but no time component:
YYYY
,YYYYMM
,YYYY*MM
,MM/DD/YYYY
,MM-DD-YY
etc. - A string with a day and time:
DD
,DD hh
,DD hh:mm
. In this case,YYYY-MM
are substituted as2000-01
. - A string that includes the date and time along with time zone offset information:
YYYY-MM-DD hh:mm:ss Β±h:mm
, etc. For example,2020-12-12 17:36:00 -5:00
.
Returned value
time_string
converted to theDateTime
data type.
Examples
Query:
SELECT parseDateTimeBestEffortUS('09/12/2020 12:12:57')
AS parseDateTimeBestEffortUS;
Result:
ββparseDateTimeBestEffortUSββ
β 2020-09-12 12:12:57 β
βββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUS('09-12-2020 12:12:57')
AS parseDateTimeBestEffortUS;
Result:
ββparseDateTimeBestEffortUSββ
β 2020-09-12 12:12:57 β
βββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUS('09.12.2020 12:12:57')
AS parseDateTimeBestEffortUS;
Result:
ββparseDateTimeBestEffortUSββ
β 2020-09-12 12:12:57 β
βββββββββββββββββββββββββββββ
parseDateTimeBestEffortOrNullβ
parseDateTime32BestEffortOrNullβ
Same as for parseDateTimeBestEffort except that it returns NULL
when it encounters a date format that cannot be processed.
parseDateTimeBestEffortOrZeroβ
parseDateTime32BestEffortOrZeroβ
Same as for parseDateTimeBestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.
parseDateTimeBestEffortUSOrNullβ
Same as parseDateTimeBestEffortUS function except that it returns NULL
when it encounters a date format that cannot be processed.
Syntax
parseDateTimeBestEffortUSOrNull(time_string[, time_zone])
Parameters
time_string
β String containing a date or date with time to convert. The date must be in the US date format (MM/DD/YYYY
, etc). String.time_zone
β Timezone. The function parsestime_string
according to the timezone. Optional. String.
Supported non-standard formats
- A string containing 9..10 digit unix timestamp.
- A string with a date and a time components:
YYYYMMDDhhmmss
,MM/DD/YYYY hh:mm:ss
,MM-DD-YY hh:mm
,YYYY-MM-DD hh:mm:ss
, etc. - A string with a date, but no time component:
YYYY
,YYYYMM
,YYYY*MM
,MM/DD/YYYY
,MM-DD-YY
, etc. - A string with a day and time:
DD
,DD hh
,DD hh:mm
. In this case,YYYY-MM
are substituted with2000-01
. - A string that includes date and time along with timezone offset information:
YYYY-MM-DD hh:mm:ss Β±h:mm
, etc. For example,2020-12-12 17:36:00 -5:00
.
Returned values
time_string
converted to the DateTime data type.NULL
if the input string cannot be converted to theDateTime
data type.
Examples
Query:
SELECT parseDateTimeBestEffortUSOrNull('02/10/2021 21:12:57') AS parseDateTimeBestEffortUSOrNull;
Result:
ββparseDateTimeBestEffortUSOrNullββ
β 2021-02-10 21:12:57 β
βββββββββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUSOrNull('02-10-2021 21:12:57 GMT', 'Asia/Istanbul') AS parseDateTimeBestEffortUSOrNull;
Result:
ββparseDateTimeBestEffortUSOrNullββ
β 2021-02-11 00:12:57 β
βββββββββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUSOrNull('02.10.2021') AS parseDateTimeBestEffortUSOrNull;
Result:
ββparseDateTimeBestEffortUSOrNullββ
β 2021-02-10 00:00:00 β
βββββββββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUSOrNull('10.2021') AS parseDateTimeBestEffortUSOrNull;
Result:
ββparseDateTimeBestEffortUSOrNullββ
β α΄Ία΅α΄Έα΄Έ β
βββββββββββββββββββββββββββββββββββ
parseDateTimeBestEffortUSOrZeroβ
Same as parseDateTimeBestEffortUS function except that it returns zero date (1970-01-01
) or zero date with time (1970-01-01 00:00:00
) when it encounters a date format that cannot be processed.
Syntax
parseDateTimeBestEffortUSOrZero(time_string[, time_zone])
Parameters
time_string
β String containing a date or date with time to convert. The date must be in the US date format (MM/DD/YYYY
, etc). String.time_zone
β Timezone. The function parsestime_string
according to the timezone. Optional. String.
Supported non-standard formats
- A string containing 9..10 digit unix timestamp.
- A string with a date and a time components:
YYYYMMDDhhmmss
,MM/DD/YYYY hh:mm:ss
,MM-DD-YY hh:mm
,YYYY-MM-DD hh:mm:ss
, etc. - A string with a date, but no time component:
YYYY
,YYYYMM
,YYYY*MM
,MM/DD/YYYY
,MM-DD-YY
, etc. - A string with a day and time:
DD
,DD hh
,DD hh:mm
. In this case,YYYY-MM
are substituted with2000-01
. - A string that includes date and time along with timezone offset information:
YYYY-MM-DD hh:mm:ss Β±h:mm
, etc. For example,2020-12-12 17:36:00 -5:00
.
Returned values
time_string
converted to the DateTime data type.- Zero date or zero date with time if the input string cannot be converted to the
DateTime
data type.
Examples
Query:
SELECT parseDateTimeBestEffortUSOrZero('02/10/2021 21:12:57') AS parseDateTimeBestEffortUSOrZero;
Result:
ββparseDateTimeBestEffortUSOrZeroββ
β 2021-02-10 21:12:57 β
βββββββββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUSOrZero('02-10-2021 21:12:57 GMT', 'Asia/Istanbul') AS parseDateTimeBestEffortUSOrZero;
Result:
ββparseDateTimeBestEffortUSOrZeroββ
β 2021-02-11 00:12:57 β
βββββββββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUSOrZero('02.10.2021') AS parseDateTimeBestEffortUSOrZero;
Result:
ββparseDateTimeBestEffortUSOrZeroββ
β 2021-02-10 00:00:00 β
βββββββββββββββββββββββββββββββββββ
Query:
SELECT parseDateTimeBestEffortUSOrZero('02.2021') AS parseDateTimeBestEffortUSOrZero;
Result:
ββparseDateTimeBestEffortUSOrZeroββ
β 1970-01-01 00:00:00 β
βββββββββββββββββββββββββββββββββββ
parseDateTime64BestEffortβ
Same as parseDateTimeBestEffort function but also parse milliseconds and microseconds and returns DateTime data type.
Syntax
parseDateTime64BestEffort(time_string [, precision [, time_zone]])
Parameters
time_string
β String containing a date or date with time to convert. String.precision
β Required precision.3
β for milliseconds,6
β for microseconds. Default β3
. Optional. UInt8.time_zone
β Timezone. The function parsestime_string
according to the timezone. Optional. String.
Returned value
time_string
converted to the DateTime data type.
Examples
Query:
SELECT parseDateTime64BestEffort('2021-01-01') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346') AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346',6) AS a, toTypeName(a) AS t
UNION ALL
SELECT parseDateTime64BestEffort('2021-01-01 01:01:00.12346',3,'Asia/Istanbul') AS a, toTypeName(a) AS t
FORMAT PrettyCompactMonoBlock;
Result:
βββββββββββββββββββββββββββaββ¬βtβββββββββββββββββββββββββββββββ
β 2021-01-01 01:01:00.123000 β DateTime64(3) β
β 2021-01-01 00:00:00.000000 β DateTime64(3) β
β 2021-01-01 01:01:00.123460 β DateTime64(6) β
β 2020-12-31 22:01:00.123000 β DateTime64(3, 'Asia/Istanbul') β
ββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββ
parseDateTime64BestEffortOrNullβ
Same as for parseDateTime64BestEffort except that it returns NULL
when it encounters a date format that cannot be processed.
parseDateTime64BestEffortOrZeroβ
Same as for parseDateTime64BestEffort except that it returns zero date or zero date time when it encounters a date format that cannot be processed.
toLowCardinalityβ
Converts input parameter to the LowCardianlity version of same data type.
To convert data from the LowCardinality
data type use the CAST function. For example, CAST(x as String)
.
Syntax
toLowCardinality(expr)
Arguments
expr
β Expression resulting in one of the supported data types.
Returned values
- Result of
expr
.
Type: LowCardinality(expr_result_type)
Example
Query:
SELECT toLowCardinality('1');
Result:
ββtoLowCardinality('1')ββ
β 1 β
βββββββββββββββββββββββββ
toUnixTimestamp64Milliβ
toUnixTimestamp64Microβ
toUnixTimestamp64Nanoβ
Converts a DateTime64
to a Int64
value with fixed sub-second precision. Input value is scaled up or down appropriately depending on it precision.
note
The output value is a timestamp in UTC, not in the timezone of DateTime64
.
Syntax
toUnixTimestamp64Milli(value)
Arguments
value
β DateTime64 value with any precision.
Returned value
value
converted to theInt64
data type.
Examples
Query:
WITH toDateTime64('2019-09-16 19:20:12.345678910', 6) AS dt64
SELECT toUnixTimestamp64Milli(dt64);
Result:
ββtoUnixTimestamp64Milli(dt64)ββ
β 1568650812345 β
ββββββββββββββββββββββββββββββββ
Query:
WITH toDateTime64('2019-09-16 19:20:12.345678910', 6) AS dt64
SELECT toUnixTimestamp64Nano(dt64);
Result:
ββtoUnixTimestamp64Nano(dt64)ββ
β 1568650812345678000 β
βββββββββββββββββββββββββββββββ
fromUnixTimestamp64Milliβ
fromUnixTimestamp64Microβ
fromUnixTimestamp64Nanoβ
Converts an Int64
to a DateTime64
value with fixed sub-second precision and optional timezone. Input value is scaled up or down appropriately depending on itβs precision. Please note that input value is treated as UTC timestamp, not timestamp at given (or implicit) timezone.
Syntax
fromUnixTimestamp64Milli(value [, ti])
Arguments
value
βInt64
value with any precision.timezone
βString
(optional) timezone name of the result.
Returned value
value
converted to theDateTime64
data type.
Example
Query:
WITH CAST(1234567891011, 'Int64') AS i64
SELECT fromUnixTimestamp64Milli(i64, 'UTC');
Result:
ββfromUnixTimestamp64Milli(i64, 'UTC')ββ
β 2009-02-13 23:31:31.011 β
ββββββββββββββββββββββββββββββββββββββββ
formatRowβ
Converts arbitrary expressions into a string via given format.
Syntax
formatRow(format, x, y, ...)
Arguments
Returned value
- A formatted string (for text formats it's usually terminated with the new line character).
Example
Query:
SELECT formatRow('CSV', number, 'good')
FROM numbers(3);
Result:
ββformatRow('CSV', number, 'good')ββ
β 0,"good"
β
β 1,"good"
β
β 2,"good"
β
ββββββββββββββββββββββββββββββββββββ
formatRowNoNewlineβ
Converts arbitrary expressions into a string via given format. The function trims the last \n
if any.
Syntax
formatRowNoNewline(format, x, y, ...)
Arguments
Returned value
- A formatted string.
Example
Query:
SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3);
Result:
ββformatRowNoNewline('CSV', number, 'good')ββ
β 0,"good" β
β 1,"good" β
β 2,"good" β
βββββββββββββββββββββββββββββββββββββββββββββ
snowflakeToDateTimeβ
Extracts time from Snowflake ID as DateTime format.
Syntax
snowflakeToDateTime(value [, time_zone])
Parameters
value
β Snowflake ID. Int64.time_zone
β Timezone. The function parsestime_string
according to the timezone. Optional. String.
Returned value
- Input value converted to the DateTime data type.
Example
Query:
SELECT snowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC');
Result:
ββsnowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC')ββ
β 2021-08-15 10:57:56 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
snowflakeToDateTime64β
Extracts time from Snowflake ID as DateTime64 format.
Syntax
snowflakeToDateTime64(value [, time_zone])
Parameters
value
β Snowflake ID. Int64.time_zone
β Timezone. The function parsestime_string
according to the timezone. Optional. String.
Returned value
- Input value converted to the DateTime64 data type.
Example
Query:
SELECT snowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC');
Result:
ββsnowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC')ββ
β 2021-08-15 10:58:19.841 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
dateTimeToSnowflakeβ
Converts DateTime value to the first Snowflake ID at the giving time.
Syntax
dateTimeToSnowflake(value)
Parameters
value
β Date and time. DateTime.
Returned value
- Input value converted to the Int64 data type as the first Snowflake ID at that time.
Example
Query:
WITH toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai') AS dt SELECT dateTimeToSnowflake(dt);
Result:
ββdateTimeToSnowflake(dt)ββ
β 1426860702823350272 β
βββββββββββββββββββββββββββ
dateTime64ToSnowflakeβ
Convert DateTime64 to the first Snowflake ID at the giving time.
Syntax
dateTime64ToSnowflake(value)
Parameters
value
β Date and time. DateTime64.
Returned value
- Input value converted to the Int64 data type as the first Snowflake ID at that time.
Example
Query:
WITH toDateTime64('2021-08-15 18:57:56.492', 3, 'Asia/Shanghai') AS dt64 SELECT dateTime64ToSnowflake(dt64);
Result:
ββdateTime64ToSnowflake(dt64)ββ
β 1426860704886947840 β
βββββββββββββββββββββββββββββββ