Primitive data types

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

Numeric types

Type	Description	Notes
`Bool`	Boolean value.
`Int8`	Signed integer. Valid values: from –2⁷ to 2⁷–1.
`Int16`	Signed integer. Valid values: from –2¹⁵ to 2¹⁵–1.
`Int32`	Signed integer. Valid values: from –2³¹ to 2³¹–1.
`Int64`	Signed integer. Valid values: from –2⁶³ to 2⁶³–1.
`Uint8`	Unsigned integer. Valid values: from 0 to 2⁸–1.
`Uint16`	Unsigned integer. Valid values: from 0 to 2¹⁶–1.
`Uint32`	Unsigned integer. Valid values: from 0 to 2³²–1.
`Uint64`	Unsigned integer. Valid values: from 0 to 2⁶⁴–1.
`Float`	Floating-point number with variable precision, 4 bytes in size.	Cannot be used in the primary key and in the columns that form the secondary index key
`Double`	Floating-point number with variable precision, 8 bytes in size.	Cannot be used in the primary key and in the columns that form the secondary index key
`Decimal(precision, scale)`	Floating-point number with fixed precision, 16 bytes in size. Precision is the maximum total number of stored decimal digits, takes values from 1 to 35. Scale is the maximum number of stored decimal digits to the right of the decimal point, takes values from 0 to the value of precision.
`DyNumber`	Binary representation of a floating-point number with up to 38 digits of precision. Valid values: positive from 1×10^-130 to 1×10¹²⁶–1, negative from -1×10¹²⁶–1 to -1×10^-130 and 0. Compatible with the `Number` type of AWS DynamoDB. Not recommended for use in ydb-native applications.	Not supported in columnar tables

Examples

SELECT
  Bool("true"),
  Uint8("0"),
  Int32("-1"),
  Uint32("2"),
  Int64("-3"),
  Uint64("4"),
  Float("-5"),
  Double("6"),
  Decimal("1.23", 5, 2), -- up to 5 decimal places, of which 2 are after the decimal point
  DyNumber("1"),
  String("foo"),
  Utf8("hello"),
  Yson("<a=1>[3;%false]"),
  Json(@@{"a":1,"b":null}@@),
  Date("2017-11-27"),
  Datetime("2017-11-27T13:24:00Z"),
  Timestamp("2017-11-27T13:24:00.123456Z"),
  Interval("P1W2DT2H3M4.567890S"),
  TzDate("2017-11-27,Europe/Moscow"),
  TzDatetime("2017-11-27T13:24:00,America/Los_Angeles"),
  TzTimestamp("2017-11-27T13:24:00.123456,GMT"),
  Uuid("f9d5cc3f-f1dc-4d9c-b97e-766e57ca4ccb");

String types

Type	Description	Notes
`String`	String, can contain arbitrary binary data
`Bytes` (alias for `String`)	String, can contain arbitrary binary data
`Utf8`	Text in UTF-8 encoding
`Text` (alias for `Utf8`)	Text in UTF-8 encoding
`Json`	JSON in text representation	Does not support comparison, cannot be used in the primary key and in the columns that form the secondary index key
`JsonDocument`	JSON in binary indexed representation	Does not support comparison, cannot be used in the primary key and in the columns that form the secondary index key
`Yson`	YSON in text or binary representation	Does not support comparison, cannot be used in the primary key and in the columns that form the secondary index key
`Uuid`	Universal identifier UUID	Not supported in columnar tables

The maximum size of a value in a cell of a non-key column with any string data type is 8 MB.

{% endnote %}
In contrast to the Json data type, which stores the original text representation provided by the user, JsonDocument uses a binary indexed representation. An important semantic difference is that JsonDocument does not preserve formatting, the order of keys in objects, or their duplicates.

Thanks to the indexed representation, JsonDocument allows you to traverse the document model using JsonPath without the need to parse the entire content. This enables efficient execution of operations from the JSON API, reducing latency and the cost of user queries. Query execution on JsonDocument can be several times more efficient, depending on the type of load.

Due to the added redundancy, JsonDocument is less efficient in terms of storage. The additional storage overhead depends on the specific content and averages 20–30% of the original volume. Storing data in JsonDocument format requires additional conversion from the text representation, which makes writing less efficient. However, for most read-intensive scenarios involving JSON data processing, this data type is preferable and is recommended for use.

The Double type is used to store numbers (JSON Number) in JsonDocument and for arithmetic operations on them in the JSON API. Loss of precision is possible when using non-standard number representations in the original JSON document.

Date and time

