ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore
Add EF.Functions translations for the remaining ClickHouse date/time functions
- Dominant language
- C#
- Stars
- 23
- Forks
- 7
- Avg merge
- 14d 3h
- Merged PRs (30d)
- 1
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 `DbFunctions` in `ClickHouseDateTimeDbFunctionsExtensions`
- a `MethodInfo` entry in `ClickHouseDateTimeMethodTranslator`
- 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.
```sql
SELECT dateDiff('month', toDate('2021-12-29'), toDate('2022-01-01')); -- 1
SELECT age('month', toDate('2021-12-29'), toDate('2022-01-01')); -- 0
```
Other measured values:
```sql
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:00
```
`toLastDayOfWeek` 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:
```csharp
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.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by reading ClickHouseDateTimeDbFunctionsExtensions, ClickHouseDateTimeMethodTranslator, and DateTimeFunctionsTranslationTests alongside PR #57. Review ClickHouseEvaluatableExpressionFilter.IsEvaluatableExpression before adding more EF.Functions methods, and wait for #53 before the time-zone group. Done means the in-scope functions have matching extension methods, MethodInfo entries, tests for the measured behavior and return mappings, and the evaluatable-expression handling no longer needs another hard-coded class test.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp
- Domain
- database
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100