Support calendar-month/year (mo/y) intervals for Continuous Query EVERY and RANGE clauses
- 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
Assessment
This issue has not been assessed yet.