GitHub user nevzheng created a discussion: Support Iceberg V3 nanosecond timestamps (`timestamp_ns` / `timestamptz_ns`)
# Proposal: nanosecond-precision timestamps (Iceberg V3 `timestamp_ns` / `timestamptz_ns`) _A separate discussion from [#11929](https://github.com/apache/gravitino/discussions/11929) (variant), but part of the same effort to give Iceberg's [format-version-3 types](https://iceberg.apache.org/spec/#primitive-types) a native home in Gravitino — see that thread for the shared V3 context._ ## What Iceberg added Iceberg [format version 3](https://iceberg.apache.org/spec/#primitive-types) introduced two nanosecond-precision timestamp types alongside the existing microsecond ones: | Iceberg type | Precision | Timezone semantics | |---|---|---| | `timestamp` (V1) | microsecond (6) | naive (no zone) | | `timestamptz` (V1) | microsecond (6) | UTC-normalized instant | | **`timestamp_ns`** (V3) | **nanosecond (9)** | naive (no zone) | | **`timestamptz_ns`** (V3) | **nanosecond (9)** | UTC-normalized instant | They're stored as a signed 64-bit count of nanoseconds since `1970-01-01`, which narrows the representable window to roughly **1677-09-21 → 2262-04-11** — the same range as Arrow's nanosecond unit and pandas `datetime64[ns]`, chosen for interop. The timezone axis is identical to the existing types: `timestamptz_ns` adjusts to UTC, `timestamp_ns` does not. The Iceberg Java client we pin (**1.11.0**) already ships these as `org.apache.iceberg.types.Types.TimestampNanoType` (`TypeID.TIMESTAMP_NANO`, with `withZone()` / `withoutZone()` / `shouldAdjustToUTC()`). Today Gravitino loads such a column as an opaque `ExternalType("TIMESTAMP_NANO")`, and it can't be created through the native API. ## How we can implement it using existing Gravitino types Unlike [`variant` (#11929)](https://github.com/apache/gravitino/discussions/11929), **this needs no new type in Gravitino's unified model.** Gravitino's `TimestampType` already carries a precision in the range `[0, 12]`, so `timestamp(9)` / `timestamp_tz(9)` are already first-class, serializable types (they already round-trip through the JSON serde, the Python client, and the OpenAPI schema). The mapping is therefore confined to the Iceberg converter: - **Read** (`FromIcebergType`): `TIMESTAMP_NANO` → `Types.TimestampType.withTimeZone(9)` / `withoutTimeZone(9)`, honoring `shouldAdjustToUTC()` — instead of falling through to `ExternalType`. - **Write** (`ToIcebergType`): Gravitino `timestamp[_tz]` at precision **9** → `TimestampNanoType.withZone()` / `withoutZone()`. Precision **6** (or unset) keeps mapping to the existing microsecond `TimestampType`; any other precision still throws, since Iceberg has only these two. Iceberg has exactly two timestamp precisions; Gravitino allows `[0, 12]`. The precision policy: | Gravitino timestamp precision | Iceberg type | |---|---| | unset or `6` | `timestamp` / `timestamptz` (µs) — unchanged | | `9` | `timestamp_ns` / `timestamptz_ns` (ns) — **new** | | any other | rejected (Iceberg can't represent it) — unchanged behavior | Round-trips are lossless for precision 6 and 9. No changes to `api`, `common`, the Python client, or OpenAPI. ## Compatibility across engines A subtlety worth calling out: because `timestamp(9)` is *already* a valid Gravitino type, the other connectors don't reject it the way they reject an unknown type like `variant` — they pass it straight into their existing timestamp conversion. So the propagation question is per-connector: does that path **preserve** precision 9, **truncate** it to microseconds, or **reject** it? The table below is the engine-capability baseline that answers this. | Connector / engine (our pin) | Nanosecond timestamp? | Max precision | Version notes | Proposed Gravitino mapping | |---|---|---|---|---| | **Iceberg** | **yes** | ns (9) | `*_ns` are V3 types; client 1.11 has `TimestampNanoType` | **native `timestamp[_tz](9)` ↔ `TimestampNanoType`** (this proposal) | | Paimon 1.2 | yes | ns (9) | `TIMESTAMP(p)`, p 0–9 | native, follow-up | | Flink | yes | ns (9) | `TIMESTAMP(p)` / `TIMESTAMP_LTZ(p)`, p 0–9 | native, follow-up (verify precision preserved) | | Trino 435 | yes | **ps (12)** | variable precision since release 341 | native, follow-up (ns is a subset) | | ClickHouse | yes | ns (9) | `DateTime64(p)`, p 0–9 | native, follow-up (UTC-with-tz-attr semantics need care) | | Hive | yes | ns (9) | `TIMESTAMP` is `…SS.fffffffff`; naive, `… WITH LOCAL TIME ZONE` since 3.1 | native, follow-up | | Lance | yes | ns (9) | Arrow `Timestamp` unit ∈ {s,ms,us,ns} | native, follow-up | | OceanBase | mode-dependent | Oracle: ns (9); MySQL: µs (6) | JDBC catalog targets **MySQL mode** → µs | truncate-risk in MySQL mode → reject / document | | Spark ≤4.0 | **no** | µs (6) | ns is unreleased, flag-gated ([SPARK-57454](https://issues.apache.org/jira/browse/SPARK-57454), `spark.sql.timestampNanosTypes.enabled`) | truncate-risk → reject / document | | Doris | no | µs (6) | `DATETIME(p)`, p 0–6 | truncate-risk → reject / document | | StarRocks | no | µs (6) | sub-second `DATETIME` only since v3.3.5 | truncate-risk → reject / document | | MySQL | no | µs (6) | `DATETIME(fsp)`/`TIMESTAMP(fsp)`, fsp 0–6 | truncate-risk → reject / document | | PostgreSQL | no | µs (6) | `timestamp(p)`/`timestamptz(p)`, p 0–6 | truncate-risk → reject / document | | Hologres | no | µs (6) | PostgreSQL-compatible | truncate-risk → reject / document | The microsecond-capped group (Spark, MySQL, PostgreSQL, Doris, StarRocks, Hologres, OceanBase/MySQL) is the one that matters most for correctness: silently narrowing `timestamp(9)` → µs on write would lose the nanosecond identity on round-trip, so we'd prefer to reject (or explicitly document the narrowing) rather than truncate quietly. ## Proposed scope and commit order 1. **Iceberg mapping first** — one converter, its unit tests, and flipping the two nanosecond assertions in the existing V3 integration test from `ExternalType` to native. This is the whole of the initial PR; it's what we'd like to align on. 2. **Then each connector, one at a time** — a focused follow-up per connector, looking closely at how its existing timestamp path handles precision 9: native-map the nanosecond-capable engines (Paimon, Flink, Trino, ClickHouse, Hive, Lance); reject-or-document the microsecond-capped ones. Same map-where-it's-the-same-concept / reject-otherwise discipline used for `variant`. I feel this is a relatively simple change — we're not adding any new types to Gravitino's model, just using `timestamp(9)` that already exists — so I don't think it needs a full design doc. Happy to file one if maintainers would prefer. ## References - Iceberg spec — V3 primitive types (`timestamp_ns` / `timestamptz_ns`): https://iceberg.apache.org/spec/#primitive-types - Related Gravitino discussion — variant (#11929): https://github.com/apache/gravitino/discussions/11929 - Flink SQL data types (`TIMESTAMP(p)`, p 0–9): https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/types/ - Trino data types (`TIMESTAMP(p)`, p 0–12): https://trino.io/docs/current/language/types.html - Paimon data types (`TIMESTAMP(p)`, p 0–9): https://paimon.apache.org/docs/master/concepts/data-types/ - ClickHouse `DateTime64`: https://clickhouse.com/docs/sql-reference/data-types/datetime64 - Hive data types (`TIMESTAMP`): https://hive.apache.org/docs/latest/language/languagemanual-types/ - Spark SQL data types: https://spark.apache.org/docs/latest/sql-ref-datatypes.html — nanosecond WIP: [SPARK-57454](https://issues.apache.org/jira/browse/SPARK-57454) - Doris `DATETIME`: https://doris.apache.org/docs/sql-manual/basic-element/sql-data-types/date-time/DATETIME/ - StarRocks `DATETIME`: https://docs.starrocks.io/docs/sql-reference/data-types/date-types/DATETIME/ - MySQL fractional seconds: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html - PostgreSQL date/time types: https://www.postgresql.org/docs/current/datatype-datetime.html - OceanBase data types: https://en.oceanbase.com/docs/common-oceanbase-database-10000000001380378 - Hologres data types: https://www.alibabacloud.com/help/en/hologres/developer-reference/data-types - Arrow timestamps / Lance schema: https://arrow.apache.org/docs/python/timestamps.html · https://lance.org/format/table/schema/ GitHub link: https://github.com/apache/gravitino/discussions/11936 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected]
