使用TimescaleDB time_bucket实现含整点数据的小时级能耗聚合

问题原因 #

time_bucket('1h', datetime)默认采用左闭右开的区间规则，例如[00:00, 01:00)，这会导致01:00的整点记录被归入01:00的桶，无法被前一个00:00的桶统计到。你的需求是让每个小时桶包含「当前整点到下一个整点」的所有记录（包括下一个整点的累计值），从而计算该小时的实际能耗（下一个整点累计值 - 当前整点累计值）。

解决方案 #

方案一：调整time_bucket的区间归属（推荐用于连续物化视图） #

通过给datetime偏移一个极小的时间单位（如1微秒），让下一个整点的记录落入前一个小时的桶中，同时手动指定时间区间的起止，确保统计范围准确。

修改后的连续物化视图SQL：

CREATE MATERIALIZED VIEW electricity_hourly
WITH (timescaledb.continuous)
AS SELECT
    time_bucket('1 h'::interval, datetime - interval '1 microsecond') as bucket,   
    bucket as period_begin,
    bucket + interval '1 h' as period_end,
    MAX(energy_use_total) - MIN(energy_use_total) as energy_use_total_processed
FROM source_table 
GROUP BY 1;

原理说明

• datetime - interval '1 microsecond'会将2023-07-26 01:00转换为2023-07-26 00:59:59.999999，使其被归入00:00的桶
• 手动设置period_end = bucket + 1h，确保时间范围显示为[00:00, 01:00]
• 聚合计算时，MAX(energy_use_total)取到下一个整点的累计值，MIN取当前整点的初始值，差值即为该小时的实际能耗

方案二：关联下一个整点值（适合按需查询） #

如果不想调整time_bucket的偏移规则，可以通过子查询关联每个桶结束时刻的整点记录值，直接计算能耗差值。

SQL示例：

WITH hourly_buckets AS (
    SELECT
        time_bucket('1 h'::interval, datetime) as bucket,
        MIN(energy_use_total) as hour_start_total
    FROM source_table
    GROUP BY 1
)
SELECT
    bucket,
    bucket as period_begin,
    bucket + interval '1 h' as period_end,
    COALESCE(
        (SELECT energy_use_total FROM source_table WHERE datetime = bucket + interval '1 h'),
        hour_start_total
    ) - hour_start_total as energy_use_total_processed
FROM hourly_buckets;

原理说明

• 先按标准time_bucket分组，获取每个小时的初始累计值
• 通过子查询找到对应下一个整点的累计值，用COALESCE处理缺失整点记录的边界情况
• 直接计算两个值的差值得到小时能耗

验证结果 #

两种方案都能输出符合预期的结果：

------------------------------------------------------------------------------------------
| bucket            | period_begin      | period_end        | energy_use_total_processed |
-----------------------------------------------------------------------------------------|
| 2023-07-26 00:00  | 2023-07-26 00:00  | 2023-07-26 01:00  | 100                        |
| 2023-07-26 01:00  | 2023-07-26 01:00  | 2023-07-26 02:00  |  40                        |
------------------------------------------------------------------------------------------

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