.Net: [MEVD] Support `DateOnly`, `TimeOnly`, `DateTimeOffset` and `DateTime` types for DBs that support this

[westey-m]

Many DBs especially SQL based DBs support separate date and time types.
We should do a pass on all our connectors and ensure that they support DateOnly and TimeOnly on those DBs where this is valuable.
We should also make sure that all our databases support dates with and without timezones in a consistent way.

[westey-m]

Here is a comparison of the different databases and what datetime handling they support:

DB	DateType	DateFormat	Link
Azure AI Search	DateTimeOffset	OData V4 format	https://learn.microsoft.com/en-us/rest/api/searchservice/supported-data-types
CosmosDBMongoDB	BSON Date which is DateTime	64Bit Int ms after Unix Epoch in UTC. Driver only supports DateTime and converts unspecified/local to universal	https://www.mongodb.com/docs/manual/reference/method/Date/
MongoDB	BSON Date which is DateTime	64Bit Int ms after Unix Epoch in UTC. Driver only supports DateTime and converts unspecified/local to universal	https://www.mongodb.com/docs/manual/reference/method/Date/
Postgres	DateTime, Date & Time (UTC and unspecified); no DateTimeOffset		https://www.postgresql.org/docs/current/datatype-datetime.html, https://www.npgsql.org/doc/types/datetime.html
Qdrant	RFC 3339 string	Supports DateOnly (midnight assumed), DateTime with offset, DateTime without offset(UTC assumed)	https://qdrant.tech/documentation/concepts/payload/#datetime
SqlServer	date, datetime, datetime2, datetimeoffset, smalldatetime		https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-and-time-types
Weaviate	RFC 3339 string	The timestamp includes the time and an offset.	https://weaviate.io/developers/weaviate/config-refs/datatypes#date
-	-	-	-
CosmosDBNoSQL	Not Supported	Recommends an ISO 8601 UTC string	https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/working-with-dates
Chroma	Not Supported		https://cookbook.chromadb.dev/core/concepts/#metadata
Milvus	Not Supported		https://milvus.io/docs/schema.md#Add-Scalar-Fields
Pinecone	Not Supported		https://docs.pinecone.io/guides/data/understanding-metadata#supported-metadata-types
Redis	Not Supported
Sqlite	Not supported	Can convert to text (ISO8601), REAL (julianday) or INTEGER (UNIX time, UTC)	https://www.sqlitetutorial.net/sqlite-date/

Kind=Local Roundtripping with DateTime is possible, if the database supports storing offset. It accurately represents the same absolute time in the different offsets involved. E.g. If someone stores a local DateTime with a time of 05:00 and an offset of 01:00, and it is read in an offset of 02:00, the time will be accurately adjusted to 06:00 and the offset to 02:00.

The tricky part is that the user may actually have wanted to just store 05:00 and get out 05:00 regardless of the local time zone offset. If the user was able to select a storage type without an offset, this could of course work, but currently we automatically pick a storage type based on the CLR type.

I think we have a few options therefore:

1. Support as many types as possible for each DB.
   1. If a DB always stores datetime with offset we support DateOnly, DateTime and DateTimeOffset.
   2. If a DB only supports storing dates as UTC, we support DateOnly, DateTime and DateTimeOffset with conversions to UTC and throws for Unspecified as required.
   3. If a DB supports both with an offset and without, we store DateOnly and DateTime without and DateTimeOffset with.
2. Support only the most closely related types for each DB.
   1. If a DB always stores datetime with offset we support only DateTimeOffset.
   2. If a DB only supports storing dates as UTC, we support DateTime Kind=UTC and DateTimeOffset only.
   3. If a DB supports both with an offset and without, we store DateOnly and DateTime without and DateTimeOffset with.

The first option (and the second to some extent) has risk that when a DB starts supporting a new type, we won't have a unique CLR type to map to it, so I think it's important to have a clear strategy around this. Here's a fairly strict strawman proposal:

Storage Type	CLR Type
UTC DateTime	DateTimeOffset (Conceptually similar to DateTime with Offset)
Date Only	DateOnly
DateTime With Offset	DateTimeOffset
DateTime Without Offset	DateTime (Kind=Unspecified, throw for any other)
Time Only With Offset	Not Supported?
Time Only Without Offset	TimeOnly