Primitive data types

The terms "simple", "primitive", and "elementary" data types are used synonymously.

Numeric types

Type Description Notes
Bool Boolean value.
Int8 A signed integer.
Acceptable values: from -27 to 27–1.		 Not supported for table columns
Int16 A signed integer.
Acceptable values: from –215 to 215–1.		 Not supported for table columns
Int32 A signed integer.
Acceptable values: from –231 to 231–1.
Int64 A signed integer.
Acceptable values: from –263 to 263–1.
Uint8 Unsigned integer.
Permitted values: from 0 to 28–1.
Uint16 Unsigned integer.
Permitted values: from 0 to 216–1.		 Not supported for table columns
Uint32 Unsigned integer.
Permitted values: from 0 to 232–1.
Uint64 Unsigned integer.
Permitted values: from 0 to 264–1.
Float A real number with variable precision, 4 bytes in size.
Double A real number with variable precision, 8 bytes in size.
Decimal A real number with the specified precision, up to 35 decimal digits

String types

Type Description Notes
String A string that can contain any binary data
Utf8 UTF-8-coded text.
Json JSON represented as text Doesn't support comparison
JsonDocument JSON in an indexed binary representation Doesn't support comparison
Yson YSON in textual or binary presentation Doesn't support comparison
Uuid UUID universal identifier Not supported for table columns

Size limitations

The maximum size of the value in a cell with any string data type is 8 MB.

Unlike the JSON data type that stores the original text representation passed by the user, JsonDocument uses an indexed binary representation. An important difference from the point of view of semantics is that JsonDocument doesn't preserve formatting, the order of keys in objects, or their duplicates.

Thanks to the indexed view, JsonDocument lets you bypass the document model using JsonPath without the need to parse the full content. This helps efficiently perform operations from the JSON API, reducing delays and the cost of user queries. Execution of JsonDocument queries can be up to several times more efficient depending on the type of load.

Due to the added redundancy, JsonDocument is less effective in storage. The additional storage overhead depends on the specific content, but is 20-30% of the original volume on average. Saving data in JsonDocument format requires additional conversion from the textual representation, which makes writing it less efficient. However, for most read-intensive scenarios that involve processing data from JSON, this data type is preferred and recommended.

Warning

Double type is used to store (JSON Number) numerical values in JsonDocument and perform arithmetic operations over them in JSON API. Precision might be lost when non-standard representations of numbers are used in the source JSON document.

Date and time

Type Description Notes
Date Date, precision to the day Range of values for all time types except Interval: From 00:00 01.01.1970 to 00:00 01.01.2106. Internal Date representation: Unsigned 16-bit integer
Datetime Date/time, precision to the second Internal representation: Unsigned 32-bit integer
Timestamp Date/time, precision to the microsecond Internal representation: Unsigned 64-bit integer
Interval Time interval (signed), precision to microseconds Value range: From -136 years to +136 years. Internal representation: Signed 64-bit integer.
TzDate Date with time zone label, precision to the day Not supported in table columns
TzDatetime Date/time with time zone label, precision to the second Not supported in table columns
TzTimestamp Date/time with time zone label, precision to the microsecond Not supported in table columns

Supporting types with a time zone label

Time zone label for the TzDate, TzDatetime, TzTimestamp types is an attribute that is used:

The point in time for these types is stored in UTC, and the timezone label doesn't participate in any other calculations in any way. For example:

select --these expressions are always true for any timezones:  the timezone doesn't affect the point in time.
    AddTimezone(CurrentUtcDate(), "Europe/Moscow") ==
        AddTimezone(CurrentUtcDate(), "America/New_York"),
    AddTimezone(CurrentUtcDatetime(), "Europe/Moscow") ==
        AddTimezone(CurrentUtcDatetime(), "America/New_York");

Keep in mind that when converting between TzDate and TzDatetime, or TzTimestamp the date's midnight doesn't follow the local time zone, but midnight in UTC for the date in UTC.

Casting between data types

Explicit casting

Explicit casting using CAST:

Casting to numeric types

Type Bool Int Uint Float Double Decimal
Bool — Yes1 Yes1 Yes1 Yes1 No
Int Yes2 — Yes3 Yes Yes Yes
Uint Yes2 Yes — Yes Yes Yes
Float Yes2 Yes Yes — Yes No
Double Yes2 Yes Yes Yes — No
Decimal No Yes Yes Yes Yes —
String Yes Yes Yes Yes Yes Yes
Utf8 Yes Yes Yes Yes Yes Yes
Json No No No No No No
Yson Yes4 Yes4 Yes4 Yes4 Yes4 Yes4
Uuid No No No No No No
Date No Yes Yes Yes Yes No
Datetime No Yes Yes Yes Yes No
Timestamp No Yes Yes Yes Yes No
Interval No Yes Yes Yes Yes No

1 True is converted to 1 and False to 0.
2 Any value other than 0 is converted to True, 0 is converted to False.
3 Possible only in the case of a non-negative value.
4 Using the built-in function Yson::ConvertTo.

Converting to date and time data types

Type Date Datetime Timestamp Interval
Bool No No No No
Int Yes Yes Yes Yes
Uint Yes Yes Yes Yes
Float No No No No
Double No No No No
Decimal No No No No
String Yes Yes Yes Yes
Utf8 Yes Yes Yes Yes
Json No No No No
Yson No No No No
Uuid No No No No
Date — Yes Yes No
Datetime Yes — Yes No
Timestamp Yes Yes — No
Interval No No No —

Conversion to other data types

Type String Utf8 Json Yson Uuid
Bool Yes No No No No
Int Yes No No No No
Uint Yes No No No No
Float Yes No No No No
Double Yes No No No No
Decimal Yes No No No No
String — Yes Yes Yes Yes
Utf8 Yes — No No No
Json Yes Yes — No No
Yson Yes4 No No No No
Uuid Yes Yes No No —
Date Yes Yes No No No
Datetime Yes Yes No No No
Timestamp Yes Yes No No No
Interval Yes Yes No No No

4 Using the built-in function Yson::ConvertTo.

Examples

SELECT
    CAST("12345" AS Double),                -- 12345.0
    CAST(1.2345 AS Uint8),                  -- 1
    CAST(12345 AS String),                  -- "12345"
    CAST("1.2345" AS Decimal(5, 2)),        -- 1.23
    CAST("xyz" AS Uint64) IS NULL,          -- true, because it failed
    CAST(-1 AS Uint16) IS NULL,             -- true, negative to unsigned
    CAST([-1, 0, 1] AS List<Uint8?>),             -- [null, 0, 1]
        --Optional item type: failed item to null.
    CAST(["3.14", "bad", "42"] AS List<Float>),   -- [3.14, 42]
        --Non-optional item type: failed item deleted.
    CAST(255 AS Uint8),                     -- 255
    CAST(256 AS Uint8) IS NULL              -- true, out of range

Implicit casting

Implicit type casting that occurs in basic operations ( +-*/) between different data types. The table cells specify the operation result type, if the operation is possible:

Numeric types

Type Int Uint Float Double
Int — Int Float Double
Uint Int — Float Double
Float Float Float — Double
Double Double Double Double —

Date and time types

Type Date Datetime Timestamp Interval TzDate TzDatetime TzTimestamp
Date — — — Date — — —
Datetime — — — Datetime — — —
Timestamp — — — Timestamp — — —
Interval Date Datetime Timestamp — TzDate TzDatetime TzTimestamp
TzDate — — — TzDate — — —
TzDatetime — — — TzDatetime — — —
TzTimestamp — — — TzTimestamp — — —
Previous
Overview
Next
Optional