Repository navigation
Description
Problem
PR #57 adds the ClickHouse toStartOf* family as EF.Functions extension methods. It closes #56.
Many other ClickHouse date/time functions stay unavailable from LINQ. These functions have no .NET equivalent, so an EF.Functions entry point is the correct shape for them.
This issue is different from #55. Issue #55 covers the translation of standard .NET members and methods, such as .Year and .AddDays(1). Those must translate without an EF.Functions call. This issue covers only the ClickHouse-specific functions.
Suggested work
Use the same shape as PR #57 for each function:
- an extension method on
DbFunctionsinClickHouseDateTimeDbFunctionsExtensions - a
MethodInfoentry inClickHouseDateTimeMethodTranslator - a test in
DateTimeFunctionsTranslationTests
Add the functions in this order. The order shows the expected value to users.
| # | Functions | Notes |
|---|---|---|
| 1 | dateDiff, age |
Add both. They give different results. Re-use the ClickHouseInterval enum from PR #57 for the unit. The return type is Int64, thus declare long. |
| 2 | dateTrunc |
The general form of toStartOf*. Uses the same interval enum. |
| 3 | toLastDayOfMonth, toLastDayOfWeek |
Companions to toStartOf*. The same translator can hold them. |
| 4 | toTimeZone, toUTCTimestamp, fromUTCTimestamp |
Do this work after #53 is merged. The DateTimeOffset mapping changes the correct return type. |
| 5 | toWeek, toYearWeek, toISOWeek, toISOYear |
Week numbers with a mode argument. .NET has no equivalent. |
| 6 | toUnixTimestamp, fromUnixTimestamp |
toUnixTimestamp returns UInt32. |
| 7 | formatDateTime |
The only way to format on the server. ToString(format) cannot translate. |
| 8 | changeYear, changeMonth, changeDay, changeHour, changeMinute, changeSecond |
Six methods with one shape. |
| 9 | toRelativeYearNum ... toRelativeSecondNum |
Integer bucket keys for GROUP BY. |
| 10 | dateName, monthName |
Name extraction. |
Out of scope
Do not add these functions now. They have a small audience. Add them if a user asks.
formatDateTimeInJodaSyntax, fromUnixTimestampInJodaSyntax, toModifiedJulianDay, fromModifiedJulianDay, toDaysSinceYearZero, fromDaysSinceYearZero, nowInBlock, serverTimezone, UTCTimestamp, localtime, addInterval, addTupleOfIntervals, timeSlot, timeSlots, toYYYYMMDD, toYYYYMMDDhhmmss, YYYYMMDDToDate.
Measured ClickHouse behaviour
These results come from a ClickHouse 26.7.1 server. Use them when you write the tests.
Return types are narrow. The translator must attach the correct type mapping, or the read fails:
| Expression | Return type |
|---|---|
toYear(...), toDayOfYear(...), toMillisecond(...) |
UInt16 |
toMonth(...), toDayOfMonth(...), toHour(...), toDayOfWeek(...) |
UInt8 |
dateDiff(...) |
Int64 |
toUnixTimestamp(...) |
UInt32 |
toStartOfMonth(DateTime) |
Date, not DateTime |
toDate(...) - toDate(...) |
Int32, a count of days |
dateDiff and age give different results. dateDiff counts the unit boundaries between the two values. age gives the number of full units.
SELECT dateDiff('month', toDate('2021-12-29'), toDate('2022-01-01')); -- 1
SELECT age('month', toDate('2021-12-29'), toDate('2022-01-01')); -- 0Other measured values:
SELECT toLastDayOfMonth(toDate('2026-02-03')); -- 2026-02-28
SELECT toLastDayOfWeek(toDate('2026-08-12')); -- 2026-08-15, a Saturday
SELECT toISOWeek(toDate('2026-08-12')); -- 33
SELECT toWeek(toDate('2026-08-12')); -- 32
SELECT toTimeZone(toDateTime('2026-08-12 12:00:00', 'UTC'), 'Europe/Paris'); -- 2026-08-12 14:00:00toLastDayOfWeek uses mode 0 by default, and mode 0 starts the week on Sunday. This agrees with toStartOfWeek, which PR #57 documents. Be careful: toDayOfWeek also has a mode 0, but that mode starts the week on Monday. The default mode is not the same across the family.
Related change: the evaluatable expression filter
ClickHouseEvaluatableExpressionFilter.IsEvaluatableExpression has one hard-coded type test for each class of EF.Functions methods. PR #57 adds the second test:
MethodCallExpression methodCallExpression when methodCallExpression.Method.DeclaringType ==
typeof(ClickHouseJsonDbFunctionsExtensions) => false,
MethodCallExpression methodCallExpression when methodCallExpression.Method.DeclaringType ==
typeof(ClickHouseDateTimeDbFunctionsExtensions) => false,Each new class of EF.Functions methods adds one more test. If a contributor forgets the test, the calls evaluate on the client and become constants. The failure is silent.
Replace the tests with one general rule. There are two options:
- a marker attribute on the extensions class
- a static set of types, which each extensions class registers itself in
Make this change before a third class exists.
Activity