Data Types

ClickHouse can store various kinds of data in table cells.

This section describes the supported data types and special considerations for using and/or implementing them if any.

You can check whether data type name is case-sensitive in the system.data_type_families table.

UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256

Fixed-length integers, with or without a sign.

When creating tables, numeric parameters for integer numbers can be set (e.g. TINYINT(8), SMALLINT(16), INT(32), BIGINT(64)), but ClickHouse ignores them.

Int Ranges​

  • Int8 — [-128 : 127]

  • Int16 — [-32768 : 32767]

  • Int32 — [-2147483648 : 2147483647]

  • Int64 — [-9223372036854775808 : 9223372036854775807]

  • Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]

  • Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]

Aliases:

  • Int8 — TINYINT, BOOL, BOOLEAN, INT1.

  • Int16 — SMALLINT, INT2.

  • Int32 — INT, INT4, INTEGER.

  • Int64 — BIGINT.

UInt Ranges​

  • UInt8 — [0 : 255]

  • UInt16 — [0 : 65535]

  • UInt32 — [0 : 4294967295]

  • UInt64 — [0 : 18446744073709551615]

  • UInt128 — [0 : 340282366920938463463374607431768211455]

  • UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

Float32, Float64

Floating point numbers.

Types are equivalent to types of C:

  • Float32 — float.

  • Float64 — double.

Aliases:

  • Float32 — FLOAT.

  • Float64 — DOUBLE.

When creating tables, numeric parameters for floating point numbers can be set (e.g. FLOAT(12), FLOAT(15, 22), DOUBLE(12), DOUBLE(4, 18)), but ClickHouse ignores them.

Boolean Values bool (boolean)

Type bool is stored as UInt8. Possible values true (1), false (0).

select true as col, toTypeName(col);
┌─col──┬─toTypeName(true)─┐
│ true │ Bool             │
└──────┴──────────────────┘

select true == 1 as col, toTypeName(col);
┌─col─┬─toTypeName(equals(true, 1))─┐
│   1 │ UInt8                       │
└─────┴─────────────────────────────┘
CREATE TABLE test_bool
(
    `A` Int64,
    `B` Bool
)
ENGINE = Memory;

INSERT INTO test_bool VALUES (1, true),(2,0);

SELECT * FROM test_bool;
┌─A─┬─B─────┐
│ 1 │ true  │
│ 2 │ false │
└───┴───────┘

String

Strings of an arbitrary length. The length is not limited. The value can contain an arbitrary set of bytes, including null bytes. The String type replaces the types VARCHAR, BLOB, CLOB, and others from other DBMSs.

When creating tables, numeric parameters for string fields can be set (e.g. VARCHAR(255)), but ClickHouse ignores them.

Aliases:

  • String — LONGTEXT, MEDIUMTEXT, TINYTEXT, TEXT, LONGBLOB, MEDIUMBLOB, TINYBLOB, BLOB, VARCHAR, CHAR.

Date

A date. Stored in two bytes as the number of days since 1970-01-01 (unsigned). Allows storing values from just after the beginning of the Unix Epoch to the upper threshold defined by a constant at the compilation stage (currently, this is until the year 2149, but the final fully-supported year is 2148).

Supported range of values: [1970-01-01, 2149-06-06].

The date value is stored without the time zone.

Example

Creating a table with a Date-type column and inserting data into it:

