[Feature] Support date_add/date_sub hour offset in MTMV partition expressions for timezone-aware partitioning

Search before asking

• [X] I had searched in the issues and found no similar issues.

Description

Currently, Apache Doris async materialized views support date_trunc as a partition expression, but do not allow combining it with hour-level arithmetic like date_add or date_sub. This limitation prevents timezone-aware partitioning, which is essential for global deployments where data is stored in UTC but needs to be aggregated by local calendar boundaries.

Note: This is a focused subset of the broader feature request for arbitrary scalar functions in partition expressions. This issue specifically tracks date_trunc(date_add/sub(..., INTERVAL N HOUR), unit) with 1-to-N partition mapping support.

The Problem:

When data is stored in UTC and business reporting requires local timezone alignment, records near midnight boundaries end up in the wrong partition:

UTC Record:         2025-07-25 22:00:00

Local Time (UTC+3): 2025-07-26 01:00:00


Without offset: date_trunc('day') → 2025-07-25  ❌ (wrong day)

With +3h offset: date_trunc('day') → 2025-07-26  ✅ (correct day)

Desired Expressions:

-- Positive offset (UTC+3, e.g., Istanbul)

date_trunc(date_add(event_time, INTERVAL 3 HOUR), 'day')


-- Negative offset (UTC-5, e.g., New York)

date_trunc(date_sub(event_time, INTERVAL 5 HOUR), 'day')

Currently, these expressions fail with:

errCode = 2, detailMessage = Unable to find a suitable base table for partitioning

Use Case

Case 1 — UTC Storage with Local Timezone Reporting (Positive Offset)

A company stores all event data in UTC. Business users in Istanbul (UTC+3) need daily aggregations aligned to their local calendar day.

-- Base table partitioned by UTC time (21:00 UTC = 00:00 Istanbul)

CREATE TABLE events (

    id BIGINT,

    event_time DATETIME

) PARTITION BY RANGE(event_time) (

    PARTITION p_20250725 VALUES [("2025-07-24 21:00:00"), ("2025-07-25 21:00:00")),

    PARTITION p_20250726 VALUES [("2025-07-25 21:00:00"), ("2025-07-26 21:00:00"))

);


-- MTMV with +3 hour offset for Istanbul timezone alignment

CREATE MATERIALIZED VIEW mv_daily_stats

PARTITION BY (date_trunc(day_alias, 'day'))

AS

SELECT

    date_trunc(date_add(event_time, INTERVAL 3 HOUR), 'day') AS day_alias,

    count(*) AS cnt

FROM events

GROUP BY day_alias;

Case 2 — UTC Storage with Local Timezone Reporting (Negative Offset)

Same scenario for New York (UTC-5):

CREATE MATERIALIZED VIEW mv_daily_stats_ny

PARTITION BY (date_trunc(day_alias, 'day'))

AS

SELECT

    date_trunc(date_sub(event_time, INTERVAL 5 HOUR), 'day') AS day_alias,

    count(*) AS cnt

FROM events

GROUP BY day_alias;

Case 3 — Business Day Offset

A logistics company defines their operational "day" as starting at 06:00 instead of midnight:

CREATE MATERIALIZED VIEW mv_operational_day

PARTITION BY (date_trunc(day_alias, 'day'))

AS

SELECT

    date_trunc(date_sub(shift_start, INTERVAL 6 HOUR), 'day') AS day_alias,

    sum(deliveries) AS total_deliveries

FROM shifts

GROUP BY day_alias;

Technical Considerations

1-to-N Partition Mapping:

When base table partitions are aligned to UTC midnight but the MTMV uses an hour offset, a single base partition may span multiple MTMV partitions:

Base partition: [2025-07-25 00:00:00, 2025-07-26 00:00:00)

With +3h offset maps to MTMV partitions:

  - p_20250725: [2025-07-25 00:00:00, 2025-07-26 00:00:00)  (first 21 hours)

  - p_20250726: [2025-07-26 00:00:00, 2025-07-27 00:00:00)  (last 3 hours)

Supported Combinations:

PARTITION BY Unit | Allowed SELECT Units
-- | --
hour | hour, day, week, month, quarter, year
day | day, week, month, quarter, year
week | week, month, quarter, year
month | month, quarter, year
quarter | quarter, year
year | year

Column Type Requirements:

• ✅ DATETIME / DATETIMEV2 (has time component for hour arithmetic)


• ❌ DATE / DATEV2 (no time component, hour offset meaningless)

Expected Behavior

The following expressions should be supported in MTMV partition definitions:

-- date_add with hour interval

date_trunc(date_add(col, INTERVAL N HOUR), 'day')

date_trunc(date_add(col, INTERVAL N HOUR), 'week')

date_trunc(date_add(col, INTERVAL N HOUR), 'month')


-- date_sub with hour interval

date_trunc(date_sub(col, INTERVAL N HOUR), 'day')

date_trunc(date_sub(col, INTERVAL N HOUR), 'week')

date_trunc(date_sub(col, INTERVAL N HOUR), 'month')

Are you willing to submit PR?

• [X] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct