Data Types

Gnok supports standard SQL data types mapped to Apache Arrow and Iceberg types.

Numeric Types

SQL Type	Aliases	Arrow Type	Description
BOOLEAN	BOOL	Boolean	true / false
TINYINT	BYTE	Int8	8-bit signed integer (-128 to 127). BYTE is the Spark / Java spelling.
SMALLINT	SHORT	Int16	16-bit signed integer. SHORT is the Spark / Java spelling.
INT	INTEGER, INT4	Int32	32-bit signed integer
BIGINT	INT8, LONG	Int64	64-bit signed integer. LONG is the Spark / Java spelling.
FLOAT	REAL, FLOAT4	Float32	32-bit IEEE 754
DOUBLE	DOUBLE PRECISION, FLOAT8	Float64	64-bit IEEE 754
DECIMAL(p, s)	NUMERIC(p, s), DEC(p, s), NUMBER(p, s)	Decimal128	Fixed-point, precision up to 38. NUMBER is the Oracle spelling — bare NUMBER defaults to NUMBER(38, 0), NUMBER(p) to NUMBER(p, 0).

DECIMAL Defaults

• DECIMAL with no arguments defaults to DECIMAL(38, 0)
• DECIMAL(p) defaults scale to 0: DECIMAL(p, 0)
• SUM of a DECIMAL column widens precision to 38

Numeric Literal Typing

Literal form	Type
Integer (1, 42, -100)	BIGINT (Int64)
Unquoted decimal (1.23, 100.00, 0.001)	DECIMAL(p, s) (Decimal128) — precision and scale derived from the digits as written
Scientific notation (1e5, 2.5e-3)	DOUBLE (Float64)
CAST(0.1 AS DOUBLE) / explicit float cast	DOUBLE

Unquoted decimal literals are exact Decimal128 values, so the standard binary-float surprises don't apply:

SELECT 0.1 + 0.2 = 0.3;        -- TRUE  (both sides are Decimal128(2, 1) and Decimal128(2, 1))
SELECT typeof(1.23);           -- 'Decimal128(3, 2)'
SELECT typeof(100.00);         -- 'Decimal128(5, 2)'
SELECT typeof(1e5);            -- 'Float64'  (scientific stays float)
SELECT typeof(1);              -- 'Int64'    (pure integer stays int)

If you need a float, write the literal in scientific notation (1e0) or cast explicitly (CAST(1.0 AS DOUBLE)).

Mixed-type containers — ARRAY[1, 2.5, 3], VALUES (1, 1.0), (2, 2.5) — promote to the narrowest type that covers every element, so an array of integers and unquoted decimals lands as List(Decimal128(p, s)), not as a List(Float64) with silent precision loss.

CAST(literal AS Target) inside a VALUES row, an ARRAY[...] constructor, or a scalar projection is folded to a target-typed scalar at plan time. Mixed-cast literal rows like VALUES (CAST('NaN' AS DOUBLE)), (1.0::DOUBLE) and ARRAY[CAST(1 AS DOUBLE), CAST(2.5 AS DOUBLE)] are emitted directly into the target's column builder rather than passing the source Decimal128 / Utf8 scalar through and tripping a "Type mismatch: expected Decimal128Builder, got Float64"-shape runtime error.

String Types

SQL Type	Aliases	Arrow Type	Description
VARCHAR	STRING, TEXT, CHAR(n), CHARACTER VARYING, NVARCHAR(n), BPCHAR	Utf8	Variable-length UTF-8 string. NVARCHAR(n) is the SQL Server / PostgreSQL Unicode-varchar spelling; BPCHAR is the PostgreSQL blank-padded-CHAR alias. Length-bound (n) is enforced identically to the VARCHAR(n) row above.
BINARY	VARBINARY, BLOB, BYTEA	Binary	Variable-length byte array

note

String values are stored as variable-length Arrow Utf8, but the (n) length parameter is enforced:

• INSERT into a VARCHAR(n) / CHAR(n) column rejects values longer than n Unicode scalars with "value too long for type character varying(N)" (PostgreSQL semantics).
• CAST(expr AS VARCHAR(n)) / CAST(expr AS CHAR(n)) truncates the value to n characters without raising an error (PostgreSQL / DuckDB semantics). TRY_CAST behaves identically.

Date and Time Types

SQL Type	Aliases	Arrow Type	Description
DATE		Date32	Calendar date (days since epoch)
TIME		Time64(Microsecond)	Time of day (microsecond precision)
TIMESTAMP	DATETIME	Timestamp(Microsecond, None)	Timestamp without timezone
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	Timestamp(Microsecond, Some("UTC"))	Timestamp with timezone — the underlying value is always stored in UTC; the type carries the zone label so accessors and casts know to interpret it as zone-aware
INTERVAL		Interval(MonthDayNano)	Calendar / clock duration. Packed as (months: i32, days: i32, nanos: i64) so month- and day-aware operations preserve their semantics across DST and variable-length months. Produced by INTERVAL '…' literals and by timestamp / time subtraction (see Date / timestamp difference result types).

All timestamps use microsecond precision. Nanosecond timestamps can be read from existing Iceberg tables but cannot be created through SQL DDL.

A tz-aware timestamp (Timestamp(Microsecond, Some(tz))) is a wall-clock value pinned to a named zone — arithmetic and comparison always operate on the underlying UTC instant, while display and component extraction (EXTRACT(HOUR FROM ts), TO_CHAR(ts, ...)) project into the labelled zone. Casting a naive TIMESTAMP to TIMESTAMPTZ interprets the wall-clock as UTC; use AT TIME ZONE to interpret it as some other zone (see Functions reference).

TIMESTAMP literal syntax

TIMESTAMP '...' accepts ISO-8601 forms with either a space or T separator, and an optional trailing timezone offset:

Form	Example
Date-only	TIMESTAMP '2024-06-15' (midnight)
Date + time, space separator	TIMESTAMP '2024-06-15 10:00:00'
Date + time, ISO T separator	TIMESTAMP '2024-06-15T10:00:00'
With UTC marker	TIMESTAMP '2024-06-15 10:00:00Z'
With ±HH:MM offset	TIMESTAMP '2024-06-15 10:00:00+05:30'
With ±HHMM compact offset	TIMESTAMP '2024-06-15 10:00:00+0530'
With negative offset	TIMESTAMP '2024-06-15 10:00:00-08:00'

When the literal carries a timezone offset, the wall-clock value is converted to UTC and stored as a Timestamp(Microsecond, None). To preserve the zone, cast to TIMESTAMPTZ explicitly.

Timestamp Variants

SQL Type	Alias	Description
TIMESTAMP_NTZ	TIMESTAMP WITHOUT TIME ZONE	Timezone-naive (default TIMESTAMP behavior)
TIMESTAMP_LTZ		Local timezone-aware (converted to session timezone on display)
TIMESTAMP_TZ	TIMESTAMP WITH TIME ZONE	Stores timezone offset with the value

SELECT CAST('2025-01-15 10:00:00' AS TIMESTAMP_NTZ);
SELECT CAST('2025-01-15 10:00:00' AS TIMESTAMP_LTZ);
SELECT CAST('2025-01-15 10:00:00 +05:00' AS TIMESTAMP_TZ);

Use ALTER SESSION SET TIMEZONE = 'America/New_York' to control the session timezone for TIMESTAMP_LTZ display.

Complex Types

SQL Type	Aliases	Arrow Type	Description
ARRAY<T>	T[], LIST<T>	List	Ordered collection of elements
MAP<K, V>		Map	Key-value pairs
STRUCT<...>		Struct	Named fields

Additional Types

SQL Type	Arrow Type	Iceberg Version	Description
UUID	FixedSizeBinary(16)	v1+	Native UUID — 16 packed bytes, Iceberg uuid primitive. Reported to drivers as Postgres uuid (OID 2950). See UUID below.
JSON / JSONB	Utf8	v1+	Stored as a plain string (no JSON validation at the type level)
VECTOR(N)	FixedSizeList<Float32>(N)	v1+	Fixed-size float32 array for ML embeddings (e.g., VECTOR(1536))
VARIANT	LargeBinary	v3	Semi-structured data in Iceberg's native variant binary format. Supports JSON round-trip, dot-path extraction, and type-specific accessors. See VARIANT functions.
GEOMETRY	LargeBinary	v3	Geometric data (Point, LineString, Polygon) with WKB/WKT serialization. See Spatial functions.
GEOGRAPHY	LargeBinary	v3	Geographic data with geodesic distance calculations. See Spatial functions.

note

VARIANT, GEOMETRY, and GEOGRAPHY types require Iceberg format version 3. Tables must be created with WITH ('format-version' = '3') to use these column types.

UUID