Type	Description	Possible values	Size (bytes)	Notes
`Date`	UTC midnight, accuracy to the day	from 00:00 January 1, 1970 to 00:00 January 1, 2106	2	—
`Date32`	UTC midnight, accuracy to the day	from 00:00 January 1, 144169 BC to 00:00 January 1, 148107 AD	4	—
`Datetime`	UTC time, accuracy to the second	from 00:00 January 1, 1970 to 00:00 January 1, 2106	4	—
`Datetime64`	UTC time, accuracy to the second	from 00:00 January 1, 144169 BC to 00:00 January 1, 148107 AD	8	—
`Timestamp`	UTC time, accuracy to the microsecond	from 00:00 January 1, 1970 to 00:00 January 1, 2106	8	—
`Timestamp64`	UTC time, accuracy to the microsecond	from 00:00 January 1, 144169 BC to 00:00 January 1, 148107 AD	8	—
`Interval`	Time interval, accuracy to the microsecond	from -136 years to +136 years	8	Not supported in columnar tables
`Interval64`	Time interval, accuracy to the microsecond	from -292277 years to +292277 years	8	Not supported in columnar tables
`TzDate`	UTC time corresponding to midnight in a given timezone	from 00:00 January 1, 1970 to 00:00 January 1, 2106		Not supported in table columns
`TzDate32`	UTC time corresponding to midnight in a given timezone	from 00:00 January 1, 144169 BC to 00:00 January 1, 148107 AD	4 and timezone mark	—
`TzDateTime`	UTC time with timezone mark and accuracy to the second	from 00:00 January 1, 1970 to 00:00 January 1, 2106		Not supported in table columns
`TzDateTime64`	UTC time with timezone mark and accuracy to the second	from 00:00 January 1, 144169 BC to 00:00 January 1, 148107 AD	8 and timezone mark	—
`TzTimestamp`	UTC time with timezone mark and accuracy to the microsecond	from 00:00 January 1, 1970 to 00:00 January 1, 2106		Not supported in table columns
`TzTimestamp64`	UTC time with timezone mark and accuracy to the microsecond	from 00:00 January 1, 144169 BC to 00:00 January 1, 148107 AD	8 and timezone mark	—

¹ Midnight is understood as the moment when all time components are equal to zero.

Interval type behavior and limitations

The Interval type uses syntax based on the ISO 8601 standard, with several specifics:

only intervals that can be represented as an integer number of microseconds are supported (intervals in years Y and months M are not supported due to their variable duration);
intervals with start/end points or repetitions are not supported;
the use of a negative sign to indicate a shift into the past is supported;
combining the week literal W with other literals is supported;
microseconds can be specified as a fractional part of a second.

Interval data examples

SELECT
    Interval("P1W2DT2H3M4.567890S"), -- interval 1 week 2 days 2 hours 3 minutes 4.567890 seconds
    Interval("P1W"),                 -- interval 1 week (7 days)
    Interval("-P1D");                -- interval in the past 1 day (24 hours)

Time zone label support features

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

When converting (using CAST, DateTime::Parse, DateTime::Format) to and from a string.
In DateTime::Split — the time zone component appears in Resource<TM>.

The actual time position value for these types is stored in UTC, and the time zone label does not participate in other calculations. For example:

SELECT -- these expressions are always true for any time zones: the time zone does not affect the point in time.
    AddTimezone(CurrentUtcDate(), "Europe/Moscow") ==
        AddTimezone(CurrentUtcDate(), "America/New_York"),
    AddTimezone(CurrentUtcDatetime(), "Europe/Moscow") ==
        AddTimezone(CurrentUtcDatetime(), "America/New_York");

It is important to understand that when converting between TzDate and TzDatetime or TzTimestamp, the date corresponds not to midnight local time in the timezone, but to midnight UTC for the date in UTC.

Casting of primitive data types

Explicit casting

Explicit casting using CAST:

Casting to numeric types

