apache / apache/iotdb

Support calendar-month/year (mo/y) intervals for Continuous Query EVERY and RANGE clauses

Open
#18,428 9 comments 0 reactions 1 assignee Claimed by @DaZuiZui View on GitHub
good-first-issue New Feature
Dominant language
Java
Stars
6.4k
Forks
1.2k
Avg merge
1d 23h
Merged PRs (30d)
115

Description

## Is your feature request related to a problem?

IoTDB's Continuous Query (CQ) does not support **calendar-month** (or **calendar-year**) alignment for the `EVERY` execution interval and the `RANGE` time offsets. This makes it impossible to schedule a CQ that fires exactly at month boundaries — e.g. *"run at the end of every month and aggregate over the whole natural month"* — which is a common downsampling requirement. Because months have 28/29/30/31 days, the window length must be handled dynamically, so a fixed-day interval is not an adequate substitute.

## Current behavior

### Documentation
The CQ documentation states that `every_interval`, `start_time_offset`, and `end_time_offset` support only the units **`ns, us, ms, s, m, h, d, w`** — `mo` (month) and `y` (year) are not listed:

> `` specifies the query execution time interval. We currently support the units of ns, us, ms, s, m, h, d, w ...

Source: https://iotdb.apache.org/UserGuide/latest/User-Manual/Database-Programming.html

### Implementation
However, the implementation does **not reject** `mo`/`y` in these clauses — it silently accepts them and converts them to a **fixed duration** (1 month → 30 days, 1 year → 365 days):

- `iotdb-core/datanode/src/main/java/org/apache/iotdb/db/utils/DataNodeDateTimeUtils.java` — the single-argument overload `convertDurationStrToLong(String)` is called with `currentTime = -1`, so the `mo`/`month` branch flattens to `30 * 86_400_000` ms and `y`/`year` to `365 * 86_400_000` ms.
- The CQ parser uses exactly this overload for `EVERY`/`RANGE`: `ASTVisitor.parseResampleClause(...)` in `iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/parser/ASTVisitor.java`.
- The scheduler then treats the result as a plain `long` and does pure integer arithmetic (`executionTime += everyInterval`, `getFirstExecutionTime(...)`) in `iotdb-core/confignode/src/main/java/org/apache/iotdb/confignode/manager/cq/CQScheduleTask.java` — there is no calendar logic at runtime.

As a result, `EVERY 1mo` does **not** mean "at the end of every calendar month"; it means "every fixed 30 days", which drifts relative to real month boundaries. This is arguably worse than a clean rejection, because the statement appears to be accepted but is silently mis-scheduled.

Note that `GROUP BY(1mo)` in the CQ body **does** work as a natural calendar month, because it goes through a different converter (`constructTimeDuration`, which keeps a separate `monthDuration` part and can be evaluated against the calendar). So within a single CQ the `EVERY`/`RANGE` cadence (fixed 30 days) and the `GROUP BY` window (natural month) can drift apart.

## Desired behavior

Support `mo`/`month` and `y`/`year` as first-class units for the CQ `EVERY` interval and `RANGE` offsets, evaluated against the calendar (the way `GROUP BY` already handles months), so a CQ can be aligned to true month/year boundaries.

```sql
CREATE CONTINUOUS QUERY cq_monthly_spread
RESAMPLE EVERY 1mo RANGE 1mo
BEGIN
SELECT max_value(s), min_value(s)
INTO root.db.device(monthly_max, monthly_min)
FROM root.db.device
GROUP BY(1mo)
END
```

This should execute at each month boundary and aggregate over the just-finished natural month (correctly handling 28/29/30/31-day months).

## Alternatives considered

- **External scheduler** (e.g. cron at month-end) running a one-shot query — works today, but moves scheduling logic outside IoTDB and loses CQ's built-in result-sink semantics.
- **Daily CQ** with `EVERY 1d RANGE 31d` + `GROUP BY(1mo)` — partially works, but the monthly result is recomputed daily and only "finalizes" after month-end; and `RANGE 31d` is still a fixed window rather than a true calendar month.

## Additional context

As a smaller, complementary fix (or interim step), the silent 30/365-day flattening of `mo`/`y` in `EVERY`/`RANGE` could be turned into an **explicit error**, so that the runtime behavior matches the documentation and users are not misled into thinking `EVERY 1mo` produces a calendar-month cadence.

/ccraised by a user who wants month-end downsampling (spread = max − min over a natural month).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.