Hi Bryan, Thanks for the detailed proposal. I agree with the overall direction of representing calendar months and fixed elapsed time separately. My preferences for the five questions are:
1. When a calendar-based EVERY omits BOUNDARY, it should align to 1970-01-01 00:00:00 in the persisted CQ ZoneId. An explicit BOUNDARY 0 should continue to mean the Unix epoch instant. Fixed-only EVERY intervals should retain their existing default behavior. 2. RANGE should use the boundary-anchored duration-vector formula. This preserves the invariant that, when RANGE == EVERY, the window start is exactly the previous scheduled occurrence. Subtracting an offset from an already-clamped execution timestamp is unsafe because calendar-month arithmetic is not reversible. 3. Comparisons whose ordering depends on the month, time zone, or DST—such as 1mo versus 30d—should be rejected rather than flattened to an approximate duration. However, compound durations should not be rejected merely because they contain both components. Comparisons that can be proven, such as 1mo3d being greater than 1mo, may still be accepted. 4. Existing persisted mo/y CQs should retain their legacy fixed-duration behavior. Old metadata should not be re-parsed and silently migrated. Recreating the CQ provides a clear way for users to opt into calendar semantics. 5. For this change, I suggest keeping the existing Tree SQL abbreviations mo/y. CQ currently uses the Tree SQL grammar, whose duration literals do not support the full month/year forms. Although Table SQL accepts MONTH/YEAR, CQ is not supported in the Table dialect yet. Full unit aliases would be better handled as a separate, consistent Tree SQL grammar enhancement covering GROUP BY TIME, date arithmetic, FILL/SESSION, and CQ, rather than as CQ-only aliases. I would also suggest explicitly defining calendarApply as applying calendar months in the persisted ZoneId first and fixed ticks second. Persisting the occurrence index, or equivalent information, would help ensure that recovery and leader changes always calculate occurrences from the original boundary. Best regards, ---------------------------- Yuan Tian On Mon, Aug 10, 2026 at 2:50 PM Bryan Yang <[email protected]> wrote: > 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（杨易达） >