Type	Bool	Int8	Int16	Int32	Int64	Uint8	Uint16	Uint32	Uint64	Float	Double	Decimal
Bool	—	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	No
Int8	Yes²	—	Yes	Yes	Yes	Yes³	Yes³	Yes³	Yes³	Yes	Yes	Yes
Int16	Yes²	Yes⁴	—	Yes	Yes	Yes^3,4	Yes³	Yes³	Yes³	Yes	Yes	Yes
Int32	Yes²	Yes⁴	Yes⁴	—	Yes	Yes^3,4	Yes^3,4	Yes³	Yes³	Yes	Yes	Yes
Int64	Yes²	Yes⁴	Yes⁴	Yes⁴	—	Yes^3,4	Yes^3,4	Yes^3,4	Yes³	Yes	Yes	Yes
Uint8	Yes²	Yes⁴	Yes	Yes	Yes	—	Yes	Yes	Yes	Yes	Yes	Yes
Uint16	Yes²	Yes⁴	Yes⁴	Yes	Yes	Yes⁴	—	Yes	Yes	Yes	Yes	Yes
Uint32	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes	Yes⁴	Yes⁴	—	Yes	Yes	Yes	Yes
Uint64	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	—	Yes	Yes	Yes
Float	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes^3,4	Yes^3,4	Yes^3,4	Yes^3,4	—	Yes	No
Double	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes^3,4	Yes^3,4	Yes^3,4	Yes^3,4	Yes	—	No
Decimal	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	—
String	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Bytes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Utf8	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Text	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Json	No	No	No	No	No	No	No	No	No	No	No	No
Yson	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	No
Uuid	No	No	No	No	No	No	No	No	No	No	No	No
Date	No	Yes⁴	Yes⁴	Yes	Yes	Yes⁴	Yes	Yes	Yes	Yes	Yes	No
Datetime	No	Yes⁴	Yes⁴	Yes⁴	Yes	Yes⁴	Yes⁴	Yes	Yes	Yes	Yes	No
Timestamp	No	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes	Yes	Yes	No
Interval	No	Yes⁴	Yes⁴	Yes⁴	Yes	Yes^3,4	Yes^3,4	Yes^3,4	Yes³	Yes	Yes	No
Date32	No	Yes⁴	Yes⁴	Yes	Yes	Yes⁴	Yes	Yes	Yes	Yes	Yes	No
Datetime64	No	Yes⁴	Yes⁴	Yes⁴	Yes	Yes⁴	Yes⁴	Yes	Yes	Yes	Yes	No
Timestamp64	No	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes	Yes	Yes	No
Interval64	No	Yes⁴	Yes⁴	Yes⁴	Yes	Yes^3,4	Yes^3,4	Yes^3,4	Yes³	Yes	Yes	No

¹ True is converted to 1, False is converted to 0.
² Any value other than 0 is converted to True, 0 is converted to False.
³ Only possible if the value is non-negative.
⁴ Only possible if the value falls within the range of allowed values.
⁵ Using the built-in function Yson::ConvertTo.

Casting to date and time data types

Type	Date	Datetime	Timestamp	Interval	Date32	Datetime64	Timestamp64	Interval64
Bool	No	No	No	No	No	No	No	No
Int8	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Int16	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Int32	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Int64	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Uint8	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Uint16	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Uint32	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Uint64	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Float	No	No	No	No	No	No	No	No
Double	No	No	No	No	No	No	No	No
Decimal	No	No	No	No	No	No	No	No
String	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Bytes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Utf8	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Text	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Json	No	No	No	No	No	No	No	No
Yson	No	No	No	No	No	No	No	No
Uuid	No	No	No	No	No	No	No	No
Date	—	Yes	Yes	No	Yes	Yes	Yes	No
Datetime	Yes	—	Yes	No	Yes	Yes	Yes	No
Timestamp	Yes	Yes	—	No	Yes	Yes	Yes	No
Interval	No	No	No	—	No	No	No	Yes
Date32	Yes	Yes	Yes	No	—	Yes	Yes	No
Datetime64	Yes	Yes	Yes	No	Yes	—	Yes	No
Timestamp64	Yes	Yes	Yes	No	Yes	Yes	—	No
Interval64	No	No	No	Yes	No	No	No	—

Casting to other data types

Type	String	Bytes	Utf8	Text	Json	Yson	Uuid
Bool	Yes	Yes	No	No	No	No	No
Int8	Yes	Yes	No	No	No	No	No
Int16	Yes	Yes	No	No	No	No	No
Int32	Yes	Yes	No	No	No	No	No
Int64	Yes	Yes	No	No	No	No	No
Uint8	Yes	Yes	No	No	No	No	No
Uint16	Yes	Yes	No	No	No	No	No
Uint32	Yes	Yes	No	No	No	No	No
Uint64	Yes	Yes	No	No	No	No	No
Float	Yes	Yes	No	No	No	No	No
Double	Yes	Yes	No	No	No	No	No
Decimal	Yes	Yes	No	No	No	No	No
String	—	—	Yes	Yes	Yes	Yes	Yes
Bytes	—	—	Yes	Yes	Yes	Yes	Yes
Utf8	Yes	Yes	—	—	No	No	No
Text	Yes	Yes	—	—	No	No	No
Json	Yes	Yes	Yes	Yes	—	No	No
Yson	Yes¹	Yes¹	No	No	No	No	No
Uuid	Yes	Yes	Yes	Yes	No	No	—
Date	Yes	Yes	Yes	Yes	No	No	No
Datetime	Yes	Yes	Yes	Yes	No	No	No
Timestamp	Yes	Yes	Yes	Yes	No	No	No
Interval	Yes	Yes	Yes	Yes	No	No	No
Date32	Yes	Yes	Yes	Yes	No	No	No
Datetime64	Yes	Yes	Yes	Yes	No	No	No
Timestamp64	Yes	Yes	Yes	Yes	No	No	No
Interval64	Yes	Yes	Yes	Yes	No	No	No

