[Feature Request] Support for arbitrary time-binning in ClickHouse (e.g., `toStartOfInterval`)

[sunyeongchoi]

I'd like to use functions like ClickHouse's toStartOfInterval natively in PRQL without having to rely on s-strings:

from events
derive bin = (timestamp | to_start_of_interval 15min)
group bin (aggregate [count])

However, toStartOfInterval is a function specific to the ClickHouse dialect and is not available in other dialects supported by PRQL. In this case, would it be appropriate for me to implement to_start_of_interval as a PRQL function? What approach would you recommend for handling such dialect-specific functions?

I’m considering three options for this:

1. Implementing to_start_of_interval for all supported dialects.
2. Implementing it only for ClickHouse and raising an error for other dialects that don't support it.
3. Adding it under a specific namespace, such as ch.to_start_of_interval, to mark it as a ClickHouse-specific function.

[prql-bot]

Thanks for the suggestion! I searched the codebase and didn't find an existing implementation of toStartOfInterval.

Here's what the codebase does have that's relevant:

• ClickHouse is a supported dialect with dialect-specific overrides for date.to_text, date.diff, and date.now (defined in prqlc/prqlc/src/sql/std.sql.prql).
• date.trunc was recently added, but it currently generates generic DATE_TRUNC() for ClickHouse (which ClickHouse does support via its dateTrunc() alias, though the casing may need a dialect override).
• toStartOfInterval is a distinct function from date.trunc — it supports arbitrary intervals (e.g., 15 minutes) rather than just named units.

For now, s-strings work as a workaround:

from events
derive bin = s"toStartOfInterval({timestamp}, INTERVAL 15 MINUTE)"
group bin (aggregate {count})

Adding this as a PRQL function would likely involve:

1. Defining the function signature in prqlc/prqlc/src/semantic/std.prql
2. Adding the ClickHouse-specific SQL generation in prqlc/prqlc/src/sql/std.sql.prql under the clickhouse module

The design question is whether this should be a ClickHouse-specific function or part of a more general time-binning abstraction that maps to different SQL across dialects. I'll leave it for a maintainer to evaluate and prioritize.

[sunyeongchoi]

@prql-bot

I’m considering three options for this:

1. Implementing to_start_of_interval for all supported dialects.
2. Implementing it only for ClickHouse and raising an error for other dialects that don't support it.
3. Adding it under a specific namespace, such as ch.to_start_of_interval, to mark it as a ClickHouse-specific function.

Which of those three would you suggest?

[prql-bot]

Great question! Looking at how the codebase handles similar cases, I'd recommend option 1 — implementing it as a cross-dialect function like date.bin (or similar) that maps to the appropriate SQL for each dialect.

Here's the reasoning based on existing patterns:

The codebase already follows this approach for other date functions. date.trunc, date.diff, and date.now all have a single PRQL function signature in std.prql with dialect-specific SQL in std.sql.prql. Time-binning to arbitrary intervals is a common need across databases — it's just expressed differently:

Dialect	SQL
ClickHouse	toStartOfInterval(col, INTERVAL 15 MINUTE)
Postgres	date_bin('15 minutes', col, '2000-01-01')
DuckDB	time_bucket(INTERVAL '15 minutes', col)
BigQuery	TIMESTAMP_BUCKET(col, INTERVAL 15 MINUTE)
MySQL / SQLite	null (unsupported → compile error)

For dialects that don't support it, the established pattern is to set the body to null, which produces a clear compile error: "operator X is not supported for dialect Y". This is what date.diff does for Postgres/SQLite and date.trunc does for SQLite.

Option 3 (namespace like ch.) doesn't exist in the codebase — there are no vendor-prefixed functions. Dialect differences are handled transparently inside the compiler, not exposed to users.

So the implementation path would be:

1. Add the function signature in prqlc/prqlc/src/semantic/std.prql (e.g., let bin = interval column -> internal std.date.bin)
2. Add default + dialect-specific SQL in prqlc/prqlc/src/sql/std.sql.prql (ClickHouse, Postgres, DuckDB, BigQuery implementations; null for MySQL/SQLite/MSSQL)
3. If any dialect needs non-trivial AST manipulation (like BigQuery's parameter ordering), add a handler in gen_expr.rs
4. Add tests following the date_trunc pattern in sql.rs

That said, this is ultimately a design decision for the maintainers — naming (date.bin vs date.to_start_of_interval), parameter format (interval as a single value vs separate unit+amount), and which dialects to support are all worth discussing. Tagging @max-sixty for input on the preferred approach.

[max-sixty]

that sounds v reasonable