Skip to main content

Type Conversion Functions

Common Issues with Data Conversion​

ClickHouse generally uses the same behavior as C++ programs.

to<type> functions and cast behave differently in some cases, for example in case of LowCardinality: cast removes LowCardinality trait to<type> functions don't. The same with Nullable, this behaviour is not compatible with SQL standard, and it can be changed using cast_keep_nullable setting.

note

Be aware of potential data loss if values of a datatype are converted to a smaller datatype (for example from Int64 to Int32) or between incompatible datatypes (for example from String to Int). Make sure to check carefully if the result is as expected.

Example:

SELECT
    toTypeName(toLowCardinality('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type

┌─source_type────────────┬─to_type_result_type────┬─cast_result_type─┐
│ LowCardinality(String) │ LowCardinality(String) │ String           │
└────────────────────────┴────────────────────────┴──────────────────┘

SELECT
    toTypeName(toNullable('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type

┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String)    │ String           │
└──────────────────┴─────────────────────┴──────────────────┘

SELECT
    toTypeName(toNullable('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type
SETTINGS cast_keep_nullable = 1

┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String)    │ Nullable(String) │
└──────────────────┴─────────────────────┴──────────────────┘

toInt(8|16|32|64|128|256)​

Converts an input value to a value the Int data type. This function family includes:

  • toInt8(expr) — Converts to a value of data type Int8.
  • toInt16(expr) — Converts to a value of data type Int16.
  • toInt32(expr) — Converts to a value of data type Int32.
  • toInt64(expr) — Converts to a value of data type Int64.
  • toInt128(expr) — Converts to a value of data type Int128.
  • toInt256(expr) — Converts to a value of data type Int256.

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 conversions 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​

Takes an argument of type String and tries to parse it into an Int (8 | 16 | 32 | 64 | 128 | 256). If unsuccessful, 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 unsuccessful, 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 unsuccessful, 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) — Converts to a value of data type UInt8.
  • toUInt16(expr) — Converts to a value of data type UInt16.
  • toUInt32(expr) — Converts to a value of data type UInt32.
  • toUInt64(expr) — Converts to a value of data type UInt64.
  • toUInt256(expr) — Converts to a value of data type UInt256.

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 arguments 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 conversions 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​

Converts the argument to Date data type.

If the argument is DateTime or DateTime64, it truncates it and leaves the date component of the DateTime:

SELECT
    now() AS x,
    toDate(x)
┌───────────────────x─┬─toDate(now())─┐
│ 2022-12-30 13:44:17 │    2022-12-30 │
└─────────────────────┴───────────────┘

If the argument is a String, it is parsed as Date or DateTime. If it was parsed as DateTime, the date component is being used:

SELECT
    toDate('2022-12-30') AS x,
    toTypeName(x)
┌──────────x─┬─toTypeName(toDate('2022-12-30'))─┐
│ 2022-12-30 │ Date                             │
└────────────┴──────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.
SELECT
    toDate('2022-12-30 01:02:03') AS x,
    toTypeName(x)
┌──────────x─┬─toTypeName(toDate('2022-12-30 01:02:03'))─┐
│ 2022-12-30 │ Date                                      │
└────────────┴───────────────────────────────────────────┘

If the argument is a number and looks like a UNIX timestamp (is greater than 65535), it is interpreted as a DateTime, then truncated to Date in the current timezone. The timezone argument can be specified as a second argument of the function. The truncation to Date depends on the timezone:

SELECT
    now() AS current_time,
    toUnixTimestamp(current_time) AS ts,
    toDateTime(ts) AS time_Amsterdam,
    toDateTime(ts, 'Pacific/Apia') AS time_Samoa,
    toDate(time_Amsterdam) AS date_Amsterdam,
    toDate(time_Samoa) AS date_Samoa,
    toDate(ts) AS date_Amsterdam_2,
    toDate(ts, 'Pacific/Apia') AS date_Samoa_2
Row 1:
──────
current_time:     2022-12-30 13:51:54
ts:               1672404714
time_Amsterdam:   2022-12-30 13:51:54
time_Samoa:       2022-12-31 01:51:54
date_Amsterdam:   2022-12-30
date_Samoa:       2022-12-31
date_Amsterdam_2: 2022-12-30
date_Samoa_2:     2022-12-31

The example above demonstrates how the same UNIX timestamp can be interpreted as different dates in different time zones.

If the argument is a number and it is smaller than 65536, it is interpreted as the number of days since 1970-01-01 (the first UNIX day) and converted to Date. It corresponds to the internal numeric representation of the Date data type. Example:

SELECT toDate(12345)
┌─toDate(12345)─┐
│    2003-10-20 │
└───────────────┘

This conversion does not depend on timezones.

If the argument does not fit in the range of the Date type, it results in an implementation-defined behavior, that can saturate to the maximum supported date or overflow:

SELECT toDate(10000000000.)
┌─toDate(10000000000.)─┐
│           2106-02-07 │
└──────────────────────┘

The function toDate can be also written in alternative forms:

SELECT
    now() AS time,
    toDate(time),
    DATE(time),
    CAST(time, 'Date')
┌────────────────time─┬─toDate(now())─┬─DATE(now())─┬─CAST(now(), 'Date')─┐
│ 2022-12-30 13:54:58 │    2022-12-30 │  2022-12-30 │          2022-12-30 │
└─────────────────────┴───────────────┴─────────────┴─────────────────────┘

toDateOrZero​

The same as toDate but returns lower boundary of Date if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateOrZero('2022-12-30'), toDateOrZero('');

Result:

┌─toDateOrZero('2022-12-30')─┬─toDateOrZero('')─┐
│                 2022-12-30 │       1970-01-01 │
└────────────────────────────┴──────────────────┘

toDateOrNull​

The same as toDate but returns NULL if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateOrNull('2022-12-30'), toDateOrNull('');

Result:

┌─toDateOrNull('2022-12-30')─┬─toDateOrNull('')─┐
│                 2022-12-30 │             ᴺᵁᴸᴸ │
└────────────────────────────┴──────────────────┘

toDateOrDefault​

Like toDate but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of Date.

Syntax

toDateOrDefault(expr [, default_value])

Example

Query:

SELECT toDateOrDefault('2022-12-30'), toDateOrDefault('', '2023-01-01'::Date);

Result:

┌─toDateOrDefault('2022-12-30')─┬─toDateOrDefault('', CAST('2023-01-01', 'Date'))─┐
│                    2022-12-30 │                                      2023-01-01 │
└───────────────────────────────┴─────────────────────────────────────────────────┘

toDateTime​

Converts an input value to DateTime.

Syntax

toDateTime(expr[, time_zone ])

Arguments

note

If expr is a number, it is interpreted as the number of seconds since the beginning of the Unix Epoch (as Unix timestamp).
If expr is a String, it may be interpreted as a Unix timestamp or as a string representation of date / date with time.
Thus, parsing of short numbers' string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string '1999' may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.

Returned value

Example

Query:

SELECT toDateTime('2022-12-30 13:44:17'), toDateTime(1685457500, 'UTC');

Result:

┌─toDateTime('2022-12-30 13:44:17')─┬─toDateTime(1685457500, 'UTC')─┐
│               2022-12-30 13:44:17 │           2023-05-30 14:38:20 │
└───────────────────────────────────┴───────────────────────────────┘

toDateTimeOrZero​

The same as toDateTime but returns lower boundary of DateTime if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateTimeOrZero('2022-12-30 13:44:17'), toDateTimeOrZero('');

Result:

┌─toDateTimeOrZero('2022-12-30 13:44:17')─┬─toDateTimeOrZero('')─┐
│                     2022-12-30 13:44:17 │  1970-01-01 00:00:00 │
└─────────────────────────────────────────┴──────────────────────┘

toDateTimeOrNull​

The same as toDateTime but returns NULL if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateTimeOrNull('2022-12-30 13:44:17'), toDateTimeOrNull('');

Result:

┌─toDateTimeOrNull('2022-12-30 13:44:17')─┬─toDateTimeOrNull('')─┐
│                     2022-12-30 13:44:17 │                 ᴺᵁᴸᴸ │
└─────────────────────────────────────────┴──────────────────────┘

toDateTimeOrDefault​

Like toDateTime but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of DateTime.

Syntax

toDateTimeOrDefault(expr [, time_zone [, default_value]])

Example

Query:

SELECT toDateTimeOrDefault('2022-12-30 13:44:17'), toDateTimeOrDefault('', 'UTC', '2023-01-01'::DateTime('UTC'));

Result:

┌─toDateTimeOrDefault('2022-12-30 13:44:17')─┬─toDateTimeOrDefault('', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))─┐
│                        2022-12-30 13:44:17 │                                                     2023-01-01 00:00:00 │
└────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘

toDate32​

Converts the argument to the Date32 data type. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument has Date type, it's borders are taken into account.

Syntax

toDate32(expr)

Arguments

Returned value

Example

  1. 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                             │
└────────────┴────────────────────────────────────┘
  1. The value is outside the range:
SELECT toDate32('1899-01-01') AS value, toTypeName(value);
┌──────value─┬─toTypeName(toDate32('1899-01-01'))─┐
│ 1900-01-01 │ Date32                             │
└────────────┴────────────────────────────────────┘
  1. With Date argument:
SELECT toDate32(toDate('1899-01-01')) AS value, toTypeName(value);
┌──────value─┬─toTypeName(toDate32(toDate('1899-01-01')))─┐
│ 1970-01-01 │ Date32                                     │
└────────────┴────────────────────────────────────────────┘

toDate32OrZero​

The same as toDate32 but returns the min value of Date32 if an invalid argument is received.

Example

Query:

SELECT toDate32OrZero('1899-01-01'), toDate32OrZero('');

Result:

┌─toDate32OrZero('1899-01-01')─┬─toDate32OrZero('')─┐
│                   1900-01-01 │         1900-01-01 │
└──────────────────────────────┴────────────────────┘

toDate32OrNull​

The same as toDate32 but returns NULL if an 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, toDate32OrDefault returns the lower border value supported by Date32. If the argument has Date type, it's borders are taken into account. Returns default value if an 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 │
└─────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘

toDateTime64​

Converts the argument to the DateTime64 data type.

Syntax

toDateTime64(expr, scale, [timezone])

Arguments

  • expr — The value. String, UInt32, Float or DateTime.
  • scale - Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].
  • timezone - Time zone of the specified datetime64 object.

Returned value

  • A calendar date and time of day, with sub-second precision. DateTime64.

Example

  1. The value is within the range:
SELECT toDateTime64('1955-01-01 00:00:00.000', 3) AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64('1955-01-01 00:00:00.000', 3))─┐
│ 1955-01-01 00:00:00.000 │ DateTime64(3)                                          │
└─────────────────────────┴────────────────────────────────────────────────────────┘
  1. As decimal with precision:
SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800., 3))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3)                            │
└─────────────────────────┴──────────────────────────────────────────┘

Without the decimal point the value is still treated as Unix Timestamp in seconds:

SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘
  1. With timezone:
SELECT toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul')                                      │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘

toDateTime64OrZero​

toDateTime64OrNull​

toDateTime64OrDefault​

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 includes:

  • toDecimal32OrNull(expr, S) — Results in Nullable(Decimal32(S)) data type.
  • toDecimal64OrNull(expr, S) — Results in Nullable(Decimal64(S)) data type.
  • toDecimal128OrNull(expr, S) — Results in Nullable(Decimal128(S)) data type.
  • toDecimal256OrNull(expr, S) — Results in Nullable(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 than S 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 includes:

  • toDecimal32OrDefault(expr, S) — Results in Decimal32(S) data type.
  • toDecimal64OrDefault(expr, S) — Results in Decimal64(S) data type.
  • toDecimal128OrDefault(expr, S) — Results in Decimal128(S) data type.
  • toDecimal256OrDefault(expr, S) — Results in Decimal256(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 than S 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 includes:

  • toDecimal32OrZero( expr, S) — Results in Decimal32(S) data type.
  • toDecimal64OrZero( expr, S) — Results in Decimal64(S) data type.
  • toDecimal128OrZero( expr, S) — Results in Decimal128(S) data type.
  • toDecimal256OrZero( expr, S) — Results in Decimal256(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 than S 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 a 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 ts,
    time_zone,
    toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10

Result:

┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam  │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra    │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan  │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens     │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast    │ 2023-09-08 20:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belgrade   │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Berlin     │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bratislava │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Brussels   │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bucharest  │ 2023-09-08 22:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘

Also see the toUnixTimestamp function.

toFixedString(s, N)​

Converts a String type argument to a FixedString(N) type (a string of fixed length N). 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   │
└────────────┴───────┘

toDecimalString​

Converts a numeric value to String with the number of fractional digits in the output specified by the user.

Syntax

toDecimalString(number, scale)

Arguments

  • number — Value to be represented as String, Int, UInt, Float, Decimal,
  • scale — Number of fractional digits, UInt8.
    • Maximum scale for Decimal and Int, UInt types is 77 (it is the maximum possible number of significant digits for Decimal),
    • Maximum scale for Float is 60.

Returned value

  • Input value represented as String with given number of fractional digits (scale). The number is rounded up or down according to common arithmetic in case requested scale is smaller than original number's scale.

Example

Query:

SELECT toDecimalString(CAST('64.32', 'Float64'), 5);

Result:

┌toDecimalString(CAST('64.32', 'Float64'), 5)─┐
│ 64.32000                                    │
└─────────────────────────────────────────────┘

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

reinterpretAsUInt8(x)

Parameters

Returned value

  • Reinterpreted value x as UInt8. UInt8.

Example

Query:

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt8(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ UInt8           │
└───┴───────────────┴─────┴─────────────────┘

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

reinterpretAsUInt16(x)

Parameters

Returned value

  • Reinterpreted value x as UInt16. UInt16.

Example

Query:

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt16(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ UInt16          │
└───┴───────────────┴─────┴─────────────────┘

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

reinterpretAsUInt32(x)

Parameters

Returned value

  • Reinterpreted value x as UInt32. UInt32.

Example

Query:

SELECT
    toUInt16(257) AS x,
    toTypeName(x),
    reinterpretAsUInt32(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt16        │ 257 │ 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

reinterpretAsUInt64(x)

Parameters

Returned value

  • Reinterpreted value x as UInt64. UInt64.

Example

Query:

SELECT
    toUInt32(257) AS x,
    toTypeName(x),
    reinterpretAsUInt64(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt32        │ 257 │ UInt64          │
└─────┴───────────────┴─────┴─────────────────┘

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

reinterpretAsUInt128(x)

Parameters

Returned value

  • Reinterpreted value x as UInt128. UInt128.

Example

Query:

SELECT
    toUInt64(257) AS x,
    toTypeName(x),
    reinterpretAsUInt128(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt64        │ 257 │ 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

reinterpretAsUInt256(x)

Parameters

Returned value

  • Reinterpreted value x as UInt256. UInt256.

Example

Query:

SELECT
    toUInt128(257) AS x,
    toTypeName(x),
    reinterpretAsUInt256(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt128       │ 257 │ UInt256         │
└─────┴───────────────┴─────┴─────────────────┘

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

reinterpretAsInt8(x)

Parameters

Returned value

  • Reinterpreted value x as Int8. Int8.

Example

Query:

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt8(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ Int8            │
└───┴───────────────┴─────┴─────────────────┘

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

reinterpretAsInt16(x)

Parameters

Returned value

  • Reinterpreted value x as Int16. Int16.

Example

Query:

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt16(x) AS res,
    toTypeName(res);

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ Int16           │
└───┴───────────────┴─────┴─────────────────┘

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

reinterpretAsInt32(x)

Parameters

Returned value

  • Reinterpreted value x as Int32. Int32.

Example

Query:

SELECT
    toInt16(257) AS x,
    toTypeName(x),
    reinterpretAsInt32(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int16         │ 257 │ 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

reinterpretAsInt64(x)

Parameters

Returned value

  • Reinterpreted value x as Int64. Int64.

Example

Query:

SELECT
    toInt32(257) AS x,
    toTypeName(x),
    reinterpretAsInt64(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int32         │ 257 │ Int64           │
└─────┴───────────────┴─────┴─────────────────┘

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

reinterpretAsInt128(x)

Parameters

Returned value

  • Reinterpreted value x as Int128. Int128.

Example

Query:

SELECT
    toInt64(257) AS x,
    toTypeName(x),
    reinterpretAsInt128(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int64         │ 257 │ 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

reinterpretAsInt256(x)

Parameters

Returned value

  • Reinterpreted value x as Int256. Int256.

Example

Query:

SELECT
    toInt128(257) AS x,
    toTypeName(x),
    reinterpretAsInt256(x) AS res,
    toTypeName(res);

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int128        │ 257 │ Int256          │
└─────┴───────────────┴─────┴─────────────────┘

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

reinterpretAsFloat32(x)

Parameters

Returned value

  • Reinterpreted value x as Float32. Float32.

Example

Query:

SELECT reinterpretAsUInt32(toFloat32(0.2)) as x, reinterpretAsFloat32(x);

Result:

┌──────────x─┬─reinterpretAsFloat32(x)─┐
│ 1045220557 │                     0.2 │
└────────────┴─────────────────────────┘

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

reinterpretAsFloat64(x)

Parameters

Returned value

  • Reinterpreted value x as Float64. Float64.

Example

Query:

SELECT reinterpretAsUInt64(toFloat64(0.2)) as x, reinterpretAsFloat64(x);

Result:

┌───────────────────x─┬─reinterpretAsFloat64(x)─┐
│ 4596373779694328218 │                     0.2 │
└─────────────────────┴─────────────────────────┘

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

reinterpretAsDate(x)

Parameters

Returned value

Implementation details

note

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:

SELECT reinterpretAsDate(65), reinterpretAsDate('A');

Result:

┌─reinterpretAsDate(65)─┬─reinterpretAsDate('A')─┐
│            1970-03-07 │             1970-03-07 │
└───────────────────────┴────────────────────────┘

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). Returns a date with time interpreted as the number of seconds since the beginning of the Unix Epoch.

Syntax

reinterpretAsDateTime(x)

Parameters

Returned value

Implementation details

note

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:

SELECT reinterpretAsDateTime(65), reinterpretAsDateTime('A');

Result:

┌─reinterpretAsDateTime(65)─┬─reinterpretAsDateTime('A')─┐
│       1970-01-01 01:01:05 │        1970-01-01 01:01:05 │
└───────────────────────────┴────────────────────────────┘

reinterpretAsString​

This function accepts a number, 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.

Syntax

reinterpretAsString(x)

Parameters

Returned value

  • String containing bytes representing x. String.

Example

Query:

SELECT 
    reinterpretAsString(toDateTime('1970-01-01 01:01:05')), 
    reinterpretAsString(toDate('1970-03-07'));

Result:

┌─reinterpretAsString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsString(toDate('1970-03-07'))─┐
│ A                                                      │ A                                         │
└────────────────────────────────────────────────────────┴───────────────────────────────────────────┘

reinterpretAsFixedString​

This function accepts a number, 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.

Syntax

reinterpretAsFixedString(x)

Parameters

Returned value

  • Fixed string containing bytes representing x. FixedString.

Example

Query:

SELECT 
    reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05')), 
    reinterpretAsFixedString(toDate('1970-03-07'));

Result:

┌─reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsFixedString(toDate('1970-03-07'))─┐
│ A                                                           │ A                                              │
└─────────────────────────────────────────────────────────────┴────────────────────────────────────────────────┘

reinterpretAsUUID​

note

In addition to the UUID functions listed here, there is dedicated UUID function documentation.

Accepts a 16 byte string and returns a 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 is longer than 16 bytes, the extra bytes at the end are ignored.

Syntax

reinterpretAsUUID(fixed_string)

Arguments

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

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)

Arguments

  • 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)

Arguments

  • 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 │
└───────────────────────────┴──────────────────────────────┘

parseDateTime​

Converts a String to DateTime according to a MySQL format string.

This function is the opposite operation of function formatDateTime.

Syntax

parseDateTime(str, format[, timezone])

Arguments

  • str — the String to be parsed
  • format — the format string
  • timezone — Timezone. Optional.

Returned value(s)

Returns DateTime values parsed from input string according to a MySQL style format string.

Supported format specifiers

All format specifiers listed in formatDateTime except:

  • %Q: Quarter (1-4)

Example

SELECT parseDateTime('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')

┌─parseDateTime('2021-01-04+23:00:00', '%Y-%m-%d+%H:%i:%s')─┐
│                                       2021-01-04 23:00:00 │
└───────────────────────────────────────────────────────────┘

Alias: TO_TIMESTAMP.

parseDateTimeOrZero​

Same as for parseDateTime except that it returns zero date when it encounters a date format that cannot be processed.

parseDateTimeOrNull​

Same as for parseDateTime except that it returns NULL when it encounters a date format that cannot be processed.

Alias: str_to_date.

parseDateTimeInJodaSyntax​

Similar to parseDateTime, except that the format string is in Joda instead of MySQL syntax.

This function is the opposite operation of function formatDateTimeInJodaSyntax.

Syntax

parseDateTimeInJodaSyntax(str, format[, timezone])

Arguments

  • str — the String to be parsed
  • format — the format string
  • timezone — Timezone. Optional.

Returned value(s)

Returns DateTime values parsed from input string according to a Joda style format.

Supported format specifiers

All format specifiers listed in formatDateTimeInJoda are supported, except:

  • S: fraction of second
  • z: time zone
  • Z: time zone offset/id

Example

SELECT parseDateTimeInJodaSyntax('2023-02-24 14:53:31', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')

┌─parseDateTimeInJodaSyntax('2023-02-24 14:53:31', 'yyyy-MM-dd HH:mm:ss', 'Europe/Minsk')─┐
│                                                                     2023-02-24 14:53:31 │
└─────────────────────────────────────────────────────────────────────────────────────────┘

parseDateTimeInJodaSyntaxOrZero​

Same as for parseDateTimeInJodaSyntax except that it returns zero date when it encounters a date format that cannot be processed.

parseDateTimeInJodaSyntaxOrNull​

Same as for parseDateTimeInJodaSyntax except that it returns NULL when it encounters a date format that cannot be processed.

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 parses time_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 case MM is substituted by 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.
  • A syslog timestamp: Mmm dd hh:mm:ss. For example, Jun 9 14:20:32.

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. If the year is not specified, it is considered to be equal to the current year. If the resulting DateTime happen to be in the future (even by a second after the current moment), then the current year is substituted by the previous year.

Returned value

  • time_string converted to the DateTime data type.

Examples

Query:

SELECT parseDateTimeBestEffort('23/10/2020 12:12:57')
AS parseDateTimeBestEffort;

Result:

┌─parseDateTimeBestEffort─┐
│     2020-10-23 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-10-23 10:12:12')
AS parseDateTimeBestEffort;

Result:

┌─parseDateTimeBestEffort─┐
│     2018-10-23 10:12:12 │
└─────────────────────────┘

Query:

SELECT toYear(now()) as year, parseDateTimeBestEffort('10 20:19');

Result:

┌─year─┬─parseDateTimeBestEffort('10 20:19')─┐
│ 2023 │                 2023-01-10 20:19:00 │
└──────┴─────────────────────────────────────┘

Query:

WITH
    now() AS ts_now,
    formatDateTime(ts_around, '%b %e %T') AS syslog_arg
SELECT
    ts_now,
    syslog_arg,
    parseDateTimeBestEffort(syslog_arg)
FROM (SELECT arrayJoin([ts_now - 30, ts_now + 30]) AS ts_around);

Result:

┌──────────────ts_now─┬─syslog_arg──────┬─parseDateTimeBestEffort(syslog_arg)─┐
│ 2023-06-30 23:59:30 │ Jun 30 23:59:00 │                 2023-06-30 23:59:00 │
│ 2023-06-30 23:59:30 │ Jul  1 00:00:00 │                 2022-07-01 00:00:00 │
└─────────────────────┴─────────────────┴─────────────────────────────────────┘

See Also

parseDateTimeBestEffortUS​

This function behaves like parseDateTimeBestEffort for ISO date formats, e.g. YYYY-MM-DD hh:mm:ss, and other date formats where the month and date components can be unambiguously extracted, e.g. YYYYMMDDhhmmss, YYYY-MM, DD hh, or YYYY-MM-DD hh:mm:ss ±h:mm. If the month and the date components cannot be unambiguously extracted, e.g. MM/DD/YYYY, MM-DD-YYYY, or MM-DD-YY, it prefers the US date format instead of DD/MM/YYYY, DD-MM-YYYY, or DD-MM-YY. As an exception from the latter, if the month is bigger than 12 and smaller or equal than 31, this function falls back to the behavior of parseDateTimeBestEffort, e.g. 15/08/2020 is parsed as 2020-08-15.

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.

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.

parseDateTime64BestEffort​

Same as parseDateTimeBestEffort function but also parse milliseconds and microseconds and returns DateTime data type.

Syntax

parseDateTime64BestEffort(time_string [, precision [, time_zone]])

Arguments

  • 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 parses time_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') │
└────────────────────────────┴────────────────────────────────┘

parseDateTime64BestEffortUS​

Same as for parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity.

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.

parseDateTime64BestEffortUSOrNull​

Same as for parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns NULL when it encounters a date format that cannot be processed.

parseDateTime64BestEffortUSOrZero​

Same as for parseDateTime64BestEffort, except that this function prefers US date format (MM/DD/YYYY etc.) in case of ambiguity and returns zero date or zero date time when it encounters a date format that cannot be processed.

toLowCardinality​

Converts input parameter to the LowCardinality 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

Returned values

Example

Query:

SELECT toLowCardinality('1');

Result:

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

toUnixTimestamp64Milli​

Converts a DateTime64 to a Int64 value with fixed millisecond precision. The input value is scaled up or down appropriately depending on its 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. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('2009-02-13 23:31:31.011', 3, 'UTC') AS dt64
SELECT toUnixTimestamp64Milli(dt64);

Result:

┌─toUnixTimestamp64Milli(dt64)─┐
│                1234567891011 │
└──────────────────────────────┘

toUnixTimestamp64Micro​

Converts a DateTime64 to a Int64 value with fixed microsecond precision. The input value is scaled up or down appropriately depending on its precision.

note

The output value is a timestamp in UTC, not in the timezone of DateTime64.

Syntax

toUnixTimestamp64Micro(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('1970-01-15 06:56:07.891011', 6, 'UTC') AS dt64
SELECT toUnixTimestamp64Micro(dt64);

Result:

┌─toUnixTimestamp64Micro(dt64)─┐
│                1234567891011 │
└──────────────────────────────┘

toUnixTimestamp64Nano​

Converts a DateTime64 to a Int64 value with fixed nanosecond precision. The input value is scaled up or down appropriately depending on its precision.

note

The output value is a timestamp in UTC, not in the timezone of DateTime64.

Syntax

toUnixTimestamp64Nano(value)

Arguments

  • value — DateTime64 value with any precision. DateTime64.

Returned value

  • value converted to the Int64 data type. Int64.

Example

Query:

WITH toDateTime64('1970-01-01 00:20:34.567891011', 9, 'UTC') AS dt64
SELECT toUnixTimestamp64Nano(dt64);

Result:

┌─toUnixTimestamp64Nano(dt64)─┐
│               1234567891011 │
└─────────────────────────────┘

fromUnixTimestamp64Milli​

Converts an Int64 to a DateTime64 value with fixed millisecond precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Milli(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 3. DateTime64.

Example

Query:

WITH CAST(1234567891011, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Milli(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌───────────────────────x─┬─toTypeName(x)────────┐
│ 2009-02-13 23:31:31.011 │ DateTime64(3, 'UTC') │
└─────────────────────────┴──────────────────────┘

fromUnixTimestamp64Micro​

Converts an Int64 to a DateTime64 value with fixed microsecond precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Micro(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 6. DateTime64.

Example

Query:

WITH CAST(1234567891011, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Micro(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌──────────────────────────x─┬─toTypeName(x)────────┐
│ 1970-01-15 06:56:07.891011 │ DateTime64(6, 'UTC') │
└────────────────────────────┴──────────────────────┘

fromUnixTimestamp64Nano​

Converts an Int64 to a DateTime64 value with fixed nanosecond precision and optional timezone. The input value is scaled up or down appropriately depending on its precision.

note

Please note that input value is treated as a UTC timestamp, not timestamp at the given (or implicit) timezone.

Syntax

fromUnixTimestamp64Nano(value[, timezone])

Arguments

  • value — value with any precision. Int64.
  • timezone — (optional) timezone name of the result. String.

Returned value

  • value converted to DateTime64 with precision 9. DateTime64.

Example

Query:

WITH CAST(1234567891011, 'Int64') AS i64
SELECT
    fromUnixTimestamp64Nano(i64, 'UTC') AS x,
    toTypeName(x);

Result:

┌─────────────────────────────x─┬─toTypeName(x)────────┐
│ 1970-01-01 00:20:34.567891011 │ DateTime64(9, 'UTC') │
└───────────────────────────────┴──────────────────────┘

formatRow​

Converts arbitrary expressions into a string via given format.

Syntax

formatRow(format, x, y, ...)

Arguments

  • format — Text format. For example, CSV, TSV.
  • x,y, ... — Expressions.

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"
                         │
└──────────────────────────────────┘

Note: If format contains suffix/prefix, it will be written in each row.

Example

Query:

SELECT formatRow('CustomSeparated', number, 'good')
FROM numbers(3)
SETTINGS format_custom_result_before_delimiter='<prefix>\n', format_custom_result_after_delimiter='<suffix>'

Result:

┌─formatRow('CustomSeparated', number, 'good')─┐
│ <prefix>
0   good
<suffix>                   │
│ <prefix>
1   good
<suffix>                   │
│ <prefix>
2   good
<suffix>                   │
└──────────────────────────────────────────────┘

Note: Only row-based formats are supported in this function.

formatRowNoNewline​

Converts arbitrary expressions into a string via given format. Differs from formatRow in that this function trims the last \n if any.

Syntax

formatRowNoNewline(format, x, y, ...)

Arguments

  • format — Text format. For example, CSV, TSV.
  • x,y, ... — Expressions.

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"                                  │
└───────────────────────────────────────────┘