SQLite: Translate TimeSpan members

[bricelam]

We can enable these by registering two UDFs on the connection:

CreateFunction("ef_days", (TimeSpan value) => value.TotalDays);
CreateFunction("ef_timespan", (double value) => TimeSpan.FromDays(value));

The following translations are enabled.

.NET	SQL
timeSpan1 + timeSpan2	ef_timespan(ef_days($timeSpan1) + ef_days($timeSpan2))
timeSpan1 - timeSpan2	ef_timespan(ef_days($timeSpan1) - ef_days($timeSpan2))
timeSpan1 / timeSpan2	ef_days($timeSpan1) / ef_days($timeSpan2)
timeSpan / d	ef_timespan(ef_days($timeSpan) / $d)
timeSpan1 > timeSpan2	ef_days($timeSpan1) > ef_days($timeSpan2)
timeSpan1 >= timeSpan2	ef_days($timeSpan1) >= ef_days($timeSpan2)
timeSpan1 < timeSpan2	ef_days($timeSpan1) < ef_days($timeSpan2)
timeSpan1 <= timeSpan2	ef_days($timeSpan1) <= ef_days($timeSpan2)
d * timeSpan	ef_timespan($d * ef_days($timeSpan))
timeSpan * d	ef_timespan(ef_days($timeSpan) * $d)
-timeSpan	ef_timespan(-ef_days($timeSpan))
dateTime + timeSpan	datetime(julianday($dateTime) + ef_days($timeSpan))
dateTime - timeSpan	datetime(julianday($dateTime) - ef_days($timeSpan))
dateTime1 - dateTime2	ef_timespan(julianday($dateTime1) - julianday($dateTime2))
timeSpan.Days	CAST(ef_days($timeSpan) AS INTEGER)
timeSpan.Hours	ef_days($timeSpan) * 24 % 24
timeSpan.Milliseconds	ef_days(%timeSpan) * 86400000 % 1000
timeSpan.Minutes	ef_days($timeSpan) * 1440 % 60
timeSpan.Seconds	ef_days($timeSpan) * 86400 % 60
timeSpan.Ticks	CAST(ef_days($timeSpan) * 864000000000 AS INTEGER)
timeSpan.TotalDays	ef_days($timeSpan)
timeSpan.TotalHours	ef_days($timeSpan) * 24
timeSpan.TotalMilliseconds	ef_days(%timeSpan) * 86400000
timeSpan.TotalMinutes	ef_days($timeSpan) * 1440
timeSpan.TotalSeconds	ef_days($timeSpan) * 86400
timeSpan.Duration()	ef_timespan(abs(ef_days($timeSpan)))
timeSpan.Fromef_days(value)	ef_timespan($value)
TimeSpan.FromHours(value)	ef_timespan($value / 24)
TimeSpan.FromMilliseconds(value)	ef_timespan($value / 86400000)
TimeSpan.FromMinutes(value)	ef_timespan($value / 1440)
TimeSpan.FromSeconds(value)	ef_timespan($value / 86400)
TimeSpan.FromTicks(value)	ef_timespan($value / 864000000000)
Max(t => t.TimeSpan)	ef_timespan(max(ef_days(t.TimeSpan)))
Min(t => t.TimeSpan)	ef_timespan(min(ef_days(t.TimeSpan)))

Notes:

• Most .NET operators have equivalent methods to translate too
• datetime() should actually be translated as rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f'), '0'), '.')
• julianday(datetime(text, modifiers)) can reduce to julianday(text, modifiers)
• julianday(datetime(real)) can reduce to real
• ef_days(ef_timespan(real)) can reduce to real

[bricelam]

I made some good progress in bricelam:timespan, but I ran into floating-point precision issues and wanted to re-examine how we handle TimeSpan and DateTime literals and parameters on SQLite before moving forward.

Assert.Equal() Failure
Expected: 00:01:00
Actual:   00:00:59.9999964

In order to get this to round to the correct time we need to re-create the TimeSpan (and possibly DateTime) instances in various places using a constructor overload that doesn't take double.

[bricelam]

We could also consider adding a collation.

.NET	SQL
OrderBy(t => t.TimeSpan)	ORDER BY t.TimeSpan COLLATE EF_TIMESPAN
OrderByDescending(t => t.TimeSpan)	ORDER BY t.TimeSpan COLLATE EF_TIMESPAN DESC

[bricelam]

Note, we can use the mod function from #18843 (which works with non-integer values) to simplify some translations (including the one for DateTime.Millisecond) which should also avoid overflows.