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

Search before asking

• 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:

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?

• Yes I am willing to submit a PR!

Code of Conduct

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