TimeSpan

[bricelam]

Functions

CreateFunction("days", (TimeSpan value) => value.TotalDays);

CreateFunction("timespan", (double value) => TimeSpan.FromDays(value));

Translations

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

Notes:

• All .NET operators have equivalent methods to translate too
• datetime() should actually be translated as rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f'), '0'), '.')

[bricelam]

Aggregate functions could also use some thought.

db.TestResults.Max(r => r.ExecutionTime);

select timespan(max(days(executionTime))) from testResult;

Note: This won't be able to leverage indexes on the column.

[bricelam]

Without this, we need to ensure server evaluation is blocked: dotnet/efcore#10534 ✔️

[bricelam]

Should look into simplifying expressions too so instead of this...

timespan(days($timeSpan1) + days(timespan(days($timeSpan2) + days($timeSpan3))))

...it generates this.

timespan(days($timeSpan1) + (days($timeSpan2) + days($timeSpan3)))

It should be as simple as collapsing any timespan(days(…)) and days(timespan(…)). The same should be true for any nested datetime and julianday calls. (Filed dotnet/efcore#12059)

[bricelam]

We can add this to the main provider behind an off-by-default option. Example:

options.UseSqlite(connection, x => x.UseTimeSpan());

[bricelam]

Tracked by dotnet/efcore#18844