No LINQ translation for DateTime members and methods

[alex-clickhouse]

Problem

The provider translates no DateTime members and no DateTime methods. Only a direct comparison works.

EF Core's base RelationalMemberTranslatorProvider registers no built-in translators. It adds plugins only (RelationalMemberTranslatorProvider.cs:22). ClickHouseMemberTranslatorProvider adds ClickHouseArrayMethodTranslator and ClickHouseStringMethodTranslator only. No date/time translator exists.

Measured behaviour

Tested against a real ClickHouse server, on a DateTime property with column type DateTime64(6, 'UTC'):

Expression	Result
.Year .Month .Day .Hour .Minute	InvalidOperationException: The LINQ expression ... could not be translated
.Date .DayOfWeek .DayOfYear .TimeOfDay	same
.AddDays(1) .AddMonths(1)	same
DateTime.UtcNow / DateTime.Now / DateTime.Today	same
dt - dt (result is TimeSpan)	InvalidCastException: Unable to cast object of type 'System.TimeSpan' to type 'System.DateTime'
ts > new DateTime(2020, 1, 1)	works: WHERE `d`.`ts` > '2020-01-01 00:00:00.000000'

The dt - dt result is different from the others. It gives an InvalidCastException instead of a clear "could not be translated" message. This looks like a defect, not only a missing feature.

Suggested work

Add one shared date/time member translator. Give it the CLR type as a parameter so that it serves DateTime, DateOnly, and DateTimeOffset from the same class. Npgsql uses this shape (NpgsqlDateTimeMemberTranslator).

ClickHouse functions to map:

Member	ClickHouse function
.Year	toYear
.Month	toMonth
.Day	toDayOfMonth
.Hour	toHour
.Minute	toMinute
.Second	toSecond
.DayOfYear	toDayOfYear
.DayOfWeek	toDayOfWeek (note: ClickHouse counts Monday as 1, .NET counts Sunday as 0)
.Date	toStartOfDay
.AddDays / .AddMonths / .AddYears	date_add
DateTime.UtcNow	now64

Also translate the DateTime difference to date_diff, or give a clear "not supported" message.

Take care with two ClickHouse behaviours:

• toDayOfWeek starts at Monday = 1. .NET DayOfWeek starts at Sunday = 0. The translation must adjust the value, in the same way the string translators adjust the 0-based to 1-based index.
• These functions read the column timezone. For a DateTimeOffset property the result must stay consistent with the UTC instant that the column holds.

Notes

Found while I investigated #53. DateTimeOffset needs the same translators, so one shared class is better than a DateTimeOffset-only class. #53 itself needs comparison support only, which already works.

[alex-clickhouse]

Two corrections to the "Suggested work" section. I measured both on a ClickHouse 26.7.1 server.

.DayOfWeek needs no arithmetic

The issue says that the translation must adjust the value, as the string translators adjust a 0-based index to a 1-based index. This is not correct.

toDayOfWeek has a mode argument. Mode 2 agrees with the .NET DayOfWeek enum exactly:

Day	toDayOfWeek(d, 2)	.NET DayOfWeek
Sunday	0	0
Monday	1	1
Tuesday	2	2
Wednesday	3	3
Thursday	4	4
Friday	5	5
Saturday	6	6

SELECT toDayOfWeek(toDate('2026-08-16'), 2);  -- 0, a Sunday
SELECT toDayOfWeek(toDate('2026-08-22'), 2);  -- 6, a Saturday

Therefore translate .DayOfWeek to toDayOfWeek(x, 2). Do not add or subtract a constant. The default mode 0 starts the week on Monday, thus the mode argument is necessary. Do not send toDayOfWeek(x).

Use the dedicated add* functions, not date_add

The issue suggests date_add for .AddDays, .AddMonths, and .AddYears. The dedicated functions are a better fit:

.NET	ClickHouse
.AddYears(n)	addYears(x, n)
.AddMonths(n)	addMonths(x, n)
.AddDays(n)	addDays(x, n)
.AddHours(n)	addHours(x, n)
.AddMinutes(n)	addMinutes(x, n)
.AddSeconds(n)	addSeconds(x, n)
.AddMilliseconds(n)	addMilliseconds(x, n)

There are two reasons.

First, date_add takes the unit as a bare SQL keyword, as in date_add(MONTH, 1, d). A keyword is not a value. The translator must therefore emit a fragment, or a custom expression node. The add* functions take two plain arguments, thus a simple SqlFunctionExpression is sufficient.

Second, the add* functions keep the type of the source. addMonths on a DateTime64(3) column returns DateTime64(3).

ClickHouse also supplies the subtract* family, which has the same shape. Use it for a negative constant, or leave the negation to add*.

The clamp behaviour agrees with .NET

This is good news for the direct mapping. addMonths and addYears clamp the day of month in the same way as .NET:

SELECT addMonths(toDate('2026-01-31'), 1);  -- 2026-02-28
SELECT addMonths(toDate('2024-01-31'), 1);  -- 2024-02-29
SELECT addYears(toDate('2024-02-29'), 1);   -- 2025-02-28

DateTime.AddMonths and DateTime.AddYears give the same three results. No correction is necessary.

One more measured value

.Date translates to toStartOfDay, as the issue says. The result keeps the timezone of the column:

SELECT toTypeName(toStartOfDay(toDateTime('2026-08-12 13:45:00', 'UTC')));  -- DateTime('UTC')

This is the correct behaviour for a DateTimeOffset column after #53.

---

The ClickHouse-specific functions, such as dateDiff and dateTrunc, are now in #58. This issue stays for the standard .NET members and methods.