¹ 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, since it failed
    CAST(-1 AS Uint16) IS NULL,             -- true, negative to unsigned
    CAST([-1, 0, 1] AS List<Uint8?>),             -- [null, 0, 1]
-- The element type is optional: the failed element is null.
    CAST(["3.14", "bad", "42"] AS List<Float>),   -- [3.14, 42]
-- The element type is not optional: the failed element is removed.
    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 indicate the result type of the operation if it is possible:

Numeric types

If the numeric types do not match, a BITCAST of both arguments to the result type is performed first, and then the operation.

Type	Int8	Int16	Int32	Int64	Uint8	Uint16	Uint32	Uint64	Float	Double
Int8	—	`Int16`	`Int32`	`Int64`	`Int8`	`Uint16`	`Uint32`	`Uint64`	`Float`	`Double`
Int16	`Int16`	—	`Int32`	`Int64`	`Int16`	`Int16`	`Uint32`	`Uint64`	`Float`	`Double`
Int32	`Int32`	`Int32`	—	`Int64`	`Int32`	`Int32`	`Int32`	`Uint64`	`Float`	`Double`
Int64	`Int64`	`Int64`	`Int64`	—	`Int64`	`Int64`	`Int64`	`Int64`	`Float`	`Double`
Uint8	`Int8`	`Int16`	`Int32`	`Int64`	—	`Uint16`	`Uint32`	`Uint64`	`Float`	`Double`
Uint16	`Uint16`	`Int16`	`Int32`	`Int64`	`Uint16`	—	`Uint32`	`Uint64`	`Float`	`Double`
Uint32	`Uint32`	`Uint32`	`Int32`	`Int64`	`Uint32`	`Uint32`	—	`Uint64`	`Float`	`Double`
Uint64	`Uint64`	`Uint64`	`Uint64`	`Int64`	`Uint64`	`Uint64`	`Uint64`	—	`Float`	`Double`
Float	`Float`	`Float`	`Float`	`Float`	`Float`	`Float`	`Float`	`Float`	—	`Double`
Double	`Double`	`Double`	`Double`	`Double`	`Double`	`Double`	`Double`	`Double`	`Double`	—

Date and time types

Type	Date	Datetime	Timestamp	Interval	TzDate	TzDatetime	TzTimestamp	Date32	Datetime64	Timestamp64	Interval64	TzDate32	TzDatetime64	TzTimestamp64
Date	—	`DateTime`	`Timestamp`	—	`TzDate`	`TzDatetime`	`TzTimestamp`	`Date32`	`DateTime64`	`Timestamp64`	—	`TzDate32`	`TzDatetime64`	`TzTimestamp64`
Datetime	—	—	`Timestamp`	—	—	`TzDatetime`	`TzTimestamp`	—	`Datetime64`	`Timestamp64`	—	—	`TzDatetime64`	`TzTimestamp64`
Timestamp	—	—	—	—	—	—	`TzTimestamp`	—	—	`Timestamp64`	—	—	—	`TzTimestamp64`
Interval	—	—	—	—	—	—	—	—	—	—	—	—	—	—
TzDate	—	—	—	—	—	`TzDatetime`	`TzTimestamp`	—	—	—	—	`TzDate32`	`TzDatetime64`	`TzTimestamp64`
TzDatetime	—	—	—	—	—	—	`TzTimestamp`	—	—	—	—	—	`TzDatetime64`	`TzTimestamp64`
TzTimestamp	—	—	—	—	—	—	—	—	—	—	—	—	—	`TzTimestamp64`
Date32	—	—	—	—	—	—	—	—	`DateTime64`	`Timestamp64`	—	`TzDate32`	`TzDatetime64`	`TzTimestamp64`
Datetime64	—	—	—	—	—	—	—	—	—	`Timestamp64`	—	—	`TzDatetime64`	`TzTimestamp64`
Timestamp64	—	—	—	—	—	—	—	—	—	—	—	—	—	`TzTimestamp64`
Interval64	—	—	—	—	—	—	—	—	—	—	—	—	—	—
TzDate32	—	—	—	—	—	—	—	—	—	—	—	—	`TzDatetime64`	`TzTimestamp64`
TzDatetime64	—	—	—	—	—	—	—	—	—	—	—	—	—	`TzTimestamp64`
TzTimestamp64	—	—	—	—	—	—	—	—	—	—	—	—	—	—

Was the article helpful?

Overview

Serial