Hi IoTDB community, I would like to share our current thoughts and proposal for supporting calendar-month and calendar-year intervals in Continuous Query (CQ). The related issue is:
https://github.com/apache/iotdb/issues/18428 Currently, CQ accepts mo/y in EVERY and RANGE, but converts them into fixed durations: one month becomes 30 days and one year becomes 365 days. This causes scheduling drift and may make the CQ cadence inconsistent with GROUP BY(1mo), which already uses natural calendar-month semantics. Our proposal is to treat calendar duration as first-class CQ metadata throughout parsing, RPC, persistence, recovery, and scheduling. Proposed user-visible behavior: - Support mo/month and y/year in CQ EVERY and RANGE. - Normalize 1y to 12 calendar months. - Keep w, d, h, m, s, ms, us, and ns as fixed elapsed-time units. - Continue supporting compound durations such as 1y2mo3d. - If EVERY is omitted, inherit the complete duration from GROUP BY TIME. Therefore, GROUP BY(1mo) should produce calendar EVERY 1mo rather than fixed 30d. - If RANGE is omitted, keep the existing defaults: - startOffset = EVERY - endOffset = 0 - This feature does not change the data types, aggregation functions, or SELECT INTO rules supported by the CQ query body. Logically, a duration would contain two components: Duration { monthPart: integer number of calendar months fixedPart: fixed ticks in the configured timestamp precision } Calendar scheduling should always be calculated from the original BOUNDARY. For boundary B, EVERY duration E, CQ time zone Z, and occurrence index n: executionTime(n) = calendarApply(B, n * E, Z) This is important for month-end anchors. For example: BOUNDARY 2024-01-31 EVERY 1mo should produce: 2024-01-31 2024-02-29 2024-03-31 2024-04-30 It should not repeatedly add one month to the previously clamped timestamp, because that would drift to March 29. For RANGE, we propose applying the offset in duration-vector space: startTime(n) = calendarApply(B, n * E - startOffset, Z) endTime(n) = calendarApply(B, n * E - endOffset, Z) This preserves the existing documented BOUNDARY formula and ensures that EVERY 1mo RANGE 1mo produces contiguous windows, including for Jan-31 and Feb-29 anchors. Calendar arithmetic should use the session ZoneId captured when the CQ is created and persisted with its metadata. Month-end dates should clamp to the last valid day of the target month: 2023-01-31 + 1mo = 2023-02-28 2024-01-31 + 1mo = 2024-02-29 2020-02-29 + 1y = 2021-02-28 2020-02-29 + 4y = 2024-02-29 For TIMEOUT POLICY: - BLOCKED should execute every occurrence in order, even when late. The query window should be based on the scheduled occurrence time rather than the actual wall-clock start time. - DISCARD should skip missed occurrences and jump directly to the first valid occurrence not earlier than the current time. - Calendar occurrence lookup should use estimation plus correction or binary search instead of iterating month by month. Example: CREATE CQ 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 In the Asia/Shanghai time zone, an execution at 2024-03-01 00:00 should cover: [2024-02-01 00:00, 2024-03-01 00:00) For compatibility, our current implementation direction is: - Add optional structured duration fields to TCreateCQReq while keeping the existing i64 fields for legacy readers. - Let new ConfigNodes prefer the structured duration fields. - Treat old requests and old snapshots without structured fields as fixed-duration CQs. - Version the CQInfo snapshot format. - Preserve the original SQL returned by SHOW CQS. - Avoid silently migrating existing flattened mo/y CQs by reparsing their saved SQL. Users can recreate those CQs to opt into calendar semantics. - Allow calendar CQ creation only after all ConfigNodes have been upgraded. - Keep the DataNode execution RPC based on concrete startTime/endTime values. Before implementation, we would appreciate feedback on the following semantic questions: 1. When a calendar EVERY omits BOUNDARY, should it automatically align to local calendar boundaries? Our proposal is to use 1970-01-01 00:00:00 in the persisted CQ time zone, while explicit BOUNDARY 0 continues to mean the Unix epoch instant. 2. Should RANGE use the boundary-anchored duration-vector formula described above, or should offsets be subtracted directly from each already-clamped execution timestamp? 3. For comparisons such as 1mo versus 30d, should ambiguous calendar/fixed combinations be conservatively rejected? 4. Should existing persisted mo/y CQs retain their legacy fixed-duration behavior until users recreate them? 5. Should the SQL grammar support the full aliases month/year in addition to mo/y? We plan to cover explicit and inherited 1mo/1y, Jan-31, Feb-29, all month lengths, multiple time zones, DST transitions, ms/us/ns precision, BLOCKED/DISCARD catch-up, leader recovery, serialization, and legacy snapshots. Any comments or suggestions on the proposed semantics would be greatly appreciated. Best regards, Bryan Yang（杨易达）
