TimescaleDB使用time_bucket_gapfill与interpolate插值每日0点数据异常问题

你的查询逻辑存在核心错误：

• time_bucket_gapfill('1 day',dtstamp)会将原始数据按天划分时间桶，每个桶的默认起始时间为当日00:00，你对桶内数据取min(value)聚合，相当于直接将1月28号桶内最早的10:00原始值赋值给了桶起始时间00:00，未针对00:00这个精确时间点做插值计算
• 后续的interpolate是基于聚合后的天粒度结果做插值，不是基于原始小时级时序数据计算，所以结果和手动计算的0点插值结果不一致

你需要先生成所有目标0点时间序列，再基于原始时序数据对每个目标0点做插值，不要提前做聚合操作，参考查询语句如下：

SELECT
  target_stamp,
  interpolate(
    NULL,
    (SELECT (dtstamp, value) FROM timedata WHERE dtstamp < target_stamp ORDER BY dtstamp DESC LIMIT 1),
    (SELECT (dtstamp, value) FROM timedata WHERE dtstamp > target_stamp ORDER BY dtstamp ASC LIMIT 1)
  ) AS val
FROM generate_series(
  '2020-01-01 00:00:00+00'::timestamptz,
  '2020-02-01 00:00:00+00'::timestamptz,
  '1 day'::interval
) AS t(target_stamp);

如果需要更高的查询性能，也可以用TimescaleDB内置的asof_join能力关联原始数据后做插值，避免逐行子查询：

WITH target_points AS (
  SELECT
    time_bucket_gapfill('1 day', dtstamp) AS target_stamp
  FROM timedata
  WHERE dtstamp >= '2020-01-01' AND dtstamp <= '2020-02-01'
  GROUP BY target_stamp
)
SELECT
  tp.target_stamp,
  interpolate(td.value, LAG((td.dtstamp, td.value)) OVER (ORDER BY tp.target_stamp), LEAD((td.dtstamp, td.value)) OVER (ORDER BY tp.target_stamp)) AS val
FROM target_points tp
LEFT JOIN timedata td ON td.dtstamp = tp.target_stamp;

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