Support calendar-month/year (mo/y) intervals for Continuous Query EVERY and RANGE clauses

[JackieTien97]

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.

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).

[DaZuiZui]

i work it

[henjoe]

+1 for this feature

[DaZuiZui]

Our implementation approach for this requirement

Below is our implementation approach for this requirement. We first define the user-visible behavior—including syntax, duration semantics, component meanings, supported units, and examples—and then describe the corresponding parser, RPC, persistence, scheduling, recovery, and execution changes.

1. Goal and scope

Continuous Query should support real calendar-month and calendar-year semantics in EVERY and RANGE:

• 1mo means one calendar month, not a fixed 30-day duration.
• 1y means twelve calendar months, not a fixed 365-day duration.
• Scheduling and query windows must correctly handle 28/29/30/31-day months, leap years, month-end dates, time zones, and DST rules.
• Calendar semantics must survive ConfigNode restart, leader changes, procedure recovery, and CQ metadata snapshots.
• When EVERY is inherited from GROUP BY(1mo) or GROUP BY(1y), it must retain calendar semantics.

This feature changes only CQ scheduling and the generated query time range. It does not add new source measurement types, result types, aggregate functions, or SELECT INTO behavior. The CQ query body continues to follow the existing tree-model query and type rules.

2. Syntax

The existing CQ grammar remains:

CREATE [CONTINUOUS QUERY | CQ] <cq_id>
[RESAMPLE
  [EVERY <duration>]
  [BOUNDARY <time_value>]
  [RANGE <start_offset> [, <end_offset>]]
]
[TIMEOUT POLICY {BLOCKED | DISCARD}]
BEGIN
  <select_into_statement>
END

Duration syntax:

<duration> ::= <duration_component>+
<duration_component> ::= <unsigned_integer><unit>

Supported duration units:

• Calendar units: y, year, mo, month
• Existing fixed units: w, d, h, m, s, ms, us, ns

Examples of valid duration values:

1mo
1year
1y2mo
1mo2d12h

Syntax rules:

• Unit names are case-insensitive.
• Components are concatenated without spaces.
• Decimal values such as 1.5mo are not supported.
• Negative components are not supported.
• Plural full names such as months or years are not introduced by this feature.
• The complete duration must satisfy the semantic positivity rules of the corresponding clause.

BOUNDARY keeps the existing timeValue inputs:

• An integer timestamp
• A datetime literal
• NOW()
• A date expression such as NOW() + 1d

3. Duration data model and component meanings

Logically, a duration is a pair:

Duration {
  monthPart: integer number of calendar months
  fixedPart: fixed ticks in the configured timestamp precision
}

Component meanings:

Input	monthPart	fixedPart	Meaning
1mo or 1month	1	0	One calendar month
1y or 1year	12	0	Twelve calendar months
1d	0	24 hours	Fixed elapsed duration
1w	0	7 x 24 hours	Fixed elapsed duration
h through ns	0	Corresponding ticks	Fixed elapsed duration
1mo2d	1	48 hours	One calendar month plus a fixed 48 hours

Additional rules:

• Years are normalized to months, so 1y equals 12mo.
• Repeated components are accumulated.
• Textual component order does not change the normalized value. For example, 1d1mo and 1mo1d represent the same pair.
• Calendar months are applied first; the fixed tick part is applied afterwards.
• d and w remain fixed elapsed durations. They are not changed into calendar-day or calendar-week units.

4. Meaning of each CQ component

EVERY

EVERY defines the recurrence duration.

Let:

• B be the recurrence boundary/anchor.
• E be the normalized EVERY duration.
• Z be the CQ time zone captured at creation.
• n be the occurrence index, starting at zero.

The nth scheduled timestamp is:

executionTime(n) = calendarApply(B, n * E, Z)

Every occurrence must be calculated from the original boundary. It must not be calculated by repeatedly adding one month to the previously clamped timestamp.

For example, with BOUNDARY 2024-01-31 and EVERY 1mo:

n=0  2024-01-31
n=1  2024-02-29
n=2  2024-03-31
n=3  2024-04-30

The sequence must not drift to March 29 after February clamps the date.

If EVERY is omitted:

• The query body must contain GROUP BY TIME, as it does today.
• EVERY inherits the complete TimeDuration from the group-by interval.
• GROUP BY(1mo) therefore produces calendar EVERY 1mo, not fixed 30d.

BOUNDARY

BOUNDARY defines the stable recurrence anchor.

When explicitly supplied, the parsed timestamp is the exact anchor.

For calendar EVERY with BOUNDARY omitted, our implementation will use 1970-01-01 00:00:00 in the persisted CQ time zone as the canonical local calendar anchor. This allows a CQ without an explicit boundary to run at local month/year boundaries.

An explicit BOUNDARY 0 would continue to mean the Unix epoch instant. Therefore, the implementation must retain whether BOUNDARY was explicitly specified; a numeric value of zero alone is insufficient.

For fixed-only EVERY with BOUNDARY omitted, the current BOUNDARY 0 behavior remains unchanged.

We would like to align with maintainers on this omitted-BOUNDARY semantic detail before coding.

RANGE

RANGE defines the left-closed, right-open query interval:

[startTime(n), endTime(n))

Defaults remain:

• If RANGE is omitted, startOffset = EVERY and endOffset = 0.
• If only startOffset is specified, endOffset = 0.

To preserve the currently documented boundary formula and avoid non-invertible calendar subtraction, our implementation will compute the endpoints in duration-vector space before applying them to the boundary:

startTime(n) = calendarApply(B, n * E - startOffset, Z)
endTime(n)   = calendarApply(B, n * E - endOffset, Z)

This guarantees that EVERY 1mo RANGE 1mo produces contiguous windows for month-end and leap-day anchors.

Example with BOUNDARY 2024-01-31, EVERY 1mo, RANGE 1mo:

Execution time	Query interval
2024-02-29	[2024-01-31, 2024-02-29)
2024-03-31	[2024-02-29, 2024-03-31)
2024-04-30	[2024-03-31, 2024-04-30)

We would like to align with maintainers on this anchored RANGE formula before coding. Directly subtracting one month from an already clamped execution timestamp has different and non-contiguous results.

TIMEOUT POLICY

BLOCKED:

• Executes every occurrence in index order, even when execution is late.
• Uses the scheduled occurrence timestamp to calculate the query window, not the actual wall-clock time when the task starts.
• Does not skip a calendar window.

DISCARD:

• Skips missed occurrences.
• Jumps to the first valid occurrence that is not earlier than the current time.
• Must preserve the original boundary and must not drift after skipping.

Calendar occurrence lookup should use an estimate plus correction or binary search. It should not iterate month by month from 1970.

BEGIN/END query body

The query body remains an existing tree-model SELECT INTO statement.

The existing CQ restrictions remain, including the automatic insertion of the time filter and GROUP BY TIME range. The DataNode ultimately receives concrete startTime and endTime values, so the main query execution engine does not need calendar-duration awareness.

5. Calendar arithmetic rules

calendarApply(base, duration, zone) is defined as:

1. Convert the base instant to local date/time in the persisted CQ zone.
2. Add the duration monthPart using calendar-month arithmetic.
3. Clamp an invalid target day to the last valid day of the target month.
4. Convert back to an instant using the zone rules.
5. Add fixedPart as an elapsed duration in the configured timestamp precision.

Month-end and leap-year examples:

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

Because each occurrence uses B plus n times E, the original day can reappear in a later valid month or leap year.

The persisted CQ zone must be used during creation, normal scheduling, leader recovery, and snapshot recovery. ConfigNode system-default time zones must not affect the result.

6. Semantic validation

The current basic constraints remain:

• EVERY > 0
• startOffset > 0
• endOffset >= 0
• startOffset > endOffset
• Keep the current startOffset >= EVERY rule
• EVERY must satisfy continuous_query_minimum_every_interval

Calendar and fixed durations do not form a permanent total order:

1mo may be shorter than 30d in February
1mo may be longer than 30d in a 31-day month

Therefore, semantic checks must never flatten a month to 30 days.

Duration comparison rules:

• Equal normalized duration pairs compare equal.
• Pure calendar durations compare by month count.
• Pure fixed durations compare by fixed ticks.
• Mixed comparisons may use conservative minimum/maximum bounds.
• If an ordering cannot be proven for all calendar positions, reject the statement with a clear semantic error instead of silently choosing a 30-day approximation.

Overflow and resource validation should also cover excessive month counts, duration multiplication, timestamps near Long.MIN_VALUE/Long.MAX_VALUE, and non-monotonic or non-terminating occurrence searches.

7. Examples

Natural monthly aggregation

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

With CQ zone Asia/Shanghai and the omitted-BOUNDARY rule defined above, an execution at 2024-03-01 00:00 covers:

[2024-02-01 00:00, 2024-03-01 00:00)

The February window is 28 or 29 days depending on the year.

Explicit month-end anchor

CREATE CQ cq_month_end
RESAMPLE
  EVERY 1mo
  BOUNDARY 2024-01-31T00:00:00+08:00
  RANGE 1mo
BEGIN
  SELECT count(s)
  INTO root.result.d(count_s)
  FROM root.source.d
  GROUP BY(1mo)
END

Occurrence sequence:

2024-01-31
2024-02-29
2024-03-31
2024-04-30

Calendar-year aggregation

CREATE CQ cq_yearly
RESAMPLE
  EVERY 1y
  BOUNDARY 2020-02-29T00:00:00+08:00
  RANGE 1y
BEGIN
  SELECT avg(s)
  INTO root.result.d(yearly_avg)
  FROM root.source.d
  GROUP BY(1y)
END

The schedule clamps to February 28 in non-leap years and returns to February 29 when n times one year lands in a leap year.

Two RANGE offsets

RESAMPLE EVERY 1mo RANGE 2mo, 1mo

At an execution timestamp of 2024-04-01 on a first-of-month anchor, the query interval is:

[2024-02-01, 2024-03-01)

8. Persistence, protocol, and upgrade behavior

Compatibility and upgrade handling:

• Add an optional structured CQ duration type to TCreateCQReq for every, startOffset, and endOffset.
• Keep the existing i64 fields as legacy fallback values; do not change their field IDs or wire types.
• New ConfigNodes prefer the structured values.
• Requests without structured values are normalized to fixed-only durations.
• Store the three structured durations in CQEntry.
• Add a magic/version to the CQInfo snapshot format.
• Load legacy CQInfo snapshots as fixed-duration CQs.
• Preserve the original SQL returned by SHOW CQS.
• Do not automatically reparse saved SQL to migrate an existing flattened 1mo/1y CQ. Users can drop and recreate the CQ to opt into calendar behavior.
• Calendar CQ creation should be enabled only after all ConfigNodes have been upgraded, because an old ConfigNode leader would ignore the optional calendar fields.

The timeout sent in TExecuteCQ should be based on the actual distance from the current occurrence to the next occurrence, rather than a fixed 30-day approximation.

9. Implementation outline

The expected data flow is:

SQL parser
  -> normalized TimeDuration(monthPart, fixedPart)
  -> optional structured fields in TCreateCQReq
  -> versioned CQInfo metadata/snapshot
  -> boundary-anchored calendar scheduler
  -> concrete startTime/endTime
  -> existing DataNode CQ execution

Main implementation areas:

1. Parse explicit EVERY/RANGE with TimeDuration rather than convertDurationStrToLong.
2. Preserve TimeDuration when EVERY is inherited from GROUP BY.
3. Change CreateContinuousQueryStatement to carry structured durations and whether BOUNDARY was explicit.
4. Add backward-compatible optional duration fields to the ConfigNode Thrift request.
5. Normalize legacy and structured requests in one place on ConfigNode.
6. Store structured duration metadata and version the CQ snapshot.
7. Extract deterministic calendar occurrence/window calculation into a pure, testable helper.
8. Update CreateCQProcedure and CQScheduleTask recovery to derive the next occurrence from the original boundary, rather than lastExecutionTime + everyInterval.
9. Keep TExecuteCQ and DataNode execution based on concrete timestamps.
10. Update the separate apache/iotdb-docs repository after the semantics are accepted.

10. Implementation details to align on

Before coding, we would like to align on the following implementation details:

1. Whether omitted BOUNDARY for calendar EVERY should align to local calendar boundaries as defined above, or preserve exact Unix timestamp zero semantics.
2. Whether RANGE should use the boundary-anchored duration-vector formula, or subtract offsets directly from each clamped execution timestamp.
3. Whether mixed calendar/fixed comparisons that change ordering by date should be conservatively rejected.
4. Whether existing persisted CQs should retain legacy fixed behavior until recreated.
5. Whether full aliases month/year are required in addition to mo/y.

The test plan will cover explicit and inherited 1mo/1y, omitted RANGE, Jan-31, Feb-29, 28/29/30/31-day months, UTC/Asia-Shanghai/America-New-York, DST transitions, ms/us/ns precision, BLOCKED/DISCARD catch-up, fixed-duration regression, procedure/plan serialization, leader recovery, new snapshot round trips, and legacy multi-entry snapshot fixtures.

[DaZuiZui]

我们实现这个需求的思路（中文）

下面说明我们准备如何实现这个需求。我们先明确用户可见的功能语义，包括语法、duration 语义、各 component 含义、支持的单位和示例，再说明解析、RPC、持久化、调度、恢复和执行链路的实现思路。

1. 功能目标与范围

Continuous Query 的 EVERY 和 RANGE 应支持真正的日历月、日历年语义：

• 1mo 表示一个自然月，不再固定为 30 天。
• 1y 表示十二个自然月，不再固定为 365 天。
• 调度时间和查询窗口应正确处理 28/29/30/31 天月份、闰年、月末日期、时区和 DST 规则。
• ConfigNode 重启、leader 切换、procedure 恢复和 CQ metadata snapshot 恢复后，日历语义不能丢失。
• EVERY 从 GROUP BY(1mo) 或 GROUP BY(1y) 继承时，也必须保留日历语义。

本功能只改变 CQ 调度和自动生成的查询时间范围，不新增 source measurement 类型、结果类型、聚合函数或 SELECT INTO 行为。CQ 查询正文继续遵循现有 tree-model 查询和类型规则。

2. 语法定义

CQ 现有语法保持不变：

CREATE [CONTINUOUS QUERY | CQ] <cq_id>
[RESAMPLE
  [EVERY <duration>]
  [BOUNDARY <time_value>]
  [RANGE <start_offset> [, <end_offset>]]
]
[TIMEOUT POLICY {BLOCKED | DISCARD}]
BEGIN
  <select_into_statement>
END

Duration 语法：

<duration> ::= <duration_component>+
<duration_component> ::= <unsigned_integer><unit>

本实现支持的单位：

• 日历单位：y、year、mo、month
• 现有固定单位：w、d、h、m、s、ms、us、ns

合法 duration 示例：

1mo
1year
1y2mo
1mo2d12h

语法规则：

• 单位大小写不敏感。
• 多个 component 直接连接，中间不允许空格。
• 不支持 1.5mo 这样的浮点数。
• 不支持负数 component。
• 本功能不新增 months、years 等复数全称。
• 整个 duration 还必须满足所在 clause 的正值语义约束。

BOUNDARY 保留现有 timeValue 输入类型：

• 整数时间戳
• 日期时间字面量
• NOW()
• NOW() + 1d 等日期表达式

3. Duration 数据类型与各 component 含义

逻辑上，一个 duration 是一个二元结构：

