read_csv() is not understanding some timestamp formats

Start by running the provided Python read_csv() reproduction with DuckDB 1.5.2 and its timestampformat '%Y-%m-%dT%H:%MZ'. Compare it with the seconds-containing example and the R implementation; done means the four timestamp fields parse instead of becoming NaT while the existing seconds format still works.

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