Built-in Jinja macro to convert a selected time_grain into a dialect-correct DATE_TRUNC/truncation expression

What is the problem or goal the end user is trying to solve or accomplish?

Users need to author SQL/Jinja that behaves correctly across dashboards where the time grain can change (e.g., day/week/month/quarter). For cumulative time series and other grain-sensitive calculations, they need a reliable way to truncate a temporal column to the currently selected time_grain (or a provided grain), without hand-coding per-engine logic. Today, switching the dashboard/native time grain breaks or misaligns custom queries unless users maintain complex if/else mappings and engine conditionals.

How are they solving it currently?

Writing verbose if/elif Jinja blocks mapping the current time_grain to a trunc function for each supported database (Postgres DATE_TRUNC, Snowflake DATE_TRUNC, BigQuery *_TRUNC, ClickHouse toStartOf*, Oracle TRUNC, etc.).

In some cases, duplicating logic in multiple datasets/charts and hard-coding the grain, which is brittle and hard to maintain.

Relying on outer query re-grouping done by the visualization (which doesn’t help when the inner query itself needs grain-aware logic, e.g., cumulative sums, window frames, or derived period keys).

What is the recommended solution by the Customer?

1. Add a first-class Jinja macro (e.g., {{ grain_trunc(column, grain=None, tz=None) }}) that returns a dialect-correct truncation expression for the current engine:

• Behavior

  • If grain is omitted, use the dashboard/native time grain currently applied.

  • Accept a column/expression (e.g., events.event_time) and optional tz for engines that support timezone-aware truncation.

  • Return the correct SQL snippet per DB engine using existing db_engine_spec time-grain mappings (e.g.,

    • Postgres: DATE_TRUNC('week', column)

    • Snowflake: DATE_TRUNC('week', column)

    • BigQuery: TIMESTAMP_TRUNC(column, WEEK) / DATE_TRUNC(column, WEEK) depending on type

    • ClickHouse: toStartOfWeek(column) / toStartOfMonth(column)

    • Oracle: TRUNC(column, 'IW'|'MM'|'Q'|'YYYY')

    • MySQL: DATE_FORMAT(...) or TIMESTAMP(...) equivalents)

  • Handle week-start conventions (ISO week vs Sunday/Monday) consistent with Superset’s existing time-grain semantics.

  • Validate/normalize unsupported grains with a clear error message.

• Developer ergonomics

  • Expose a companion helper to return the label for the trunc grain (e.g., {{ grain_label(grain=None) }}) when building GROUP BY 1 style queries and readable aliases.

  • Optionally: a convenience macro for cumulative windows that respects the current grain, e.g.,
    {{ cumulative_sum(expr='sum(val)', order_by=grain_trunc('ts')) }} that expands to the appropriate SUM(...) OVER (PARTITION BY ... ORDER BY grain_trunc(...)) frame per engine.