Duration {
  monthPart: 整数日历月数量
  fixedPart: 当前 timestamp precision 下的固定 tick 数量
}

各 component 含义：

输入	monthPart	fixedPart	含义
1mo 或 1month	1	0	一个自然月
1y 或 1year	12	0	十二个自然月
1d	0	24 小时	固定经过时间
1w	0	7 x 24 小时	固定经过时间
h 到 ns	0	对应 tick	固定经过时间
1mo2d	1	48 小时	一个自然月再加固定 48 小时

补充规则：

• year 统一归一化成 month，所以 1y 等价于 12mo。
• 重复 component 可以累加。
• 文本中 component 的先后顺序不改变归一化结果，例如 1d1mo 与 1mo1d 表示同一个二元结构。
• 计算时先应用日历月部分，再应用 fixed tick 部分。
• d 和 w 仍是固定经过时间，不在本需求中改成日历日或日历周。

4. 每个 CQ component 的具体语义

EVERY

EVERY 定义 CQ 的重复调度周期。

设：

• B 为 recurrence boundary/anchor。
• E 为归一化后的 EVERY duration。
• Z 为创建 CQ 时捕获的时区。
• n 为 occurrence 序号，从 0 开始。

第 n 个计划执行时间为：

executionTime(n) = calendarApply(B, n * E, Z)

每个 occurrence 必须从原始 BOUNDARY 直接计算，不能在上一次已经发生月末钳制的 executionTime 上连续加月。

例如 BOUNDARY 2024-01-31、EVERY 1mo：

n=0  2024-01-31
n=1  2024-02-29
n=2  2024-03-31
n=3  2024-04-30

二月钳制到 29 日后，后续不能漂移到 3 月 29 日。

如果省略 EVERY：

• 查询正文必须像现在一样包含 GROUP BY TIME。
• EVERY 完整继承 group-by interval 的 TimeDuration。
• GROUP BY(1mo) 应得到日历 EVERY 1mo，而不是固定 30d。

BOUNDARY

BOUNDARY 定义整个 recurrence 序列的稳定锚点。

用户显式指定时，使用解析后的准确时间戳作为锚点。

对于包含日历月的 EVERY，如果省略 BOUNDARY，我们的实现会使用 CQ 持久化时区中的 1970-01-01 00:00:00 作为规范化的本地日历锚点。这样不显式写 BOUNDARY 的 CQ 也可以在本地自然月界或自然年界运行。

显式 BOUNDARY 0 仍表示 Unix epoch instant。因此实现中必须保存用户是否显式指定了 BOUNDARY，不能只通过数值是否为 0 来判断。

对于不包含日历月的 fixed-only EVERY，省略 BOUNDARY 时继续保持现有 BOUNDARY 0 行为。

这一省略 BOUNDARY 的处理方式需要 maintainer 确认。

RANGE

RANGE 定义每次 CQ 查询的左闭右开时间范围：

[startTime(n), endTime(n))

默认值保持不变：

• 省略 RANGE 时，startOffset = EVERY、endOffset = 0。
• 只指定 startOffset 时，endOffset = 0。

为了保持现有文档中的 BOUNDARY 公式，并避免日历月加减法不可逆，我们的实现会先在 duration 向量上完成加减，再把结果一次性作用于原始 BOUNDARY：

startTime(n) = calendarApply(B, n * E - startOffset, Z)
endTime(n)   = calendarApply(B, n * E - endOffset, Z)

这样可以保证 EVERY 1mo RANGE 1mo 在月末或闰日锚点下仍产生连续窗口。

例如 BOUNDARY 2024-01-31、EVERY 1mo、RANGE 1mo：

执行时间	查询范围
2024-02-29	[2024-01-31, 2024-02-29)
2024-03-31	[2024-02-29, 2024-03-31)
2024-04-30	[2024-03-31, 2024-04-30)

这一锚定 RANGE 公式需要 maintainer 确认。直接从已经钳制的 executionTime 反向减一个月，会得到不同且不连续的结果。

