Hi Bryan, Yuan, One data point before the implementation starts, from having built the calendar-bucket path in the ThingsBoard table-model integration and hitting the same questions there.
The important caveat first, so this reads as what it is: CQ and date_bin cannot share an entry point. CQ needs the zone persisted with the metadata so occurrence n is reproducible across restart and leader change, whereas date_bin takes its zone from the live session. That difference is real and it is the reason the CQ work needs its own plumbing. But the arithmetic underneath is already implemented, and it is already static: iotdb-core/calc-commons/src/main/java/org/apache/iotdb/calc/transformation/ dag/column/unary/scalar/DateBinFunctionColumnTransformer public static long dateBin( long source, long origin, int monthDuration, long nonMonthDuration, ZoneId zoneId) Reading it against what the proposal specifies: 1. Duration representation. The signature already carries the split you propose as CQDuration -- monthDuration is the calendar-month part, nonMonthDuration is the fixed part, and they are applied separately. Same decomposition, already in the tree. 2. Boundary-anchored, not iterative. The proposal is explicit that occurrence n must be computed from the original BOUNDARY rather than by repeatedly adding one month to the previously clamped timestamp, so that BOUNDARY 2024-01-31 gives Jan-31, Feb-29, Mar-31 rather than drifting to Mar-29. dateBin already works that way: long monthsDiff = ChronoUnit.MONTHS.between(originDate, sourceDate); long completedMonthCycles = monthsDiff / monthDuration; binStart = originDate .plusNanos(...) .plusMonths(completedMonthCycles * monthDuration); Every bin start is derived from originDate in one step with a multiplied count. No loop, no accumulation, nothing computed from a previously clamped value -- so the drift you are guarding against is structurally impossible rather than avoided by care. 3. Month-end clamping, for pure mo/y durations. The clamping table in the proposal -- 2023-01-31 + 1mo = 2023-02-28, 2024-01-31 + 1mo = 2024-02-29, 2020-02-29 + 1y = 2021-02-28 -- is exactly java.time LocalDateTime.plusMonths semantics, which is what the code above uses. When the fixed part is zero the behaviour matches your table without needing to be specified separately. Once there is a fixed part, see the next point. 4. Ordering, which I think is an open decision rather than an agreement. Yuan asked for calendarApply to be defined explicitly as calendar months first, fixed ticks second. dateBin does the opposite -- fixed part first, calendar months second: originDate .plusNanos(getNanoTimeStamp(nonMonthDuration) * completedMonthCycles) .plusMonths(completedMonthCycles * monthDuration) For pure 1mo or 1y this is immaterial, since the fixed part is zero. For compound durations it is observable, because plusMonths clamps day-of-month. >From origin 2024-01-30 with 1mo + 2d, one cycle: fixed first (current code): 01-30 +2d = 02-01, +1mo = 2024-03-01 months first (Yuan's def): 01-30 +1mo = 02-29, +2d = 2024-03-02 A day apart. So if CQ specifies months-then-fixed and later reuses or mirrors this code, the two would disagree on compound durations. Worth settling deliberately rather than inheriting. Related, and the reason I am fairly confident this is under-specified rather than intentional: the step-back inside the same method uses the opposite order from its own forward path. binStart.minusMonths(monthDuration).minusNanos(getNanoTimeStamp(nonMonthDuration)) Forward is nanos-then-months, backward is months-then-nanos, and with clamping in play those are not inverses. So the suggestion is narrow: rather than defining calendarApply from scratch, consider calling this method or lifting it into a shared utility -- with the ordering settled first. The upside is not saving the code, it is small either way. It is that CQ EVERY 1mo and GROUP BY(1mo) then agree by construction. If they are implemented twice they will agree until one of them is fixed. Two things I have NOT verified, so please do not take them as cleared: - Whether the single step-back is always sufficient for compound durations. dateBin estimates the cycle count from months only and then steps back once if it overshot. I did not convince myself that one step is always enough when the fixed part is large, and I have not tested it. Combined with the ordering asymmetry above, the compound-duration path looks worth its own tests regardless of whether you reuse the method. - Overflow. Your addendum requires checked arithmetic on duration multiplication and narrowing conversions. dateBin takes monthDuration as an int and I do not see a guard on the multiplication, so a shared utility would need that added rather than inherited. For context on where this comes from: the ThingsBoard integration needed WEEK/MONTH/QUARTER buckets in a per-query timezone, and a pooled session cannot rebind its zone per query, so that path walks calendar boundaries in Java instead. Different constraint from yours, same underlying arithmetic -- which is why the shared-core question came up. Best, Zihan Dai On Fri, Aug 14, 2026 06:15 PM, Bryan Yang <[email protected]> wrote: > HI Yuan Tian， > > This email is a follow-up to the implementation proposal for Issue #18428: > https://github.com/apache/iotdb/issues/18428 > > The issue requests true calendar-month/year semantics for Continuous > Query EVERY and RANGE clauses. > > The previous proposal is available here: > > - English proposal: > https://github.com/apache/iotdb/issues/18428#issuecomment-5235636695 > > - Chinese proposal: > https://github.com/apache/iotdb/issues/18428#issuecomment-5235641552 > > I subsequently posted a scope-limited addendum: > > - English addendum: > https://github.com/apache/iotdb/issues/18428#issuecomment-5278263388 > > - Chinese translation: > https://github.com/apache/iotdb/issues/18428#issuecomment-5284464055 > > This email includes the actual addendum and the newly clarified > definitions, rather than only linking to them. > > The addendum only closes implementation ambiguities related to the > calendar-duration behavior requested by #18428. It does not introduce > a separate feature or replace the whole proposal. > > Where the addendum conflicts with an earlier point, it supersedes only > that specific point. The rest of the proposal remains unchanged. > > The proposal and this addendum are still pending maintainer > confirmation. No maintainer approval or alignment is assumed. > > 1. CQ duration aliases > > Only in the CQ EVERY and RANGE duration positions, support the > following case-insensitive calendar units: > > - month: mo, month > - year: y, year > > 1y and 1year normalize to 12 calendar months. > > Plural aliases, decimal values, signed input components, and > whitespace inside a duration remain unsupported. > > A CQ-specific duration rule must consume and validate the complete > value. Adding month and year here must not change GROUP BY TIME, date > arithmetic, FILL, SESSION, or general identifier tokenization. > > This supersedes the earlier statement that this issue would support > only the abbreviated mo and y spellings. > > 2. Structured request and explicit BOUNDARY > > Use a bounded structured duration value such as: > > CQDuration { > int64 monthPart > int64 fixedPart > } > > TCreateCQReq adds optional structured values for: > > - every > - startOffset > - endOffset > > It also adds an optional boundaryExplicit flag. > > For a new structured request, all three effective durations and > boundaryExplicit must be present. A partial structured representation > must be rejected. > > All arithmetic operations, duration multiplication, vector operations, > timestamp operations, and narrowing conversions must be checked for > overflow and invalid values. > > The existing i64 duration fields and their field IDs remain unchanged > for legacy fixed-only requests. > > A request containing no structured duration fields is interpreted as a > legacy fixed-only request. > > For a structured request: > > - the structured values are authoritative; > - new code must never use the legacy i64 fields to approximate a > non-zero calendar month part; > - when all structured monthPart values are zero, the legacy and > structured fixed values must agree, otherwise the request is rejected. > > boundaryExplicit=false means that ConfigNode applies the selected > omitted-BOUNDARY rule. > > boundaryExplicit=true preserves an explicitly written BOUNDARY 0 as > the Unix epoch instant. > > The flag, normalized boundary, structured durations, ZoneId, and > progress index must be preserved through procedure/plan serialization > and CQ metadata snapshots. > > 3. Mixed-version creation policy > > This issue does not claim support for creating a calendar CQ during a > rolling mixed-version window. > > Calendar CQ creation is supported only after: > > - all registered ConfigNodes have been upgraded to code that > understands the structured representation; and > - all DataNodes that can accept client SQL have also been upgraded. > > DataNodes are included because CREATE CQ is parsed on the DataNode > before TCreateCQReq is constructed. > > Before forwarding a structured calendar request, an upgraded > SQL-ingress DataNode must positively verify the condition using the > existing cluster node-version information. > > The upgraded ConfigNode repeats the same check before accepting the > request. > > An unknown or unsupported registered node version causes a clear rejection. > > During an upgrade, old DataNodes must be removed from client routing > before calendar CQ creation is used. > > Fixed-only CQ creation and historical CQs retain their legacy behavior. > > This is a narrow fail-closed compatibility check for #18428. It is not > a new feature-activation, membership-attestation, or general upgrade > framework. > > 4. Deterministic calendar and DST resolution > > Let B be the exact anchor instant represented in persisted > timestamp-precision ticks, and let Z be the persisted CQ ZoneId. > > For a duration vector (M, F): > > anchor = instant(B).atZone(Z) > anchorLocal = anchor.toLocalDateTime() > anchorOffset = anchor.getOffset() > > If M == 0: > > calendarApply(B, (M, F), Z) = checkedAdd(B, F) > > This is direct elapsed-tick addition. > > In particular: > > calendarApply(B, ZERO, Z) == B > > This preserves existing fixed-only CQ behavior. > > If M != 0: > > 1. Compute: > > targetLocal = anchorLocal.plusMonths(M) > > including end-of-month clamping. > > 2. Resolve targetLocal using the rules of Z. > > 3. Prefer anchorOffset when it is valid at targetLocal. > > 4. Add F afterwards as checked elapsed ticks. > > For a DST gap: > > - shift the local time forward by the transition duration; > - use the post-transition offset. > > For a DST overlap: > > - use anchorOffset when it is valid; > - otherwise use the earlier valid offset. > > This is the behavior of: > > ZonedDateTime.ofLocal(targetLocal, Z, anchorOffset) > > The result must not depend on the host default time zone. > > An integer or explicitly-offset BOUNDARY is first parsed as the exact > instant B. > > A written offset selects that instant, but it does not replace the > persisted CQ ZoneId Z. Recurrence observes and calculates B in Z. > > An offset-less BOUNDARY is resolved in Z using the same gap rule. For > an overlap without a preferred anchor offset, the earlier valid offset > is selected. > > This issue persists ZoneId, not a private copy of TZDB rules. > > Scheduling nodes are expected to use compatible TZDB data. Installing > a newer TZDB may legitimately affect future civil-time transitions. > > 5. Anchor-relative compound durations > > Duration arithmetic is component-wise and relative to the original > boundary: > > E = (monthPart, fixedPart) > > n * E = ( > n * monthPart, > n * fixedPart > ) > > executionTime(n) = calendarApply(B, n * E, Z) > > Multiplication, vector addition/subtraction, and timestamp conversion > must all be checked. > > A compound duration must not be repeatedly applied to the previous > occurrence. > > For example, with a UTC anchor of 2024-01-30 00:00 and EVERY 1mo2d: > > n=1 -> 2024-03-02 00:00 > n=2 -> 2024-04-03 00:00 > > RANGE vectors use the same component-wise arithmetic before one > calendarApply call. > > This preserves the original boundary and avoids drift caused by > repeatedly applying a duration to an already-clamped occurrence. > > 6. Initial occurrence and persisted progress > > ConfigNode captures the CREATE reference instant C exactly once. > > The initial occurrence index is: > > min { n >= 0 | executionTime(n) >= C } > > Equality selects that occurrence. The comparison is >=, not >. > > The search must use the captured C and must not repeatedly read a > moving current time. > > CQ metadata persists nextOccurrenceIndex. > > nextOccurrenceIndex is defined as the first occurrence that has not > yet been durably completed. > > The progress behavior is: > > - a failed execution retries the same index; > - after a successful execution, progress is persisted before the next > occurrence is scheduled; > - recovery resumes from the stored index; > - BLOCKED advances through indices in order; > - DISCARD applies the same lower-bound rule against one captured > callback time when skipping missed indices. > > This preserves the existing at-least-once execution boundary. > > It does not introduce an exactly-once execution protocol. > > 7. Timeout conversion > > TExecuteCQ.timeout remains expressed in milliseconds. > > For occurrence n, calculate the timeout using the actual adjacent > scheduled instants: > > deltaTicks = > executionTime(n + 1) - executionTime(n) > > timeoutMs = > ceil(deltaTicks / ticksPerMillisecond) > > The subtraction and conversion must be checked. > > A positive sub-millisecond interval becomes 1 millisecond. > > Timestamp-precision ticks must not be passed directly as milliseconds, > and the implementation must not use a fixed 30-day approximation. > > 8. Focused tests > > In addition to the tests already listed in the proposal, the > implementation will include focused tests for: > > - all four accepted aliases: mo, month, y, and year; > - rejection of these aliases outside CQ EVERY and RANGE; > - partial or conflicting structured fields; > - boundaryExplicit serialization and snapshot round trips; > - mixed-version and unknown-version rejection at SQL ingress; > - mixed-version and unknown-version rejection at ConfigNode acceptance; > - calendarApply(B, ZERO, Z) == B; > - DST gaps; > - both DST overlap offset cases; > - explicitly-offset BOUNDARY with a different CQ ZoneId; > - compound EVERY and RANGE schedules; > - checked duration-vector overflow; > - CREATE occurring exactly on a boundary; > - persisted nextOccurrenceIndex; > - recovery without an off-by-one index shift; > - timeout conversion under ms, us, and ns timestamp precision. > > 9. Explicitly excluded from this issue > > The following mechanisms are explicitly outside the scope of this > addendum and #18428: > > - new V2 RPC families; > - PREPARE/ACTIVATE commands; > - reader-floor markers; > - node attestation; > - general mutation exact-retry protocols; > - execution-admission redesign; > - custom ZoneRules snapshot formats. > > They are not required for accepting or implementing the > calendar-duration behavior requested by #18428. > > Review request > > Could you please confirm whether this scope-limited proposal satisfies > the requirements of #18428? > > In particular, feedback would be helpful on: > > - the CQ-only alias scope; > - the structured duration representation; > - the selected BOUNDARY and RANGE semantics; > - deterministic calendar and DST resolution; > - occurrence-index recovery; > - legacy compatibility; > - the narrow mixed-version rejection policy; and > - timeout conversion. > > If any part does not satisfy the issue, please identify: > > 1. the specific requirement that is not satisfied; > 2. a concrete failure scenario within the scope of #18428; and > 3. the smallest in-scope adjustment needed. > > I would like to avoid expanding this work into unrelated RPC, > upgrade-framework, exactly-once, or execution-admission changes. > > Best, > DaZuiZui > > > Bryan Yang <[email protected]> 于2026年8月11日周二 16:33写道： > > > HI Yuan Tian > > Thanks for the clarification. We will proceed with the implementation > > based on these semantics. In particular, we will keep only the existing > > mo/y Tree SQL units, use the boundary-anchored duration-vector formula, > > implement provable duration comparisons without flattening calendar > months, > > preserve legacy CQ behavior, and ensure recovery always derives > occurrences > > from the original boundary using a persisted occurrence index or > equivalent > > information. We will also define calendarApply as applying calendar > > months in the persisted ZoneId first and fixed ticks second. > > Best， > > Bryan Yang > > > > Yuan Tian <[email protected]> 于2026年8月11日周二 09:49写道： > > > >> 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（杨易达） > >> > > >> > > >
