MariaDB应用时间时态表更新后时间范围重叠问题求助

问题分析 #

你创建的Contracts时态表使用DATE类型存储有效时间边界，执行FOR PORTION OF更新后，出现了区间边界重叠的情况：原记录被拆分为[2024-05-01, 2024-06-01]、[2024-06-01, 2024-07-31]、[2024-07-31, 2024-08-31]，导致查询2024-06-01或2024-07-31时会返回两条记录。这是因为MariaDB对DATE类型的有效时间默认按闭区间处理，边界日期会被多个区间同时包含。

解决方案 #

要实现无重叠的时间区间，需要遵循左闭右开的区间约定（即Contract_End为区间的不包含结束日期），具体操作如下：

1. 调整初始数据的时间边界 #

将原插入语句的Contract_End改为2024-09-01（表示覆盖到2024-08-31）：

INSERT INTO Contracts (Employee_ID, Working_hours, Contract_Begin, Contract_End) 
VALUES (1, 100, '2024-05-01', '2024-09-01');

2. 修改更新语句的时间范围 #

更新时，将目标区间的结束日期设为2024-08-01（表示覆盖到2024-07-31）：

UPDATE Contracts 
FOR PORTION OF valid_time FROM '2024-06-01' TO '2024-08-01' 
SET Working_hours = 145 
WHERE Employee_ID = 1;

3. 验证结果 #

执行更新后，表中的三条记录将是：

• Employee_ID=1, Working_hours=100, Contract_Begin='2024-05-01', Contract_End='2024-06-01'（覆盖2024-05-01至2024-05-31）
• Employee_ID=1, Working_hours=145, Contract_Begin='2024-06-01', Contract_End='2024-08-01'（覆盖2024-06-01至2024-07-31）
• Employee_ID=1, Working_hours=100, Contract_Begin='2024-08-01', Contract_End='2024-09-01'（覆盖2024-08-01至2024-08-31）

此时查询任意日期都只会返回一条记录，区间完全无重叠。

备选方案：使用TIMESTAMP类型提升精度 #

如果必须保留DATE类型的原始边界，也可以将字段类型改为TIMESTAMP，通过时分秒来区分边界：

• 原记录的Contract_End设为2024-06-01 00:00:00
• 更新的起始时间设为2024-06-01 00:00:00，结束时间设为2024-08-01 00:00:00

这种方式同样能避免边界重叠，但会增加数据复杂度，推荐优先使用左闭右开的DATE区间约定。

内容的提问来源于stack exchange，提问作者PeterStrawson