Snowflake PERIOD 数据类型：用原生时间范围查询生效记录

使用原生时间范围来查找生效记录、关联历史记录并分配车队运营时长。

一项费率于 4 月 1 日变更。针对 2 月的报告仍应使用旧费率。若使用独立的开始和结束列，每次查询都必须重建该规则，包括结束日期是否计入。

Snowflake 的 PERIOD 数据类型将这些边界存储为一个有类型的值。其 SQL 函数可让你查询某个日期是否落在某个范围内、两个范围是否重叠以及它们共享哪一部分。PERIOD 及其 函数已正式发布。

下面的示例展示了三种应用：生效日期查找、历史关联和车队值勤时长报告。

具有明确边界的时间范围

INTERVAL 描述的是一个时长，例如八小时。PERIOD 标识的是一个具有开始和结束的范围，例如从周日晚上 10 点到周一早上 6 点的一次班次。

需显式声明元素类型：PERIOD(DATE)、PERIOD(TIME) 或时间戳变体，例如 PERIOD(TIMESTAMP_NTZ)。Snowflake 还支持 TIMESTAMP_LTZ 和 TIMESTAMP_TZ 边界，对于时间和时间戳类型，支持从 0 到 9 的亚秒精度。

你可以使用同一时间类型的两个值构造一个 period：

SELECT PERIOD_CONSTRUCT(
    DATE '2026-01-01', DATE '2026-04-01'
) AS valid_during;

结果为 [2026–01–01, 2026–04–01)。左方括号包含 1 月 1 日；右圆括号排除 4 月 1 日。每个 PERIOD 都使用这种半开区间约定。连续的范围可以在同一边界相接而不发生重叠。

两个边界都必须是有限的，并且开始必须早于结束。空范围或反向范围均无效。NULL 边界并不意味着“仍然有效”。对于持续存在的记录，请选择适合你应用的、有文档说明的有限结束边界约定。

查找某个业务日期当天生效的版本

参考数据的生效日期往往与其到达日期不同。今天加载的一项费率可能适用于下个月。下周收到的一项更正可能适用于过去的某个日期。

对于生效日期查找，请将每个版本的业务有效期范围与其值一起存储。此示例中，5% 的费率持续到 3 月，6% 的费率从 4 月 1 日开始：

WITH rate_history AS (
    SELECT 'RATE_A' AS rate_id, 0.05 AS rate,
           PERIOD(DATE) '[2026-01-01, 2026-04-01)' AS valid_during
    UNION ALL
    SELECT 'RATE_A', 0.06,
           PERIOD(DATE) '[2026-04-01, 2027-01-01)'
)
SELECT rate_id, rate
FROM rate_history
WHERE PERIOD_CONTAINS(valid_during, DATE '2026-02-15');

该查询返回 RATE_A，值为 0.05。将查找日期改为 4 月 1 日，则返回 0.06。该边界仅属于新版本。

此模式同样适用于按生效日期确定的保费或合同条款。你的摄取逻辑仍需创建并关闭版本。PERIOD 会验证每个范围，但它不会阻止同一业务键的版本发生重叠。

按同一业务日期关联历史记录

合同的状态和逾期余额可能在不同日期发生变化。历史报告需要各自在其报告日期适用的版本。关联它们的最新行会回答一个不同的问题。

假设 contract_status 和 overdue_balances 各有一个类型为 PERIOD(DATE) 的 valid_during 列。以下查询将两个历史记录对齐到 9 月 16 日：

WITH reporting_date AS (
    SELECT DATE '2026-09-16' AS business_date
)
SELECT status_history.contract_id, report.business_date,
       status_history.status, balance_history.overdue_amount
FROM reporting_date AS report
JOIN contract_status AS status_history
  ON PERIOD_CONTAINS(status_history.valid_during, report.business_date)
LEFT JOIN overdue_balances AS balance_history
  ON balance_history.contract_id = status_history.contract_id
 AND PERIOD_CONTAINS(balance_history.valid_during, report.business_date);

对于一份在整个 9 月都标记为 ACTIVE 且逾期余额在 [2026-09-10, 2026-09-20) 为 250 的合同，9 月 16 日的报告返回 ACTIVE 和 250。如果没有余额版本覆盖该日期，左连接会保留该合同，并为未匹配的余额返回 NULL。

每个历史记录对于每个键和日期最多应有一个适用版本。如果你还跟踪信息被记录的时间，请针对所选的记录时间快照筛选两个历史记录。你的管道仍负责捕获该历史。用 PERIOD 谓词替换边界比较可提高可读性；任何性能收益都需要针对具体工作负载进行测试。

跨报告周分配车队值勤时间

一名司机从周日晚上 10 点工作到周一早上 6 点。在以周一为起始的报告周下，两小时属于上一周，六小时属于新的一周。将全部八小时都分配到班次的开始日期会误报两个周的合计值。

将值勤窗口和每个报告周表示为时间戳 period，然后对它们求交集。此查询计算对新一周的贡献：

WITH windows AS (
    SELECT
        PERIOD(TIMESTAMP_NTZ)
          '[2026-09-13 22:00:00, 2026-09-14 06:00:00)' AS duty,
        PERIOD(TIMESTAMP_NTZ)
          '[2026-09-14 00:00:00, 2026-09-21 00:00:00)' AS workweek
), allocation AS (
    SELECT PERIOD_INTERSECT(duty, workweek) AS segment
    FROM windows
)
SELECT DATEDIFF('second', PERIOD_BEGIN(segment), PERIOD_END(segment))
           / 3600.0 AS hours_in_workweek
FROM allocation;

结果为 6。与上一周求交集返回 2，总计保留全部八小时。对于全车队报告，请将值勤记录与它们重叠的每个报告周关联，然后再对分配的小时数求和。

这些合计值可用于加班风险报告和人员配置决策。相同的交集模式可用于衡量车辆的维护窗口有多大一部分落在计划运营窗口内。

该示例假设采用单一、一致的时间戳基准且没有偏移量变化。生产系统需要明确的时区策略和权威值勤记录。仅凭行程遥测无法确定带薪时间或工时合规性；这些规则与范围分配保持分离。这里，DATEDIFF 测量所提取边界之间的秒数。它不是 PERIOD 到 INTERVAL 的转换。

从一个范围查询开始

选择一个现有的生效日期查找或重叠计算，并将其与等效的 PERIOD 表达式进行比较。这也为迁移提供了一个原生目标，用于此前用字符串或拆分边界表示的受支持范围操作。验证共享边界和缺失历史：在迁移到排除式结束边界时，包含式的 BETWEEN 谓词需要特别小心。

请使用 PERIOD 文档 了解支持的类型和限制，并使用 函数参考 查看每种操作的示例。

使用 Snowflake 的 PERIOD 数据类型查询时间范围 最初发布于 Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science（发布于 Medium），人们正在通过高亮并回应这个故事来继续对话。