UUID is a first-class type stored as Arrow FixedSizeBinary(16) — 16 packed bytes, mapped to the Iceberg uuid primitive. Over the PostgreSQL wire protocol it is reported as the native uuid type (OID 2950), so typed driver reads (SQLx Uuid, JDBC, psycopg) work directly; over Flight SQL and the HTTP API it renders as the canonical hyphenated string.

CREATE TABLE events (id UUID PRIMARY KEY, payload VARCHAR);
INSERT INTO events VALUES ('550e8400-e29b-41d4-a716-446655440000', 'hello');
SELECT id FROM events WHERE id = '550e8400-e29b-41d4-a716-446655440000';

Literal forms. UUID literals are accepted in any of the PostgreSQL-compatible forms and normalized to the canonical lowercase 8-4-4-4-12 rendering, so equality, DISTINCT, GROUP BY, JOIN, and ::text output all agree:

Form	Example
Hyphenated (canonical)	'550e8400-e29b-41d4-a716-446655440000'
Simple (32 hex, no hyphens)	'550e8400e29b41d4a716446655440000'
Braced	'{550e8400-e29b-41d4-a716-446655440000}'
URN	'urn:uuid:550e8400-e29b-41d4-a716-446655440000'

A malformed value is rejected at bind time — value '…' is not a valid UUID — expected a 128-bit hex value in hyphenated, simple (32 hex), braced, or urn form. gen_random_uuid() generates a random v4 UUID (the aliases uuid(), uuidv4(), uuid_string(), and random_uuid() are equivalent).

Supported operations. UUID columns behave like any other scalar type. Ordering and MIN/MAX follow byte order (which equals canonical-string order):

• Comparison & filtering: =, <>, <, >, BETWEEN, IN, with literals or bound parameters
• ORDER BY, GROUP BY, DISTINCT, COUNT(DISTINCT …), MIN/MAX (scalar and grouped)
• Joins on UUID keys, including a UUID column against a VARCHAR column (the string side is parsed to UUID for the comparison)
• PRIMARY KEY / UNIQUE enforcement (NULLs are distinct for UNIQUE, per ANSI)
• UPDATE / DELETE … WHERE uuid_col = …
• COALESCE / NULLIF / GREATEST / LEAST mixing a UUID and a string
• LAG / LEAD / FIRST_VALUE / LAST_VALUE and other window value functions
• CAST(u AS TEXT) (→ canonical string) and CAST(str AS UUID) (→ 16 bytes), in either direction

Cross-engine portability — UUID vs VARCHAR

