JackieTien97 opened a new issue, #18428: URL: https://github.com/apache/iotdb/issues/18428
## 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: > `<every_interval>` 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). -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
