read_csv() is not understanding some timestamp formats #442

Activity

1. cklunch

   added

   needs triage

   on May 4, 2026
2. aleks-drozy commented on Aug 2, 2026

   

   aleks-drozy

   on Aug 2, 2026

   More actions

   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.
3. cklunch commented on Aug 17, 2026

   

   cklunch

   on Aug 17, 2026

   Author

   More actions

   @aleks-drozy Thank you for looking into this! I have created an issue in the DuckDB repo. duckdb/duckdb#24820
4. evertlammerts commented on Sep 22, 2026

   

   evertlammerts

   on Sep 22, 2026

   Last edited by evertlammerts

   Member

   More actions

   Confirmed that this is now solved. With the test.csv you gave in duckdb/duckdb#24820:

   ❯ cat test.csv
   ───────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
          │ File: test.csv
   ───────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
      1   │ id,startDate
      2   │ 1,2021-08-06T13:18:21Z
   ───────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   

   Script:

   import duckdb
   
   schema = {"id": "VARCHAR", "startDate": "TIMESTAMPTZ"}
   print(duckdb.sql("SELECT * FROM read_csv('test.csv', header=true, columns=$schema, timestampformat='%Y-%m-%dT%H:%MZ')", params={"schema": schema}).fetchall())
   

   Output:

   [('1', datetime.datetime(2021, 8, 6, 15, 18, 21, tzinfo=<DstTzInfo 'Europe/Amsterdam' CEST+2:00:00 DST>))]
   
5. evertlammerts

   closed this as completedon Sep 22, 2026