The native type writes the Iceberg uuid primitive into table metadata (Parquet FIXED_LEN_BYTE_ARRAY(16), RFC-4122 byte order). DuckDB (Iceberg extension) reads it natively as UUID with correct values — verified against a gnok-written table on S3. Trino likewise reads it natively as UUID; Spark reads it as a string (Iceberg maps uuid → StringType, but can't write a uuid column); some engines' Iceberg uuid support is version-dependent — verify before relying on it. For maximum cross-engine portability (especially with engines that lack full Iceberg uuid support, or for Spark writes), declare the column VARCHAR and store the canonical string instead — the broadly portable representation that every engine reads natively.

Cross-dialect Compatibility Aliases

Gnok accepts a number of cross-dialect type names in CAST / expr::TYPE position so SQL generated by PostgreSQL, MySQL, SQL Server, Spark, and other warehouse drivers compiles without modification. The cast is a pure type-system handshake — the value lands in the closest Arrow representation listed below:

Source dialect	Type	Maps to	Notes
MS-SQL / PG	BIT (no length)	Boolean	CAST(1 AS BIT) → TRUE. With an explicit length (BIT(n), VARBIT(n)), maps to Int64 and treats the bits as a packed integer.
PG	SERIAL / SERIAL4	Int32	Auto-increment is a DDL concern (handled by CREATE TABLE with IDENTITY / DEFAULT NEXTVAL(...)); the cast just adopts the underlying integer type.
PG	SMALLSERIAL	Int16	Same as SERIAL but for SMALLINT.
PG	BIGSERIAL	Int64	Same as SERIAL but for BIGINT.
PG / MS-SQL	MONEY	Decimal128(19, 4)	Exact arithmetic at (19, 4) precision — no float drift on currency calculations.
PG / MS-SQL	XML	Utf8	Text-with-validation surrogate. XML accessor functions emit their own validation errors on malformed input.
PG	REGCLASS	Utf8	OID-name reference compatibility hook for ORM-emitted 'public.users'::regclass. The engine does not have a pg_class catalog wired through.
PG	JSON / JSONB	Utf8	Canonical text-JSON representation. The ->, ->>, JSON_EXTRACT, and JSON_VALUE accessors all consume Utf8 surrogates. The future v3 VARIANT pipeline will replace this with native binary VARIANT.
MySQL / ClickHouse	DATETIME	Timestamp(Microsecond, None)	No-timezone alias of TIMESTAMP.
PG / MS-SQL	UUID	FixedSizeBinary(16)	Native uuid (Iceberg uuid, OID 2950). Accepts hyphenated, simple 32-hex, braced, and urn:uuid: literal forms; stored/compared canonically. gen_random_uuid() returns a uuid string that coerces into uuid columns. See UUID.

Type Casting

Explicit Cast

-- CAST syntax
SELECT CAST(amount AS DECIMAL(10, 2)) FROM orders;

-- PostgreSQL :: syntax
SELECT amount::DECIMAL(10, 2) FROM orders;

Implicit Casting Rules

Gnok automatically applies safe implicit casts:

From	To	Rule
Integer types	Wider integer	TINYINT → SMALLINT → INT → BIGINT
Integer	Float	INT → FLOAT64
Float types	Wider float	FLOAT → DOUBLE
Integer	Decimal	Preserves precision
NULL	Any type	Always compatible
Date32	Date64	Widened
DATE	TIMESTAMP	Auto-promoted for comparison (e.g., my_date = my_timestamp); the date is widened to a timestamp at midnight in the target unit
Timestamp units	Wider unit	Microsecond ↔ Nanosecond (same timezone)
VARCHAR ↔ Int* / Float* / DECIMAL	Common numeric type	Parseable string is strict-cast to the numeric side at comparison time ('1.5' = 1.5, '42' = 42, '0.001' < d). A non-parseable string errors explicitly (Cannot cast 'abc' to ...) rather than silently becoming NULL. The same string operand inside arithmetic ('1' + 2) is still rejected at bind time — see Numeric arithmetic with a string operand below.

Casts not in the table above (e.g., string-to-temporal outside a comparison context) require an explicit CAST.

Numeric arithmetic with a string operand

Arithmetic and comparison operators (+, -, *, /, %) reject operand pairs where one side is numeric and the other is VARCHAR, matching Postgres / DuckDB. Both shapes are rejected at bind time:

-- Bind error: mixed numeric / string in arithmetic
SELECT 1 + '2';
SELECT '1' + '2';

-- OK: cast explicitly
SELECT 1 + CAST('2' AS BIGINT);
SELECT CAST('1' AS BIGINT) + 2;

Earlier builds silently coerced 1 + '2' to 3 typed Utf8, producing schema / runtime drift across downstream consumers.

Rejected casts

From → To	Status	Workaround
Integer → DATE	Bind error	Cast to VARCHAR first (CAST(n AS VARCHAR)::DATE), or use TO_DATE(CAST(n AS VARCHAR), 'YYYYMMDD'). Direct integer-day-count → DATE was ambiguous (epoch days vs YYYYMMDD) and is no longer accepted.
Integer → TIMESTAMP	Bind / runtime error	Use TO_TIMESTAMP(n) or FROM_UNIXTIME(n) (both interpret n as epoch seconds), or cast through VARCHAR first. Direct CAST(<int> AS TIMESTAMP) was ambiguous: Arrow defaults to microseconds-since-epoch, so CAST(1704067200 AS TIMESTAMP) (epoch seconds for 2024-01-01) silently returned 1970-01-01T00:28:24.067200Z. The cast is now rejected with an error directing callers to TO_TIMESTAMP / FROM_UNIXTIME. TO_TIMESTAMP(numeric) itself routes through FROM_UNIXTIME (it no longer goes through the rejected CAST path).

DECIMAL(38, _) arithmetic overflow

DECIMAL(38, s) is the widest decimal Gnok supports — its underlying physical representation is a signed 128-bit integer, but its logical range is bounded at 10^38 - 1, not the looser i128::MAX. Wave 97 tightened the overflow check so that addition and subtraction at the maximum precision now raise an explicit error when the mathematical result exceeds 10^38 - 1:

-- Bind / runtime error: result would be a 39-digit value, exceeds DECIMAL(38, 0)
SELECT CAST('99999999999999999999999999999999999999' AS DECIMAL(38, 0))
     + CAST('1' AS DECIMAL(38, 0));

Earlier builds silently produced a 39-digit value typed DECIMAL(38, _) (which then truncated on storage / read), so any existing DECIMAL(38, _) accumulators that previously appeared to work may now surface as overflow errors — widen the operands' scale downward (e.g. cast to DECIMAL(38, 2) and accept loss of precision) or rescale into a smaller decimal type before summing.

DECIMAL × integer widening

Multiplying DECIMAL(p, s) by an integer column promotes the integer to DECIMAL(int_digits, 0) before the multiply, and the output type is DECIMAL(p + int_digits + 1, s). The integer's int_digits is sized by the input type:

Integer type	int_digits
TINYINT (Int8)	3
SMALLINT (Int16)	5
INT (Int32)	10
BIGINT (Int64)	19

-- DECIMAL(7, 2) * INT  →  DECIMAL(7 + 10 + 1, 2) = DECIMAL(18, 2)
SELECT typeof(CAST(1.50 AS DECIMAL(7, 2)) * CAST(3 AS INT));
-- 'Decimal128(18, 2)'

Earlier builds declared the output as DECIMAL(p, s) (same as the left operand), which silently NULLed every product whose runtime value overflowed that narrower target. If you previously cast the result back down explicitly, you can drop the cast — the new output type is wide enough to hold the product without loss.

DECIMAL / integer unification in CASE / COALESCE / GREATEST / UNION

When an integer branch is unified with a small-precision decimal literal — in CASE / IF / IIF, COALESCE, GREATEST / LEAST, or a UNION / UNION ALL column — the common DECIMAL type is widened to hold the integer's value range plus the decimal's scale (using the same int_digits table above). For example unifying INT with DECIMAL(2, 1) yields DECIMAL(11, 1), not DECIMAL(2, 1):

-- 10.0  (was silently NULL — the integer 10 didn't fit in the
--        DECIMAL(2,1) max of 9.9 inferred from the 1.5 literal)
SELECT CASE WHEN true THEN 10 ELSE 1.5 END;

-- 100.0, 1.5
SELECT GREATEST(100, 1.5), LEAST(100, 1.5);

-- 1.5, 100.0  (was a hard "100.0 is too large for DECIMAL(2,1)" error)
SELECT 100 AS x UNION ALL SELECT 1.5 ORDER BY x;

This regression surfaced after unquoted decimal literals began promoting to Decimal128 (so 1.5 is DECIMAL(2,1) rather than a float); the unification rule now widens precision so the integer branch fits.

TIME ± INTERVAL is strict

Adding or subtracting an INTERVAL from a TIME value rejects any calendar component (MONTH / YEAR / DAY) — TIME has no date anchor — and errors on a sub-day result that falls outside [00:00:00, 24:00:00) rather than silently wrapping modulo 24h:

SELECT TIME '12:00:00' + INTERVAL '3 hours';   -- 15:00:00 (OK, in range)
SELECT TIME '23:00:00' + INTERVAL '2 hours';   -- Error: out of range
SELECT TIME '12:00:00' + INTERVAL '1 day';     -- Error: day unit on TIME

PostgreSQL raises on these shapes as well. Cast the TIME to a TIMESTAMP first if calendar-day wrapping is intended.

Typed Literals

-- Date and timestamp literals
DATE '2024-01-15'
TIMESTAMP '2024-01-15 10:30:00'
TIMESTAMP '2024-01-15T10:30:00'        -- ISO 'T' separator
TIMESTAMP '2024-01-15 10:30:00Z'       -- UTC marker
TIMESTAMP '2024-01-15 10:30:00+05:30'  -- offset, converted to UTC
TIMESTAMP '2024-01-15 10:30:00-08:00'  -- negative offset
TIMESTAMP '2024-01-15 10:30:00+0530'   -- compact offset
TIMESTAMP '2024-01-15'                 -- date-only (midnight)
TIME '14:30:00'

-- Interval (expression-only, not a column type)
SELECT order_date + INTERVAL '30' DAY FROM orders;
SELECT created_at - INTERVAL '1' MONTH FROM users;
SELECT ts + INTERVAL '2 hours 30 minutes' FROM events;

-- Array literals
ARRAY[1, 2, 3]
['red', 'blue', 'green']

-- Map literals
MAP {'key1': 'value1', 'key2': 'value2'}

-- Struct literals
{'name': 'Alice', 'age': 30}

-- Binary hex literals
X'48454C4C4F'

PostgreSQL-style escape strings

Prefixing a string literal with E (or e) enables backslash escape interpretation, matching PostgreSQL E'...' syntax. Without the prefix, backslashes are literal characters.

Escape	Meaning
\n / \t / \r / \b / \f / \\ / \' / \"	Standard C-style escapes
\xHH	One byte by hex code (\x41 → 'A')
\uHHHH	Unicode codepoint, 4 hex digits (é → 'é')
\UHHHHHHHH	Unicode codepoint, 8 hex digits

SELECT E'tab\there\nnewline';     -- 'tab<TAB>here<NL>newline'
SELECT E'éclair';            -- 'éclair'
SELECT E'\U0001F600 hi';          -- '😀 hi'
SELECT 'é';                  -- literal 7-char string 'é' (no E prefix)

The \u / \x / \U forms were silently passed through as literal text in earlier builds even though the docstring advertised them; they now decode at parse time.

Interval Units

INTERVAL is a first-class value type (Interval(MonthDayNano) in Arrow), valid in expressions and as the result of timestamp / time subtraction. It is not yet supported as a column type in CREATE TABLE.

Supported units: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND (and their plural forms in compound strings, e.g. INTERVAL '2 hours 30 minutes').

SELECT typeof(INTERVAL '1 day');                    -- 'Interval(MonthDayNano)'
SELECT typeof(INTERVAL '2 hours 30 minutes');       -- 'Interval(MonthDayNano)'
SELECT typeof(INTERVAL '1 year 6 months');          -- 'Interval(MonthDayNano)'

The packed (months, days, nanos) representation preserves calendar semantics — INTERVAL '1 month' added to DATE '2024-01-31' still lands on '2024-02-29' (last-day-of-month carry), and INTERVAL '1 day' across a DST boundary stays a calendar day rather than collapsing to 23 or 25 hours of nanoseconds.

INTERVAL-vs-INTERVAL arithmetic and comparisons are rejected at bind time; convert to a numeric quantity (e.g. via EXTRACT) before combining intervals.

DATE + INTERVAL promotion

Adding (or subtracting) an INTERVAL to a DATE produces a DATE when the interval is a whole-day multiple, but promotes to TIMESTAMP when the interval has any sub-day component (hours, minutes, seconds). This matches Postgres.

SELECT DATE '2024-01-15' + INTERVAL '10 days';      -- DATE '2024-01-25'
SELECT DATE '2024-01-15' + INTERVAL '90 minutes';   -- TIMESTAMP '2024-01-15 01:30:00'

If you specifically want a DATE result with sub-day intervals, truncate explicitly: DATE_TRUNC('day', date_col + INTERVAL '90 minutes').

Date / timestamp difference result types

Operation	Result type	Notes
DATE - DATE	INT (Int32)	Whole-day count. Negative when the right side is later.
TIMESTAMP - TIMESTAMP	INTERVAL (Interval(MonthDayNano))	Wall-clock difference down to microseconds. Display format: "1 day 02:00:00", "-1 day -12:00:00", etc.
TIMESTAMP - DATE / DATE - TIMESTAMP	INTERVAL	Same packed representation and display format.
TIME - TIME	INTERVAL	Clock-style. Display format: "06:15:15", "-06:00:00".

DATE - DATE returns a plain integer day count rather than an interval — this is the count callers want for "how many days apart". For every other difference shape, the result is a native Interval(MonthDayNano) value, so downstream arithmetic, comparisons against another interval (rejected at bind time — see above), and EXTRACT all operate on the interval's components directly rather than going through string round-trips.

SELECT TIME '14:30:00' - TIME '08:14:45';                  -- '06:15:15'
SELECT TIME '08:00:00' - TIME '14:00:00';                  -- '-06:00:00'
SELECT typeof(TIME '10:00:00' - TIME '09:00:00');          -- 'Interval(MonthDayNano)'
SELECT typeof(TIMESTAMP '2024-01-02 10:00:00'
            - TIMESTAMP '2024-01-01 08:00:00');            -- 'Interval(MonthDayNano)'
SELECT typeof(DATE '2024-01-10' - DATE '2024-01-01');      -- 'Int32'

The displayed string format on the wire is unchanged from earlier surrogate-as-VARCHAR builds — clients that previously read these columns as text still receive the same characters. The difference is structural: the column is now a native interval, so it composes with the rest of the temporal type system.

Use DATE_DIFF(unit, a, b) if you need a difference expressed as a plain integer count of units (e.g. DATE_DIFF('hour', start, end)) rather than as a packed interval.