CREATE TABLE dt
(
    `timestamp` Date,
    `event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt VALUES (1546300800, 1), ('2019-01-01', 2);
SELECT * FROM dt;
┌──timestamp─┬─event_id─┐
│ 2019-01-01 │        1 │
│ 2019-01-01 │        2 │
└────────────┴──────────┘

Date32

A date. Supports the date range same with DateTime64. Stored as a signed 32-bit integer in native byte order with the value representing the days since 1970-01-01 (0 represents 1970-01-01 and negative values represent the days before 1970).

Examples

Creating a table with a Date32-type column and inserting data into it:

CREATE TABLE new
(
    `timestamp` Date32,
    `event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO new VALUES (4102444800, 1), ('2100-01-01', 2);
SELECT * FROM new;
┌──timestamp─┬─event_id─┐
│ 2100-01-01 │        1 │
│ 2100-01-01 │        2 │
└────────────┴──────────┘

DateTime

Allows to store an instant in time, that can be expressed as a calendar date and a time of a day.

Syntax:

DateTime([timezone])

Supported range of values: [1970-01-01 00:00:00, 2106-02-07 06:28:15].

Resolution: 1 second.

Usage Remarks​

The point in time is saved as a Unix timestamp, regardless of the time zone or daylight saving time. The time zone affects how the values of the DateTime type values are displayed in text format and how the values specified as strings are parsed (‘2020-01-01 05:00:01’).

Timezone agnostic unix timestamp is stored in tables, and the timezone is used to transform it to text format or back during data import/export or to make calendar calculations on the values (example: toDate, toHour functions et cetera). The time zone is not stored in the rows of the table (or in resultset), but is stored in the column metadata.

A list of supported time zones can be found in the IANA Time Zone Database and also can be queried by SELECT * FROM system.time_zones. The list is also available at Wikipedia.

You can explicitly set a time zone for DateTime-type columns when creating a table. Example: DateTime('UTC'). If the time zone isn’t set, ClickHouse uses the value of the timezone parameter in the server settings or the operating system settings at the moment of the ClickHouse server start.

The clickhouse-client applies the server time zone by default if a time zone isn’t explicitly set when initializing the data type. To use the client time zone, run clickhouse-client with the --use_client_time_zone parameter.

ClickHouse outputs values depending on the value of the date_time_output_format setting. YYYY-MM-DD hh:mm:ss text format by default. Additionaly you can change the output with the formatDateTime function.

When inserting data into ClickHouse, you can use different formats of date and time strings, depending on the value of the date_time_input_format setting.

Examples​

1. Creating a table with a DateTime-type column and inserting data into it:

CREATE TABLE dt
(
    `timestamp` DateTime('Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt Values (1546300800, 1), ('2019-01-01 00:00:00', 2);
SELECT * FROM dt;
┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00 │        1 │
│ 2019-01-01 00:00:00 │        2 │
└─────────────────────┴──────────┘

  • When inserting datetime as an integer, it is treated as Unix Timestamp (UTC). 1546300800 represents '2019-01-01 00:00:00' UTC. However, as timestamp column has Asia/Istanbul (UTC+3) timezone specified, when outputting as string the value will be shown as '2019-01-01 03:00:00'

  • When inserting string value as datetime, it is treated as being in column timezone. '2019-01-01 00:00:00' will be treated as being in Asia/Istanbul timezone and saved as 1546290000.

2. Filtering on DateTime values

SELECT * FROM dt WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')
┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00 │        2 │
└─────────────────────┴──────────┘

DateTime column values can be filtered using a string value in WHERE predicate. It will be converted to DateTime automatically:

SELECT * FROM dt WHERE timestamp = '2019-01-01 00:00:00'
┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00 │        1 │
└─────────────────────┴──────────┘

3. Getting a time zone for a DateTime-type column:

SELECT toDateTime(now(), 'Asia/Istanbul') AS column, toTypeName(column) AS x
┌──────────────column─┬─x─────────────────────────┐
│ 2019-10-16 04:12:04 │ DateTime('Asia/Istanbul') │
└─────────────────────┴───────────────────────────┘

4. Timezone conversion

SELECT
toDateTime(timestamp, 'Europe/London') as lon_time,
toDateTime(timestamp, 'Asia/Istanbul') as mos_time
FROM dt
┌───────────lon_time──┬────────────mos_time─┐
│ 2019-01-01 00:00:00 │ 2019-01-01 03:00:00 │
│ 2018-12-31 21:00:00 │ 2019-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

As timezone conversion only changes the metadata, the operation has no computation cost.

DateTime64

Allows to store an instant in time, that can be expressed as a calendar date and a time of a day, with defined sub-second precision

Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ]. Typically are used - 3 (milliseconds), 6 (microseconds), 9 (nanoseconds).

Syntax:

DateTime64(precision, [timezone])

Internally, stores data as a number of ‘ticks’ since epoch start (1970-01-01 00:00:00 UTC) as Int64. The tick resolution is determined by the precision parameter. Additionally, the DateTime64 type can store time zone that is the same for the entire column, that affects how the values of the DateTime64 type values are displayed in text format and how the values specified as strings are parsed (‘2020-01-01 05:00:01.000’). The time zone is not stored in the rows of the table (or in resultset), but is stored in the column metadata. See details in DateTime.

Supported range of values: [1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999]

Note: The precision of the maximum value is 8. If the maximum precision of 9 digits (nanoseconds) is used, the maximum supported value is 2262-04-11 23:47:16 in UTC.

Examples​

  1. Creating a table with DateTime64-type column and inserting data into it:

CREATE TABLE dt
(
    `timestamp` DateTime64(3, 'Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt Values (1546300800123, 1), (1546300800.123, 2), ('2019-01-01 00:00:00', 3);
SELECT * FROM dt;
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.123 │        1 │
│ 2019-01-01 03:00:00.123 │        2 │
│ 2019-01-01 00:00:00.000 │        3 │
└─────────────────────────┴──────────┘

  • When inserting datetime as an integer, it is treated as an appropriately scaled Unix Timestamp (UTC). 1546300800000 (with precision 3) represents '2019-01-01 00:00:00' UTC. However, as timestamp column has Asia/Istanbul (UTC+3) timezone specified, when outputting as a string the value will be shown as '2019-01-01 03:00:00'. Inserting datetime as a decimal will treat it similarly as an integer, except the value before the decimal point is the Unix Timestamp up to and including the seconds, and after the decimal point will be treated as the precision.

  • When inserting string value as datetime, it is treated as being in column timezone. '2019-01-01 00:00:00' will be treated as being in Asia/Istanbul timezone and stored as 1546290000000.

  1. Filtering on DateTime64 values

SELECT * FROM dt WHERE timestamp = toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul');
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00.000 │        2 │
└─────────────────────────┴──────────┘

Unlike DateTime, DateTime64 values are not converted from String automatically.

SELECT * FROM dt WHERE timestamp = toDateTime64(1546300800.123, 3);
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00.123 │        1 │
│ 2019-01-01 00:00:00.123 │        2 │
└─────────────────────────┴──────────┘

Contrary to inserting, the toDateTime64 function will treat all values as the decimal variant, so precision needs to be given after the decimal point.

  1. Getting a time zone for a DateTime64-type value:

SELECT toDateTime64(now(), 3, 'Asia/Istanbul') AS column, toTypeName(column) AS x;
┌──────────────────column─┬─x──────────────────────────────┐
│ 2019-10-16 04:12:04.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴────────────────────────────────┘

  1. Timezone conversion

SELECT
toDateTime64(timestamp, 3, 'Europe/London') as lon_time,
toDateTime64(timestamp, 3, 'Asia/Istanbul') as mos_time
FROM dt;
┌───────────────lon_time──┬────────────────mos_time─┐
│ 2019-01-01 00:00:00.000 │ 2019-01-01 03:00:00.000 │
│ 2018-12-31 21:00:00.000 │ 2019-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘
PreviousAggregate FunctionsNextUnlock.CI

Last updated