Parquet reader truncates INT96 timestamps to microseconds; no way to read their nanosecond part

What happens?

read_parquet types every INT96 column as TIMESTAMP (microseconds), so the sub-microsecond digits the file stores are silently dropped. INT96 stores a Julian day plus nanoseconds of the day, and it is still what Spark, Hive, Impala and SQL Server (CREATE EXTERNAL TABLE AS SELECT with datetime2(7) / time(7)) write by default, so round-tripping their data through DuckDB changes values without a warning.

There is currently no way to opt into the full precision:

• casting the column to TIMESTAMP_NS happens after the value was already read at microsecond precision;
• the schema parameter of read_parquet is keyed by field id, which these writers do not emit, and it also casts after the read;
• no setting or read_parquet parameter changes how INT96 is typed.

What makes this look like a small gap rather than a design limit: the Parquet extension already has a nanosecond INT96 path. ImpalaTimestampToTimestampNS exists in parquet_timestamp.cpp, and column_reader.cpp creates a CallbackColumnReader<Int96, timestamp_ns_t, ImpalaTimestampToTimestampNS> when the column's logical type is TIMESTAMP_NS. However, ParquetReader::DeriveLogicalType always returns LogicalType::TIMESTAMP for Type::INT96, so that reader is never reached:

• https://github.com/duckdb/duckdb/blob/7e886f44428e90c8379d4d34e2afb866108ff079/extension/parquet/parquet_reader.cpp#L594-L596
• https://github.com/duckdb/duckdb/blob/7e886f44428e90c8379d4d34e2afb866108ff079/extension/parquet/column_reader.cpp#L1063-L1064
• https://github.com/duckdb/duckdb/blob/7e886f44428e90c8379d4d34e2afb866108ff079/extension/parquet/parquet_timestamp.cpp#L30-L39

By contrast, an INT64 TIMESTAMP(NANOS) column is already read as TIMESTAMP_NS with all nine digits (see the last step below), so the behaviour differs only by the physical encoding of the same logical value.

Keeping TIMESTAMP as the default makes sense: TIMESTAMP_NS only covers 1677-09-21 to 2262-04-11, while INT96 files commonly hold values such as 0001-01-01 or 9999-12-31. A possible opt-in, purely as a suggestion:

• a read_parquet option (for example int96_as := 'timestamp_ns') that makes DeriveLogicalType return TIMESTAMP_NS and so reaches the existing reader, with out-of-range values raising a conversion error; and/or
• int96_as := 'blob' exposing the raw 12 bytes, so callers that need the full 0001-9999 range at nanosecond precision can decode the value themselves.

To Reproduce

The file is written with pyarrow, because DuckDB itself writes INT96 only from microsecond timestamps.

import duckdb
import pyarrow as pa
import pyarrow.parquet as pq

values = [
    "1970-01-02 00:00:00.000000001",
    "2026-09-16 10:20:30.123456789",
    "2200-12-31 23:59:59.987654321",
]
table = pa.table({"ts": pa.array(values).cast(pa.timestamp("ns"))})
pq.write_table(table, "int96_ns.parquet", use_deprecated_int96_timestamps=True)

print(pq.ParquetFile("int96_ns.parquet").schema.column(0).physical_type)
print(pq.read_table("int96_ns.parquet").column("ts").cast(pa.string()).to_pylist())

con = duckdb.connect()
print(con.sql("DESCRIBE SELECT * FROM read_parquet('int96_ns.parquet')").fetchall())
print(con.sql("SELECT ts::VARCHAR FROM read_parquet('int96_ns.parquet')").fetchall())
print(con.sql("SELECT ts::TIMESTAMP_NS::VARCHAR FROM read_parquet('int96_ns.parquet')").fetchall())

# Contrast: the same value as INT64 TIMESTAMP(NANOS) keeps all nine digits.
pq.write_table(table, "int64_ns.parquet")
print(con.sql("DESCRIBE SELECT * FROM read_parquet('int64_ns.parquet')").fetchall())
print(con.sql("SELECT ts::VARCHAR FROM read_parquet('int64_ns.parquet')").fetchall())

Output (identical on 1.5.5 and 2.0.0.dev2609121639):

INT96
['1970-01-02 00:00:00.000000001', '2026-09-16 10:20:30.123456789', '2200-12-31 23:59:59.987654321']
[('ts', 'TIMESTAMP', 'YES', None, None, None)]
[('1970-01-02 00:00:00',), ('2026-09-16 10:20:30.123456',), ('2200-12-31 23:59:59.987654',)]
[('1970-01-02 00:00:00',), ('2026-09-16 10:20:30.123456',), ('2200-12-31 23:59:59.987654',)]
[('ts', 'TIMESTAMP_NS', 'YES', None, None, None)]
[('1970-01-02 00:00:00.000000001',), ('2026-09-16 10:20:30.123456789',), ('2200-12-31 23:59:59.987654321',)]

pyarrow reads the INT96 file with all nine digits; DuckDB returns six. The same truncation happens with the DuckDB CLI and with files written by SQL Server 2025 CETAS (datetime2(7) value 9999-12-31 23:59:59.9999999 reads as 23:59:59.999999).

OS:

Windows 10 Enterprise 22H2 (10.0.19045), x86_64

DuckDB Version:

1.5.5; also reproduced on 2.0.0.dev2609121639 (main @ 7e886f4)

DuckDB Client:

Python (pyarrow 25.0.1); also observed through DuckDB.NET and the CLI

Hardware:

(not performance-related)

Full Name:

Kamil Kliczbor

Affiliation:

Klikamsoft Kamil Kliczbor

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set