read_csv() is not understanding some timestamp formats

[cklunch]

What happens?

I am getting NaT (not a timestamp) for time stamps in format %Y-%m-%dT%H:%MZ in files read using read_csv().

To Reproduce

For example:

import duckdb
import pandas as pd

url = ['https://storage.googleapis.com/neon-publication/NEON.DOM.SITE.DP4.00132.001/TOOK/20210801T000000--20210901T000000/expanded/NEON.D18.TOOK.DP4.00132.001.bat_sonarRecord.2021-08.expanded.20251206T025505Z.csv']
schema = {'uid': 'VARCHAR',
 'domainID': 'VARCHAR',
 'siteID': 'VARCHAR',
 'namedLocation': 'VARCHAR',
 'startDate': 'TIMESTAMPTZ',
 'endDate': 'TIMESTAMPTZ',
 'eventID': 'VARCHAR',
 'sonarRecordingStartTime': 'TIMESTAMPTZ',
 'sonarRecordingStopTime': 'TIMESTAMPTZ',
 'sonarRecordingNumber': 'VARCHAR',
 'remarks': 'VARCHAR',
 'dataQF': 'VARCHAR'}
tformat = "'%Y-%m-%dT%H:%MZ'"
dat = duckdb.sql(f"SELECT * FROM read_csv({url}, header=true, columns={schema}, timestampformat={tformat})")
d = dat.df()
d[0:3]

uid	domainID	siteID	namedLocation	startDate	endDate	eventID	sonarRecordingStartTime	sonarRecordingStopTime	sonarRecordingNumber	remarks	dataQF
b72ef2b3-45f0-492f-85a2-03f7c65c1cb6	D18	TOOK	TOOK	NaT	NaT	TOOK.2021	NaT	NaT	TOOK_20210806_131828.dt4	None	None
6d46d5cf-124e-430e-b1ba-fb5ebb1a01f0	D18	TOOK	TOOK	NaT	NaT	TOOK.2021	NaT	NaT	TOOK_20210806_103707.dt4	None	None
a61d20e3-b179-4c18-9eb0-124afc04e1d5	D18	TOOK	TOOK	NaT	NaT	TOOK.2021	NaT	NaT	TOOK_20210804_164251.dt4	None	None

The four date-time fields are all empty (NaT) in the output here.

I am not seeing this problem on dates without times, or on timestamps that include seconds. This example includes seconds and correctly parses the time stamps:

url = ['https://storage.googleapis.com/neon-publication/NEON.DOM.SITE.DP1.00005.001/MOAB/20250501T000000--20250601T000000/basic/NEON.D13.MOAB.DP1.00005.001.000.010.030.IRBT_30_minute.2025-05.basic.20250715T232027Z.csv']
schema = {'startDateTime': 'TIMESTAMPTZ',
 'endDateTime': 'TIMESTAMPTZ',
 'bioTempMean': 'DOUBLE',
 'bioTempMinimum': 'DOUBLE',
 'bioTempMaximum': 'DOUBLE',
 'bioTempVariance': 'DOUBLE',
 'bioTempNumPts': 'DOUBLE',
 'bioTempExpUncert': 'DOUBLE',
 'bioTempStdErMean': 'DOUBLE',
 'finalQF': 'BIGINT'}
tformat = "'%Y-%m-%dT%H:%M:%SZ'"
dat = duckdb.sql(f"SELECT * FROM read_csv({url}, header=true, columns={schema}, timestampformat={tformat})")
d = dat.df()
d[0:3]

startDateTime	endDateTime	bioTempMean	bioTempMinimum	bioTempMaximum	bioTempVariance	bioTempNumPts	bioTempExpUncert	bioTempStdErMean	finalQF
2025-04-30 18:00:00-06:00	2025-04-30 18:30:00-06:00	20.78	19.35	22.14	0.80	1800.0	0.54	0.02	0
2025-04-30 18:30:00-06:00	2025-04-30 19:00:00-06:00	15.47	10.56	19.35	10.81	1800.0	0.57	0.08	0
2025-04-30 19:00:00-06:00	2025-04-30 19:30:00-06:00	12.71	11.77	13.53	0.30	1800.0	0.54	0.01	0

I have also been able to correctly parse time stamps in format %Y-%m-%dT%H:%MZ using the R implementation of duckdb, so I think this is specific to the Python package.

OS:

macOS 26.4.1

DuckDB Package Version:

1.5.2

Python Version:

3.13

Full Name:

Claire Lunch

Affiliation:

National Ecological Observatory Network

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

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

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[aleks-drozy]

Dug into this and narrowed down the exact trigger, since your two examples differ in two ways at once (seconds in the format, and it's not obvious which one matters).

Reproduced directly (DuckDB 1.5.5) with a minimal CSV:

import duckdb
# id,startDate
# 1,2021-08-06T13:18Z
schema = {"id": "VARCHAR", "startDate": "TIMESTAMPTZ"}
duckdb.sql("SELECT * FROM read_csv('test.csv', header=true, columns=$schema, "
           "timestampformat='%Y-%m-%dT%H:%MZ')", params={"schema": schema}).fetchall()
# [('1', None)]  -- silently NULL, no error

Isolated it to the format lacking %S combined with a TIMESTAMPTZ column specifically, not the format string alone:

• timestampformat='%Y-%m-%dT%H:%MZ' (no seconds) + TIMESTAMPTZ column → silently NULL
• timestampformat='%Y-%m-%dT%H:%M:%SZ' (with seconds) + TIMESTAMPTZ column → works
• timestampformat='%Y-%m-%dT%H:%MZ' (no seconds) + plain TIMESTAMP column (not TZ) → works fine

So plain strptime('2021-08-06T13:18Z', '%Y-%m-%dT%H:%MZ') also works correctly on its own - the bug is specific to read_csv's TIMESTAMPTZ casting path when the configured format has no seconds component, not to the format parser in general.

Given plain TIMESTAMP with the same format/input works correctly, this looks like it lives in the CSV reader's TIMESTAMPTZ-specific cast/parsing logic in core DuckDB, which this repo vendors as a submodule (external/duckdb) rather than owns - might be better reported/fixed against duckdb/duckdb directly rather than here, if that's not already where triage for this class of bug happens. Happy to be corrected if duckdb-python does own this code path.

[cklunch]

@aleks-drozy Thank you for looking into this! I have created an issue in the DuckDB repo. duckdb/duckdb#24820