TIMEOUT POLICY

BLOCKED：

• 即使任务已经延迟，也按 occurrence 序号逐个执行。
• 查询窗口基于计划执行时间，而不是任务真正开始执行时的墙钟时间。
• 不跳过任何日历窗口。

DISCARD：

• 跳过已经错过的 occurrence。
• 直接跳到第一个不早于当前时间的合法 occurrence。
• 跳过后仍以原始 BOUNDARY 为锚点，不能产生日期漂移。

Calendar occurrence 的查找应使用估算加修正或二分，不能从 1970 年开始逐月循环。

BEGIN/END 查询正文

查询正文继续使用现有 tree-model SELECT INTO statement。

现有 CQ 限制保持不变，包括自动加入 time filter 和 GROUP BY TIME range。DataNode 最终仍接收具体的 startTime 和 endTime，因此主查询执行引擎不需要感知 calendar duration。

5. 日历运算规则

calendarApply(base, duration, zone) 定义为：

1. 把 base instant 转换为 CQ 持久化时区中的本地日期时间。
2. 使用日历月语义加上 monthPart。
3. 如果目标月份不存在原日期，则钳制到目标月份最后一个有效日期。
4. 按该时区规则转换回 instant。
5. 在当前 timestamp precision 下加上 fixedPart 经过时间。

月末和闰年示例：

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

因为每次 occurrence 都使用 B + n * E 计算，所以原始日期可以在后续合法月份或闰年重新出现。

CQ 持久化的 ZoneId 必须用于创建、正常调度、leader 恢复和 snapshot 恢复。ConfigNode 所在机器的 system default time zone 不能影响结果。

6. 语义校验

保留现有基础约束：

• EVERY > 0
• startOffset > 0
• endOffset >= 0
• startOffset > endOffset
• 保留当前 startOffset >= EVERY 规则
• EVERY 必须满足 continuous_query_minimum_every_interval

日历时长和固定时长不存在永久全序：

二月中 1mo 可能短于 30d
31 天月份中 1mo 可能长于 30d

因此语义校验绝不能继续把一个月压成 30 天。

比较与校验规则：

• 归一化后的 duration 二元结构完全相同时判等。
• 纯 calendar duration 按 monthPart 比较。
• 纯 fixed duration 按 fixedPart 比较。
• 混合比较可以使用保守的最小值/最大值边界。
• 如果无法证明在所有日历位置都满足大小关系，则返回明确的语义错误，不能静默选择 30 天近似。

还需要校验超大月份数量、duration 乘法、接近 Long.MIN_VALUE/Long.MAX_VALUE 的时间戳，以及可能导致 occurrence 搜索不单调或不终止的输入。

7. 使用示例

自然月聚合

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

在 CQ 时区为 Asia/Shanghai，并按上述省略 BOUNDARY 规则，2024-03-01 00:00 的执行覆盖：

[2024-02-01 00:00, 2024-03-01 00:00)

二月窗口会根据年份自动包含 28 或 29 天。

显式月末锚点

CREATE CQ cq_month_end
RESAMPLE
  EVERY 1mo
  BOUNDARY 2024-01-31T00:00:00+08:00
  RANGE 1mo
BEGIN
  SELECT count(s)
  INTO root.result.d(count_s)
  FROM root.source.d
  GROUP BY(1mo)
END

Occurrence 序列：

2024-01-31
2024-02-29
2024-03-31
2024-04-30

自然年聚合

CREATE CQ cq_yearly
RESAMPLE
  EVERY 1y
  BOUNDARY 2020-02-29T00:00:00+08:00
  RANGE 1y
BEGIN
  SELECT avg(s)
  INTO root.result.d(yearly_avg)
  FROM root.source.d
  GROUP BY(1y)
END

非闰年会钳制到 2 月 28 日；当 n * 1y 再次落在闰年时，日期会恢复为 2 月 29 日。

