Support the new BCL DateOnly and TimeOnly structs for Sqlite

[roji]

New DateOnly and TimeOnly structs are being introduced to .NET 6 as alternatives to DateTime (dotnet/runtime#49036). This issue tracks supporting them in Sqlite (both ADO.NET and EF Core providers).

[bricelam]

The DateOnly translations will be the same as the current DateTime translations but using date() instead of datetime().
The TimeOfDay translations will be the same as the ones listed in issue #18844, but using time() (well actually strftime('%H:%M:%f') to preserve precision) instead of ef_timespan(), and julianday() instead of ef_days().

Additional translations:

.NET	SQL
date + time	$date || ' ' || $time

The ADO.NET provider should serialize and deserialize these to TEXT using the hh\:mm\:ss.fff custom format for TimeOfDay and yyyy\-MM\-dd for DateOnly. EF Core literals should use these same formats.

[bricelam]

On a related note, we should look into updating the DateTime, DateTimeOffset, and TimeSpan formats to use fff instead of FFFFFFF.

This would be a breaking change, but also enables us to get rid of the ugly rtrim calls in the SQL.

[mattjohnsonpint]

Why truncate precision to milliseconds?

[bricelam]

SQLite date and time functions only go to milliseconds. We could allow reading and writing values with higher precision—we do this today for DateTime. But any arithmetic operations on the server will have to truncate. (Unless we add our own UDFs, but IMHO that’s going too far to work around SQLite’s native limitations.)

[mattjohnsonpint]

Please note that the final names for these types are DateOnly and TimeOnly and that they are now merged into the main branch for the next version of .NET 6 (preview 4 likely). Please rename the title of this issue accordingly. Thanks.

[roji]

Clearing milestone to discuss doing this in 6.0.0.

[roji]

Poaching