ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore
Add EF.Functions translations for the remaining ClickHouse date/time functions
- Ngôn ngữ chính
- C#
- Star
- 23
- Fork
- 7
- Merge trung bình
- 14 ngày 3 giờ
- Pull request đã merge (30 ngày)
- 1
Mô tả
## 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.
Hướng dẫn đóng góp
Chưa lập chỉ mục được hướng dẫn đóng góp cho kho mã nguồn này
Hướng nghiên cứu
Bắt đầu bằng cách đọc ClickHouseDateTimeDbFunctionsExtensions, ClickHouseDateTimeMethodTranslator và DateTimeFunctionsTranslationTests cùng với PR #57. Xem xét ClickHouseEvaluatableExpressionFilter.IsEvaluatableExpression trước khi thêm các phương thức EF.Functions khác, và chờ #53 trước khi xử lý nhóm múi giờ. Được xem là hoàn tất khi các hàm nằm trong phạm vi có các extension method và mục MethodInfo tương ứng, có các bài kiểm thử cho hành vi được đo và các ánh xạ giá trị trả về, đồng thời việc xử lý biểu thức có thể đánh giá không còn cần thêm một kiểm tra lớp hard-coded nào nữa.
Do mô hình lập chỉ mục viết ra từ nội dung của issue.
Đánh giá
- Công nghệ
- csharp
- Lĩnh vực
- database
- Loại issue
- Tính năng
- Độ khó
- 5/5
- Thời gian dự kiến
- Hơn một tuần
- Mức độ hoạt động
- Ít trao đổi
- Độ rõ ràng
- Khá rõ ràng
- Mức phù hợp với người mới
- 42/100