两个 RANGE offset

RESAMPLE EVERY 1mo RANGE 2mo, 1mo

如果锚点为每月 1 日，在 2024-04-01 执行时，查询范围为：

[2024-02-01, 2024-03-01)

8. 持久化、协议和升级行为

兼容与升级实现：

• 在 TCreateCQReq 中为 every、startOffset、endOffset 追加 optional 的结构化 CQ duration。
• 保留现有 i64 字段作为 legacy fallback，不修改原 field id 和 wire type。
• 新 ConfigNode 优先使用结构化值。
• 没有结构化字段的旧请求统一转换为 fixed-only duration。
• CQEntry 保存三组结构化 duration。
• CQInfo snapshot 增加 magic/version。
• 旧 CQInfo snapshot 中的 duration 继续按固定时长读取。
• SHOW CQS 继续返回原始 SQL。
• 不通过重新解析已保存 SQL 的方式自动迁移已持久化且被压平的 1mo/1y CQ。用户可以 drop 并重新 create 以启用 calendar 语义。
• Calendar CQ 只应在所有 ConfigNode 升级完成后允许创建，因为旧 ConfigNode leader 会忽略 optional calendar 字段。

TExecuteCQ 中的 timeout 应使用本次 occurrence 到下一次 occurrence 的实际距离，不能继续使用固定 30 天近似。

9. 实现链路

预期的数据流：

SQL parser
  -> 归一化 TimeDuration(monthPart, fixedPart)
  -> TCreateCQReq optional 结构化字段
  -> 带版本的 CQInfo metadata/snapshot
  -> 基于原始 BOUNDARY 的 calendar scheduler
  -> 具体 startTime/endTime
  -> 现有 DataNode CQ 执行逻辑

主要实现点：

1. 显式 EVERY/RANGE 使用 TimeDuration 解析，不再调用 convertDurationStrToLong 压平。
2. EVERY 从 GROUP BY 继承时保留完整 TimeDuration。
3. CreateContinuousQueryStatement 保存结构化 duration，以及用户是否显式指定 BOUNDARY。
4. ConfigNode Thrift request 增加向后兼容的 optional duration 字段。
5. ConfigNode 在一个统一入口中归一化 legacy 和 structured request。
6. CQEntry 保存结构化 duration，并为 CQ snapshot 增加版本。
7. 将 calendar occurrence/window 计算提取成无系统时间依赖、可确定性单测的纯 helper。
8. CreateCQProcedure 和 CQScheduleTask 恢复时，根据原始 BOUNDARY 推导下一 occurrence，不能继续使用 lastExecutionTime + everyInterval。
9. TExecuteCQ 和 DataNode 执行仍只使用具体时间戳。
10. 功能语义确认后，在独立的 apache/iotdb-docs 仓库同步更新文档。

10. 需要与 maintainer 对齐的实现细节

在编码前，我们需要与 maintainer 对齐以下实现细节：

1. Calendar EVERY 省略 BOUNDARY 时，是按上述实现自动对齐本地日历边界，还是继续使用 Unix timestamp 0 的精确 instant 语义。
2. RANGE 是采用基于原始 BOUNDARY 的 duration 向量公式，还是从每次已经钳制的 executionTime 直接减 offset。
3. 对随日期变化的 calendar/fixed 混合比较，是否采用保守拒绝策略。
4. 已经持久化的旧 CQ 是否保持 legacy fixed 行为，直到用户重新创建。
5. 除 mo/y 外，是否要求同时支持 month/year 全称。

测试计划覆盖：显式及从 GROUP BY 继承的 1mo/1y、省略 RANGE、Jan-31、Feb-29、28/29/30/31 天月份、UTC/Asia-Shanghai/America-New-York、DST 切换、ms/us/ns 精度、BLOCKED/DISCARD 追赶、fixed duration 回归、procedure/plan 序列化、leader 恢复、新 snapshot round trip，以及包含多条 CQ 的 legacy snapshot fixture。