Implementing better System.TimeSpan support so member translations make sense

[lauxjpn]

trafficstars

Nope ;-) https://docs.microsoft.com/en-us/dotnet/api/system.timespan.divide?view=netcore-3.1

Ah, makes sense that it returns a TimeSpan and not a DateTime. We currently don't translate any TimeSpan methods in Pomelo (I think that is the same situation in other providers as well).

Technically this should be possible, but we would need to reevaluate our general strategy here, because we currently use TimeSpan to represent the TIME(n) store type, that is limited to -838 < x < +838 hours. So for member translation to make more general sense, we might also want to optionally (at least internally) map DATETIME(n) values to System.TimeSpan when needed (basically making System.TimeSpan interchangeable with System.DateTime).

This looks like a good general enhancement to EF Core, but would need a bit more thinking and testing.

Originally posted by @lauxjpn in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1137#issuecomment-675378961

[lauxjpn]

This might work, because when the user tries to use members/methods of an System.TimeSpan on a DATETIME(n) column, we would know that the current DATETIME(n) value should be interpreted as an interval and not as an absolute date/time. The type mapping of the result would take care of returning a TimeSpan in the end, but MySqlConnector would need to add support for optionally mapping a DATETIME(n) column to System.TimeSpan.

[lauxjpn]

The first half of this, translating TimeSpan members to SQL equivalents, has been implemented with #1191. The second half, which is checking the type mapping and using DATETIME(n) instead of TIME(n), still needs to be done.