from_dataset fails on object-dtype variables (cftime times, strings) and out-of-ns-range datetimes

[ghostiee-11]

Summary

XarrayContext.from_dataset (via read_xarray_table -> df.py) fails to register several common NetCDF/xarray datasets, because it derives the Arrow schema and block statistics from numpy dtypes alone:

1. Object-dtype variables/coordinates are rejected. _parse_schema calls pa.from_numpy_dtype(var.dtype), which raises ArrowNotImplementedError: Unsupported numpy type 17 for object dtype. That is exactly what a cftime time coordinate (any non-standard calendar such as noleap/360_day) or a string variable produces.

2. Datetimes outside the datetime64[ns] range overflow. _block_metadata coerces datetime min/max to nanoseconds with int(pd.Timestamp(min_val).value), so coordinates with early/late dates (paleoclimate records, or anything before ~1678 / after ~2262) raise OverflowError, even at us resolution.

Reproduce

Object dtype (string variable shown; a cftime time coordinate produces the same Unsupported numpy type 17):

import numpy as np, xarray as xr
from xarray_sql import XarrayContext
ds = xr.Dataset({"label": (["x"], np.array(["a", "b"], dtype=object))}, coords={"x": [1, 2]})
XarrayContext().from_dataset("t", ds, chunks={"x": 2})
# pyarrow.lib.ArrowNotImplementedError: Unsupported numpy type 17

Out-of-ns-range datetimes:

import numpy as np, xarray as xr
from xarray_sql import XarrayContext
times = xr.date_range("0001-01-01", periods=3, freq="100YS", use_cftime=True).to_datetimeindex(time_unit="us")
ds = xr.Dataset({"v": (["time"], np.arange(3.0))}, coords={"time": times})
XarrayContext().from_dataset("t", ds, chunks={"time": 2})
# OverflowError: Cannot convert Timestamp to nanoseconds without overflow

Real-world impact

NetCDF/climate files routinely use non-standard calendars, so xarray decodes their time coordinate to cftime (object dtype) and (1) fires immediately. Long or paleoclimate records additionally hit (2). Between them a large class of real .nc files cannot be registered.

Suggested fixes

• _parse_schema: handle object/datetime dtypes rather than passing them straight to pa.from_numpy_dtype. Infer from data (pa.array(...).type), map cftime/datetime to a pa.timestamp, strings to pa.string().
• _block_metadata: use the array's native unit instead of forcing ns, e.g. min_val.asm8.view("i8") with the matching timestamp_<unit> tag (the OverflowError message itself suggests .asm8.view('i8')).

Environment

xarray-sql 0.2.1, pyarrow 23.0.1, xarray 2026.2.0, pandas 3.0.1, numpy 2.4.2, Python 3.14.3

Found via HoloViz Lumen's XArraySQLSource, where uploading a .nc with a non-standard-calendar time coordinate hits this. Repros verified locally.

[alxmrs]

cftime support was added in 0.2.2: https://github.com/xqlsystems/xarray-sql/releases/tag/v0.2.2

Can you try to recreate this but in 0.3.1?

[ghostiee-11]

confirmed on 0.3.1 (main). the cftime schema part is fixed, thanks. but two things from the report still repro:

1. object-dtype vars/coords that aren't cftime (a string label var, a string coord) still hit _parse_schema -> pa.from_numpy_dtype -> ArrowNotImplementedError: Unsupported numpy type 17
2. out-of-ns datetimes still overflow in _block_metadata at int(pd.Timestamp(min_val).value)

repros on 0.3.1:

# 1 - string var
ds = xr.Dataset({"label": (["x"], np.array(["a","b"], dtype=object))}, coords={"x":[1,2]})
XarrayContext().from_dataset("t", ds, chunks={"x":2})   # Unsupported numpy type 17

# 2 - out-of-ns datetime
times = xr.date_range("0001-01-01", periods=3, freq="100YS", use_cftime=True).to_datetimeindex(time_unit="us")
ds = xr.Dataset({"v": (["time"], np.arange(3.0))}, coords={"time": times})
XarrayContext().from_dataset("t", ds, chunks={"time":2})  # OverflowError

both are pure-python in df.py. happy to send a PR.

[alxmrs]

Thanks! I'd love to see a fix for this.

[ghostiee-11]

Suree!!