Support for `'infinity'` date and timestamp

[rynoV]

Currently using 'infinity'::date/timestamp looks to be unsupported, with no easy way to use infinity as a parameter value or to read it from query results. For parameters, closest I got was passing in a string parameter and casting it within the query, and for reading query results I can get a DuckDBDateOnly value for infinities, but for timestamps a duckdb::ConversionException is thrown from duckdb_from_timestamp. Generally the duckdb_from/to_date/timestamp functions don't seem to support infinity, and the python client special-cases infinity and avoids calling these conversion functions on infinity values. Based on https://github.com/duckdb/duckdb/issues/10138, client libraries can't avoid special casing.

Would you accept a PR for support of these values? If so I think the design could be

• reader.GetDateTime/GetFieldValue<DateTime/Only> continue to throw exceptions for infinity values. I think this makes sense to avoid a breaking change and because duckdb seems to support the entire range of DateTime/Only values, so I don't see an unambiguous way to represent +/-infinity with these types
• To read infinities, it would instead be necessary to read a DuckDBDateOnly or DuckDBTimestamp. Then we can provide helpers to check for the infinity constants, and the existing conversion methods remain to convert to DateTime/Only once infinity is handled
• Since the duckdb_from/to_date/timestamp conversion functions sometimes throw for infinities, we would likely hardcode DuckDBDateOnly and DuckDBTimestamp values to represent infinity, and add methods wrap these to convert to/from DuckDBDate/DuckDBTimestampStruct while special casing the infinity values
  • Since DuckDBDateOnly can directly represent the infinity values from c++ we can use that as the constant
  • DuckDBTimestamp can't represent the infinity value, so the constant could just use the DuckDBDateOnly infinity constant and a max time value

[Giorgi]

How does Npgsql deal with infinity date(time)s?

[rynoV]

https://www.npgsql.org/doc/types/datetime.html?q=infinity#infinity-values

Looks like their default is to convert to DateTime/Only.Max/MinValue, and it can be opted out of with an AppContext switch

I've implemented the plan I described above in my fork here, this is the reading logic which could be updated to return Max/MinValue based on a flag:

https://github.com/Auspice-Capital/DuckDB.NET/blob/06d900586d61f5b08c55f8eba4f2f2b7d155192f/DuckDB.NET.Data/DataChunk/Reader/DateTimeVectorDataReader.cs#L33-L47

Then we could also add handling in the writer to convert Max/MinValue to +/-infinity.

I wouldn't personally use the Max/MinValue representation and would like to keep the option of DuckDBDateOnly having special sentinel values to avoid ambiguity

[Giorgi]

Ok, open a PR and I'll try to review it. We can skip Min/MaxValue for now if that's additional code.