Giorgi
Timestamp format errors after 1.2.0
After switching from v1.1.1 to v1.2.1, I started getting the following errors when inserting with timestamp fields, seemingly due to an incorrect format being used for DateTime (and DateTimeOffset) values when using the syntax insert into tbl by name select flds.
With timestamptz:
DuckDB.NET.Data.DuckDBException : Conversion Error: timestamp field value "2022-04-05 6:15:17 p.m." has a timestamp that is not UTC.
Use the TIMESTAMPTZ type with the ICU extension loaded to handle non-UTC timestamps.
With timestamp:
invalid timestamp field format: "2017-06-15 6:00:15 a.m.", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])
The error doesn't occur when using the syntax insert into tbl (a,b) values ($a,$b).
The following diff causes the TimestampTests.InsertAndQueryTest test to fail:
modified DuckDB.NET.Test/Parameters/TimestampTests.cs
@@ -113,6 +113,10 @@ public class TimestampTests(DuckDBDatabaseFixture db) : DuckDBTestBase(db)
Command.Parameters.Clear();
Command.CommandText = "SELECT * FROM TimestampTestTable LIMIT 1;";
+ Command.CommandText = "INSERT INTO TimestampTestTable by name select a: 42, b: ?;";
+ Command.Parameters.Add(new DuckDBParameter(expectedValue));
+ Command.ExecuteNonQuery();
+
var reader = Command.ExecuteReader();
reader.Read();
I couldn't tell for sure if this is an error with this library or duckdb's native library, apologies if I got it wrong
INSERT INTO TimestampTestTable select a: 42, b: ?; also fails with the same error
It's the same error for any syntax when I use the format given in the exception
D prepare ts1 as INSERT INTO TimestampTestTable (a, b) VALUES (42, $ts);
D execute ts1(ts := '2017-06-15 6:00:15 a.m.');
Conversion Error:
invalid timestamp field format: "2017-06-15 6:00:15 a.m.", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])
D prepare ts1 as INSERT INTO TimestampTestTable (a, b) select 42, $ts;
D execute ts1(ts := '2017-06-15 6:00:15 a.m.');
Conversion Error:
invalid timestamp field format: "2017-06-15 6:00:15 a.m.", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])
Somehow the duckdb.net/duckdb c api is using a valid date format for the values syntax but not the select syntax
If it happens in the cli too, it's not this library issue. You should open an issue at the DuckDB repository
I think the exception is expected behaviour in the cli because the date is being provided in an invalid format
The reason I thought it might be this library is because, based on the exception, it seems like somewhere in the pipeline the DateTime object is getting converted to the invalid string format in the exception (2017-06-15 6:00:15 a.m.)
What happens if you use the same values in the CLI and in the code? Can you post a minimal, reproducible example showing the failure?
What happens if you use the same values in the CLI and in the code?
In the cli sessions I pasted above I tried to do this by using the date string from the exception thrown in the code, which I assume to be the value the code is providing to duckdb
Can you post a minimal, reproducible example showing the failure?
I think the diff for TimestampTests.cs in my top comment is a good reproduction, on my machine it fails the test with the exception I'm describing