SQLite: Translate TimeSpan members #18844
Description
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 asrtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f'), '0'), '.')julianday(datetime(text, modifiers))can reduce tojulianday(text, modifiers)julianday(datetime(real))can reduce torealef_days(ef_timespan(real))can reduce toreal