Skip to content
XTDB

XTDB Data Types

The following types are available within XTDB:

Numeric Types

SMALLINT

16-bit signed integer

INT | INTEGER

32-bit signed integer

BIGINT

64-bit signed integer

FLOAT | REAL

32-bit (IEEE single-precision) floating-point number

DOUBLE

64-bit (IEEE double-precision) floating-point number

Date/time types

DATE

date without time.

e.g. DATE '2007-06-29'

TIMESTAMP [WITHOUT TIMEZONE]

date and time, without a time-zone offset.

  • SQL standard: TIMESTAMP [WITHOUT TIMEZONE] '2020-01-01 00:00:00'

  • ISO8601: TIMESTAMP [WITHOUT TIMEZONE] '2020-01-01T00:00:00'

TIMESTAMP WITH TIMEZONE

date and time, with a time-zone offset.

  • SQL standard: TIMESTAMP WITH TIMEZONE '2020-01-01 18:00:00+00:00'

  • ISO8601 (WITH TIMEZONE optional):

    • TIMESTAMP [WITH TIMEZONE] '2020-01-01T18:00:00Z

    • TIMESTAMP [WITH TIMEZONE] '2020-01-01T18:00:00+00:00'

    • TIMESTAMP [WITH TIMEZONE] '2020-08-01T18:00:00+01:00[Europe/London]'

TIME [WITHOUT TIMEZONE]

time-of-day, without a time-zone offset.

e.g. TIME '22:15:04.1237'

DURATION

(SQL extension) a fixed amount of time.

Days are assumed to be 24 hours, months and years are not supported.

  • ISO8601: DURATION 'PT1H3M5.533S'

INTERVAL

a value representing the difference between two timestamps

Intervals can either be expressed as years/months or days/hours/minutes/seconds (although these cannot overlap). Years are assumed to be 12 months, no other assumptions are either made or allowed.

  • SQL standard: INTERVAL '1 3' YEAR TO MONTH, INTERVAL '163 12:00:00' DAY TO SECOND

  • ISO8601: INTERVAL 'P1Y3M', INTERVAL 'P163DT12H'

PERIOD

a pair of timestamps representing a temporal range, with inclusive start and exclusive end ('closed-open').

  • PERIOD(DATE '1998-01-05', DATE '1998-01-12')

  • PERIOD(TIMESTAMP '1998-01-05T12:00:00Z', TIMESTAMP '1998-01-12T15:00:00Z')

Conversions between temporal types

There are a number of considerations when casting between temporal types:

  • Casting from DATE to TIMESTAMP assumes the start of the day.

  • Casting to TIMESTAMP WITH TIME ZONE will use the system default time zone.

  • When explicitly casting to most temporal types, can specify an optional fractional precision to truncate the value to:

    • In SQL, the syntax for this would be CAST(value AS TYPE(<precision>)).

  • Casting to/from VARCHAR involves formatting or parsing as ISO8601 strings.

  • Intervals have specific casting behaviors, which are detailed in the next section.

Casting between Intervals

Explicitly casting between intervals is supported, but only between intervals of the same type. When casting between intervals, it is required to specify an interval qualifier, otherwise the cast operation will not do anything.

Casting to an interval qualifier will:

  • Normalize the interval to the new qualifier

    i.e. if an Interval of 25 hours is cast to DAY TO HOUR, it will be normalized to 1 day 1 hour.

  • Truncate the interval to the new qualifier

    i.e. if an Interval of 25 hours is cast to DAY, it will be truncated to 1 day.

Casting to/from Intervals

When casting to/from intervals from other types, the following rules apply:

  • Casting from VARCHAR to an interval:

    • Without specifying an interval qualifier: will parse the string as an ISO8601 interval, and will return a day-time interval.

    • With an interval qualifier: will parse the string and output the type of interval based on the qualifier.

  • Casting from an INTERVAL to VARCHAR will format the interval as an ISO8601 string.

  • Casting from an INTERVAL to DURATION:

    • Will only work if the interval is a day-time interval.

    • Will return the entire interval as its ISO 8601 duration - any days will be converted to 24 hours.

  • Casting from a DURATION to INTERVAL:

    • Always returns a day-time interval.

    • Without specifying an interval qualifier: always returns with zero days and put the whole duration into the time part of the interval.

    • With an interval qualifier: will normalize and truncate the duration according to the interval qualifier (will normalize hours to days, with 1 day = 24 hours, if qualifier contains DAY).

Other scalar types

BOOLEAN

3-valued boolean: TRUE, FALSE or NULL

VARBINARY

a variable-length byte array

e.g. X('41af8e01')

VARCHAR | TEXT

a variable-length character array

e.g.:

  • 'hello world!'

  • E'hello\n world!' - string containing C-style escape characters:

    • \ooo: octal

    • \xXX, \uXXXX, \UXXXXXXXX: 2, 4 or 8 hex digits

    • \r, \n, \t, \\, \'

  • $$dollar quoted string$$: no need to escape single/double quotes etc in here.

    • dollars can also contain a tag, for nesting purposes: $mytag$…​$mytag$

URI

e.g. URI 'https://xtdb.com'

UUID

e.g. UUID '97a392d5-5e3f-406f-9651-a828ee79b156'

Collection Types

XTDB supports arbitrarily nested data in a first-class way, without needing to store it as JSON:

ARRAY

an ordered list of values

e.g.

  • ARRAY[1, 2, 3]

  • [1, 2, 3]

OBJECT | RECORD

a mapping of keys to values:

e.g.

  • OBJECT(name: 'Lucy', age: 38)

  • RECORD(name: 'Lucy', age: 38)

  • {name: 'Lucy', age: 38}