ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore

Add EF.Functions translations for the remaining ClickHouse date/time functions

Open
#58 0 comments 0 reactions 0 assignees View on GitHub
enhancement
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.