Функции преобразования типов
Общие проблемы преобразования чисел
При преобразовании значения из одного типа в другой необходимо помнить, что в общем случае это небезопасная операция, которая может привести к потере данных. Потеря данных может произойти при попытке сконвертировать тип данных значения от большего к меньшему или при конвертировании между различными классами типов данных.
Поведение ClickHouse при конвертировании похоже на поведение C++ программ.
toInt(8|16|32|64|128|256)
Преобразует входное значение к типу Int. Семейство функций включает:
toInt8(expr)— возвращает значение типаInt8.toInt16(expr)— возвращает значение типаInt16.toInt32(expr)— возвращает значение типаInt32.toInt64(expr)— возвращает значение типаInt64.toInt128(expr)— возвращает значение типаInt128.toInt256(expr)— возвращает значение типаInt256.
Параметры
expr— выражение возвращающее число или строку с десятичным представление числа. Бинарное, восьмеричное и шестнадцатеричное представление числа не поддержаны. Ведущие нули обрезаются.
Возвращаемое значение
Целое число типа Int8, Int16, Int32, Int64, Int128 или Int256.
Функции используют округление к нулю, т.е. обрезают дробную часть числа.
Поведение функций для аргументов NaN и Inf не определено. При использовании функций помните о возможных проблемах при преобразовании чисел.
Пример
SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8)
┌─────────toInt64(nan)─┬─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐
│ -9223372036854775808 │ 32 │ 16 │ 8 │
└──────────────────────┴─────────────┴───────────────┴─────────────┘
toInt(8|16|32|64|128|256)OrZero
Принимает аргумент типа String и пытается его распарсить в Int(8|16|32|64|128|256). Если не удалось - возвращает 0.
Пример
select toInt64OrZero('123123'), toInt8OrZero('123qwe123')
┌─toInt64OrZero('123123')─┬─toInt8OrZero('123qwe123')─┐
│ 123123 │ 0 │
└─────────────────────────┴───────────────────────────┘
toInt(8|16|32|64|128|256)OrNull
Принимает аргумент типа String и пытается его распарсить в Int(8|16|32|64|128|256). Если не удалось - возвращает NULL.
Пример
select toInt64OrNull('123123'), toInt8OrNull('123qwe123')
┌─toInt64OrNull('123123')─┬─toInt8OrNull('123qwe123')─┐
│ 123123 │ ᴺᵁᴸᴸ │
└─────────────────────────┴───────────────────────────┘
toUInt(8|16|32|64|256)
Преобраует входное значение к типу UInt. Семейство функций включает:
toUInt8(expr)— возвращает значение типаUInt8.toUInt16(expr)— возвращает значение типаUInt16.toUInt32(expr)— возвращает значение типаUInt32.toUInt64(expr)— возвращает значение типаUInt64.toUInt256(expr)— возвращает значение типаUInt256.
Параметры
expr— выражение возвращающее число или строку с десятичным представление числа. Бинарное, восьмеричное и шестнадцатеричное представление числа не поддержаны. Ведущие нули обрезаются.
Возвращаемое значение
Целое число типа UInt8, UInt16, UInt32, UInt64 или UInt256.
Функции используют округление к нулю, т.е. обрезают дробную часть числа.
Поведение функций для аргументов NaN и Inf не определено. Если передать строку, содержащую отрицательное число, например '-32', ClickHouse генерирует исключение. При использовании функций помните о возможных проблемах при преобразовании чисел.
Пример
SELECT toUInt64(nan), toUInt32(-32), toUInt16('16'), toUInt8(8.8)
┌───────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
toFloat(32|64)
toFloat(32|64)OrZero
toFloat(32|64)OrNull
toDate
toDateOrZero
toDateOrNull
toDateTime
toDateTimeOrZero
toDateTimeOrNull
toDecimal(32|64|128|256)
Преобразует value к типу данных Decimal с точностью S. value может быть числом или строкой. Параметр S (scale) задаёт число десятичных знаков.
toDecimal32(value, S)toDecimal64(value, S)toDecimal128(value, S)toDecimal256(value, S)
toDecimal(32|64|128|256)OrNull
Преобразует входную строку в значение с типом данных Nullable (Decimal (P, S)). Семейство функций включает в себя:
toDecimal32OrNull(expr, S)— Возвращает значение типаNullable(Decimal32(S)).toDecimal64OrNull(expr, S)— Возвращает значение типаNullable(Decimal64(S)).toDecimal128OrNull(expr, S)— Возвращает значение типаNullable(Decimal128(S)).toDecimal256OrNull(expr, S)— Возвращает значение типаNullable(Decimal256(S)).
Эти функции следует использовать вместо функций toDecimal*(), если при ошибке обработки входного значения вы хотите получать NULL вместо исключения.
Параметры
expr— выражение, возвращающее значение типа String. ClickHouse ожидает текстовое представление десятичного числа. Например,'1.111'.S— количество десятичных знаков в результирующем значении.
Возвращаемое значение
Значение типа Nullable(Decimal(P,S)). Значение содержит:
- Число с
Sдесятичными знаками, если ClickHouse распознал число во входной строке. NULL, если ClickHouse не смог распознать число во входной строке или входное число содержит больше чемSдесятичных знаков.
Примеры
SELECT toDecimal32OrNull(toString(-1.111), 5) AS val, toTypeName(val)
┌──────val─┬─toTypeName(toDecimal32OrNull(toString(-1.111), 5))─┐
│ -1.11100 │ Nullable(Decimal(9, 5)) │
└──────────┴────────────────────────────────────────────────────┘
SELECT toDecimal32OrNull(toString(-1.111), 2) AS val, toTypeName(val)
┌──val─┬─toTypeName(toDecimal32OrNull(toString(-1.111), 2))─┐
│ ᴺᵁᴸᴸ │ Nullable(Decimal(9, 2)) │
└──────┴────────────────────────────────────────────────────┘
toDecimal(32|64|128|256)OrZero
Преобразует тип входного значения в Decimal (P, S). Семейство функций включает в себя:
toDecimal32OrZero( expr, S)— возвращает значение типаDecimal32(S).toDecimal64OrZero( expr, S)— возвращает значение типаDecimal64(S).toDecimal128OrZero( expr, S)— возвращает значение типаDecimal128(S).toDecimal256OrZero( expr, S)— возвращает значение типаDecimal256(S).
Эти функции следует использовать вместо функций toDecimal*(), если при ошибке обработки входного значения вы хотите получать 0 вместо исключения.
Параметры
expr— выражение, возвращающее значение типа String. ClickHouse ожидает текстовое представление десятичного числа. Например,'1.111'.S— количество десятичных знаков в результирующем значении.
Возвращаемое значение
Значение типа Nullable(Decimal(P,S)). P равно числовой части имени функции. Например, для функции toDecimal32OrZero, P = 32. Значение содержит:
- Число с
Sдесятичными знаками, если ClickHouse распознал число во входной строке. - 0 c
Sдесятичными знаками, если ClickHouse не смог распознать число во входной строке или входное число содержит больше чемSдесятичных знаков.
Пример
SELECT toDecimal32OrZero(toString(-1.111), 5) AS val, toTypeName(val)
┌──────val─┬─toTypeName(toDecimal32OrZero(toString(-1.111), 5))─┐
│ -1.11100 │ Decimal(9, 5) │
└──────────┴────────────────────────────────────────────────────┘
SELECT toDecimal32OrZero(toString(-1.111), 2) AS val, toTypeName(val)
┌──val─┬─toTypeName(toDecimal32OrZero(toString(-1.111), 2))─┐
│ 0.00 │ Decimal(9, 2) │
└──────┴────────────────────────────────────────────────────┘
toString
Функции преобразования между числами, строками (но не фиксированными строками), датами и датами-с-временем.
Все эти функции принимают один аргумент.
При преобразовании в строку или из строки, производится форматирование или парсинг значения по тем же правилам, что и для формата TabSeparated (и почти всех остальных текстовых форматов). Если распарсить строку не удаётся - кидается исключение и выполнение запроса прерывается.
При преобразовании даты в число или наоборот, дате соответствует число дней от начала unix эпохи.
При преобразовании даты-с-временем в число или наоборот, дате-с-временем соответствует число секунд от начала unix эпохи.
Форматы даты и даты-с-временем для функций toDate/toDateTime определены следующим образом:
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
В качестве исключения, если делается преобразование из числа типа UInt32, Int32, UInt64, Int64 в Date, и если число больше или равно 65536, то число рассматривается как unix timestamp (а не как число дней) и округляется до даты. Это позволяет поддержать распространённый случай, когда пишут toDate(unix_timestamp), что иначе было бы ошибкой и требовало бы написания более громоздкого toDate(toDateTime(unix_timestamp))
Преобразование между датой и датой-с-временем производится естественным образом: добавлением нулевого времени или отбрасыванием времени.
Преобразование между числовыми типами производится по тем же правилам, что и присваивание между разными числовыми типами в C++.
Дополнительно, функция toString от аргумента типа DateTime может принимать второй аргумент String - имя тайм-зоны. Пример: Asia/Yekaterinburg В этом случае, форматирование времени производится согласно указанной тайм-зоне.
SELECT
now() AS now_local,
toString(now(), 'Asia/Yekaterinburg') AS now_yekat
┌───────────now_local─┬─now_yekat───────────┐
│ 2016-06-15 00:11:21 │ 2016-06-15 02:11:21 │
└─────────────────────┴─────────────────────┘
Также смотрите функцию toUnixTimestamp.
toFixedString(s, N)
Преобразует аргумент типа String в тип FixedString(N) (строку фиксированной длины N). N должно быть константой.
Если строка имеет меньше байт, чем N, то она дополняется нулевыми байтами справа. Если строка имеет больше байт, чем N - кидается исключение.
toStringCutToZero(s)
Принимает аргумент типа String или FixedString. Возвращает String, вырезая содержимое строки до первого найденного нулевого байта.
Пример:
SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut
┌─s─────────────┬─s_cut─┐
│ foo\0\0\0\0\0 │ foo │
└───────────────┴───────┘
SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut
┌─s──────────┬─s_cut─┐
│ foo\0bar\0 │ foo │
└────────────┴───────┘
reinterpretAsUInt(8|16|32|64)
reinterpretAsInt(8|16|32|64)
reinterpretAsFloat(32|64)
reinterpretAsDate
reinterpretAsDateTime
Функции принимают строку и интерпретируют байты, расположенные в начале строки, как число в host order (little endian). Если строка имеет недостаточную длину, то функции работают так, как будто строка дополнена необходимым количеством нулевых байт. Если строка длиннее, чем нужно, то лишние байты игнорируются. Дата интерпретируется, как число дней с начала unix-эпохи, а дата-с-временем - как число секунд с начала unix-эпохи.
reinterpretAsString
Функция принимает число или дату или дату-с-временем и возвращает строку, содержащую байты, представляющие соответствующее значение в host order (little endian). При этом, отбрасываются нулевые байты с конца. Например, значение 255 типа UInt32 будет строкой длины 1 байт.
reinterpretAsUUID
Функция принимает шестнадцатибайтную строку и интерпретирует ее байты в network order (big-endian). Если строка имеет недостаточную длину, то функция работает так, как будто строка дополнена необходимым количетсвом нулевых байт с конца. Если строка длиннее, чем шестнадцать байт, то игнорируются лишние байты с конца.
Синтаксис
reinterpretAsUUID(fixed_string)
Параметры
fixed_string— cтрока с big-endian порядком байтов. FixedString.
Возвращаемое значение
- Значение типа UUID.
Примеры
Интерпретация строки как UUID.
Запрос:
SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))
Результат:
┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐
│ 08090a0b-0c0d-0e0f-0001-020304050607 │
└───────────────────────────────────────────────────────────────────────┘
Переход в UUID и обратно.
Запрос:
WITH
generateUUIDv4() AS uuid,
identity(lower(hex(reverse(reinterpretAsString(uuid))))) AS str,
reinterpretAsUUID(reverse(unhex(str))) AS uuid2
SELECT uuid = uuid2;
Результат:
┌─equals(uuid, uuid2)─┐
│ 1 │
└─────────────────────┘
CAST(x, T)
Преобразует x в тип данных t.
Поддерживается также синтаксис CAST(x AS t).
Пример:
SELECT
'2016-06-15 23:00:00' AS timestamp,
CAST(timestamp AS DateTime) AS datetime,
CAST(timestamp AS Date) AS date,
CAST(timestamp, 'String') AS string,
CAST(timestamp, 'FixedString(22)') AS fixed_string
┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string──────────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴───────────────────────────┘
Преобразование в FixedString(N) работает только для аргументов типа String или FixedString(N).
Поддержано преобразование к типу Nullable и обратно. Пример:
SELECT toTypeName(x) FROM t_null
┌─toTypeName(x)─┐
│ Int8 │
│ Int8 │
└───────────────┘
SELECT toTypeName(CAST(x, 'Nullable(UInt16)')) FROM t_null
┌─toTypeName(CAST(x, 'Nullable(UInt16)'))─┐
│ Nullable(UInt16) │
│ Nullable(UInt16) │
└─────────────────────────────────────────┘
См. также
- Настройка cast_keep_nullable
toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second)
Приводит аргумент из числового типа данных к типу данных IntervalType.
Синтаксис
toIntervalSecond(number)
toIntervalMinute(number)
toIntervalHour(number)
toIntervalDay(number)
toIntervalWeek(number)
toIntervalMonth(number)
toIntervalQuarter(number)
toIntervalYear(number)
Параметры
number— длительность интервала. Положительное целое число.
Возвращаемые значения
- Значение с типом данных
Interval.
Пример
WITH
toDate('2019-01-01') AS date,
INTERVAL 1 WEEK AS interval_week,
toIntervalWeek(1) AS interval_to_week
SELECT
date + interval_week,
date + interval_to_week
┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┐
│ 2019-01-08 │ 2019-01-08 │
└───────────────────────────┴──────────────────────────────┘
parseDateTimeBestEffort
Преобразует дату и время в строковом представлении к типу данных DateTime.
Функция распознаёт форматы ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, формат даты времени ClickHouse’s а также некоторые другие форматы.
Синтаксис
parseDateTimeBestEffort(time_string[, time_zone]);
Параметры
time_string— строка, содержащая дату и время для преобразования. String.time_zone— часовой пояс. Функция анализируетtime_stringв соответствии с заданным часовым поясом. String.
Поддерживаемые нестандартные форматы
- Unix timestamp в строковом представлении. 9 или 10 символов.
- Строка с датой и временем:
YYYYMMDDhhmmss,DD/MM/YYYY hh:mm:ss,DD-MM-YY hh:mm,YYYY-MM-DD hh:mm:ss, etc. - Строка с датой, но без времени:
YYYY,YYYYMM,YYYY*MM,DD/MM/YYYY,DD-MM-YYи т.д. - Строка с временем, и с днём:
DD,DD hh,DD hh:mm. В этом случаеYYYY-MMпринимается равным2000-01. - Строка, содержащая дату и время вместе с информацией о часовом поясе:
YYYY-MM-DD hh:mm:ss ±h:mm, и т.д. Например,2020-12-12 17:36:00 -5:00.
Для всех форматов с разделителями функция распознаёт названия месяцев, выраженных в виде полного англоязычного имени месяца или в виде первых трёх символов имени месяца. Примеры: 24/DEC/18, 24-Dec-18, 01-September-2018.
Возвращаемое значение
time_stringпреобразованная к типу данныхDateTime.
Примеры
Запрос:
SELECT parseDateTimeBestEffort('12/12/2020 12:12:57')
AS parseDateTimeBestEffort;
Результат:
┌─parseDateTimeBestEffort─┐
│ 2020-12-12 12:12:57 │
└─────────────────────────┘
Запрос:
SELECT parseDateTimeBestEffort('Sat, 18 Aug 2018 07:22:16 GMT', 'Europe/Moscow')
AS parseDateTimeBestEffort
Результат:
┌─parseDateTimeBestEffort─┐
│ 2018-08-18 10:22:16 │
└─────────────────────────┘
Запрос:
SELECT parseDateTimeBestEffort('1284101485')
AS parseDateTimeBestEffort
Результат:
┌─parseDateTimeBestEffort─┐
│ 2015-07-07 12:04:41 │
└─────────────────────────┘
Запрос:
SELECT parseDateTimeBestEffort('2018-12-12 10:12:12')
AS parseDateTimeBestEffort
Результат:
┌─parseDateTimeBestEffort─┐
│ 2018-12-12 10:12:12 │
└─────────────────────────┘
Запрос:
SELECT parseDateTimeBestEffort('10 20:19')
Результат:
┌─parseDateTimeBestEffort('10 20:19')─┐
│ 2000-01-10 20:19:00 │
└─────────────────────────────────────┘
См. также
parseDateTimeBestEffortUS
Эта функция похожа на ‘parseDateTimeBestEffort’, но разница состоит в том, что в она предполагает американский формат даты (MM/DD/YYYY etc.) в случае неоднозначности.
Синтаксис
parseDateTimeBestEffortUS(time_string [, time_zone]);
Параметры
time_string— строка, содержащая дату и время для преобразования. String.time_zone— часовой пояс. Функция анализируетtime_stringв соответствии с часовым поясом. String.
Поддерживаемые нестандартные форматы
- Строка, содержащая 9-10 цифр unix timestamp.
- Строка, содержащая дату и время:
YYYYMMDDhhmmss,MM/DD/YYYY hh:mm:ss,MM-DD-YY hh:mm,YYYY-MM-DD hh:mm:ss, etc. - Строка с датой, но без времени:
YYYY,YYYYMM,YYYY*MM,MM/DD/YYYY,MM-DD-YYetc. - Строка, содержащая день и время:
DD,DD hh,DD hh:mm. В этом случаеYYYY-MMзаменяется на2000-01. - Строка, содержащая дату и время, а также информацию о часовом поясе:
YYYY-MM-DD hh:mm:ss ±h:mmи т.д. Например,2020-12-12 17:36:00 -5:00.
Возвращаемое значение
time_stringпреобразован в тип данныхDateTime.
Примеры
Запрос:
SELECT parseDateTimeBestEffortUS('09/12/2020 12:12:57')
AS parseDateTimeBestEffortUS;
Ответ:
┌─parseDateTimeBestEffortUS─┐
│ 2020-09-12 12:12:57 │
└─────────────────────────——┘
Запрос:
SELECT parseDateTimeBestEffortUS('09-12-2020 12:12:57')
AS parseDateTimeBestEffortUS;
Ответ:
┌─parseDateTimeBestEffortUS─┐
│ 2020-09-12 12:12:57 │
└─────────────────────────——┘
Запрос:
SELECT parseDateTimeBestEffortUS('09.12.2020 12:12:57')
AS parseDateTimeBestEffortUS;
Ответ:
┌─parseDateTimeBestEffortUS─┐
│ 2020-09-12 12:12:57 │
└─────────────────────────——┘
toUnixTimestamp64Milli
toUnixTimestamp64Micro
toUnixTimestamp64Nano
Преобразует значение DateTime64 в значение Int64 с фиксированной точностью менее одной секунды.
Входное значение округляется соответствующим образом вверх или вниз в зависимости от его точности. Обратите внимание, что возвращаемое значение - это временная метка в UTC, а не в часовом поясе DateTime64.
Синтаксис
toUnixTimestamp64Milli(value)
Параметры
value— значениеDateTime64с любой точностью.
Возвращаемое значение
- Значение
value, преобразованное в тип данныхInt64.
Примеры
Запрос:
WITH toDateTime64('2019-09-16 19:20:12.345678910', 6) AS dt64
SELECT toUnixTimestamp64Milli(dt64)
Ответ:
┌─toUnixTimestamp64Milli(dt64)─┐
│ 1568650812345 │
└──────────────────────────────┘
Запрос:
WITH toDateTime64('2019-09-16 19:20:12.345678910', 6) AS dt64
SELECT toUnixTimestamp64Nano(dt64)
Ответ:
┌─toUnixTimestamp64Nano(dt64)─┐
│ 1568650812345678000 │
└─────────────────────────────┘
fromUnixTimestamp64Milli
fromUnixTimestamp64Micro
fromUnixTimestamp64Nano
Преобразует значение Int64 в значение DateTime64 с фиксированной точностью менее одной секунды и дополнительным часовым поясом. Входное значение округляется соответствующим образом вверх или вниз в зависимости от его точности. Обратите внимание, что входное значение обрабатывается как метка времени UTC, а не метка времени в заданном (или неявном) часовом поясе.
Синтаксис
fromUnixTimestamp64Milli(value [, ti])
Параметры
value— значение типыInt64с любой точностью.timezone— (не обязательный параметр) часовой пояс в форматеStringдля возвращаемого результата.
Возвращаемое значение
- Значение
value, преобразованное в тип данныхDateTime64.
Пример
Запрос:
WITH CAST(1234567891011, 'Int64') AS i64
SELECT fromUnixTimestamp64Milli(i64, 'UTC')
Ответ:
┌─fromUnixTimestamp64Milli(i64, 'UTC')─┐
│ 2009-02-13 23:31:31.011 │
└──────────────────────────────────────┘
toLowCardinality
Преобразует входные данные в версию LowCardianlity того же типа данных.
Чтобы преобразовать данные из типа LowCardinality, используйте функцию CAST. Например, CAST(x as String).
Синтаксис
toLowCardinality(expr)
Параметры
expr— Выражение, которое в результате преобразуется в один из поддерживаемых типов данных.
Возвращаемое значение
- Результат преобразования
expr.
Тип: LowCardinality(expr_result_type)
Example
Запрос:
SELECT toLowCardinality('1')
Результат:
┌─toLowCardinality('1')─┐
│ 1 │
└───────────────────────┘
formatRow
Преобразует произвольные выражения в строку заданного формата.
Синтаксис
formatRow(format, x, y, ...)
Параметры
Возвращаемое значение
- Отформатированная строка (в текстовых форматах обычно с завершающим переводом строки).
Пример
Запрос:
SELECT formatRow('CSV', number, 'good')
FROM numbers(3)
Ответ:
┌─formatRow('CSV', number, 'good')─┐
│ 0,"good"
│
│ 1,"good"
│
│ 2,"good"
│
└──────────────────────────────────┘
formatRowNoNewline
Преобразует произвольные выражения в строку заданного формата. При этом удаляет лишние переводы строк \n, если они появились.
Синтаксис
formatRowNoNewline(format, x, y, ...)
Параметры
Возвращаемое значение
- Отформатированная строка (в текстовых форматах без завершающего перевода строки).
Пример
Запрос:
SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3)
Ответ:
┌─formatRowNoNewline('CSV', number, 'good')─┐
│ 0,"good" │
│ 1,"good" │
│ 2,"good" │
└───────────────────────────────────────────┘