如何调整TimeScale DB跨天4小时时间桶聚合的起始时间？

使用TimeScale DB时，需要创建基于4小时时间桶的连续聚合视图，但默认时间桶从当日午夜开始划分，希望实现跨天的自定义时间桶：每日首个区间为前一天22:00至当日2:00，后续按4小时依次递增（如2:00-6:00、6:00-10:00等）。

源5分钟数据表 #

GBPUSD | 2024-02-04 22:15:00+00 |  1.26291 |  1.26291 |   1.2626 |  1.26275
GBPUSD | 2024-02-04 22:10:00+00 |  1.26345 |  1.26355 |  1.26278 |  1.26297
GBPUSD | 2024-02-04 22:05:00+00 |  1.26355 |  1.26376 |  1.26344 |  1.26344
GBPUSD | 2024-02-02 21:55:00+00 |  1.26339 |  1.26348 |  1.26302 |  1.26302

当前4小时聚合结果 #

GBPUSD | 2024-02-05 04:00:00+00 |  1.26096 |  1.26238 |  1.26034 |  1.26167
GBPUSD | 2024-02-04 20:00:00+00 |  1.26355 |   1.2639 |  1.26113 |  1.26168
GBPUSD | 2024-02-02 20:00:00+00 |  1.26387 |   1.2643 |  1.26302 |  1.26302

当前实现代码 #

CREATE MATERIALIZED VIEW forex4hour
WITH (timescaledb.continuous) AS
SELECT symbol,
       time_bucket(INTERVAL '4 hour', timestamp) AS _4h,
       first(open, timestamp) as open,
       max(high) as high,
       min(low) as low,
       last(close, timestamp) as close
FROM forex5m
GROUP BY symbol, _4h;

SELECT add_continuous_aggregate_policy('forex4hour',
  start_offset => INTERVAL '2 year',
  end_offset => INTERVAL '1hour' ,
  schedule_interval => INTERVAL '1 hour');

要实现自定义跨天时间桶，只需修改time_bucket函数，添加origin参数指定桶的起始基准时间即可。我们需要将基准起始点设为任意一天的22:00（比如'2000-01-01 22:00:00+00'），这样所有4小时桶都会从该基准点开始按4小时递增，自然形成22:00-2:00、2:00-6:00的跨天区间。

修改后的连续聚合视图代码如下：

CREATE MATERIALIZED VIEW forex4hour
WITH (timescaledb.continuous) AS
SELECT symbol,
       -- 指定origin为22:00，让桶从22:00开始按4小时划分
       time_bucket(INTERVAL '4 hour', timestamp, '2000-01-01 22:00:00+00') AS _4h,
       first(open, timestamp) as open,
       max(high) as high,
       min(low) as low,
       last(close, timestamp) as close
FROM forex5m
GROUP BY symbol, _4h;

-- 保留原有的连续聚合策略
SELECT add_continuous_aggregate_policy('forex4hour',
  start_offset => INTERVAL '2 year',
  end_offset => INTERVAL '1hour' ,
  schedule_interval => INTERVAL '1 hour');

效果说明 #

• 对于2024-02-04 22:05:00+00这条数据，会被归入桶2024-02-04 22:00:00+00（对应区间22:00-次日2:00）
• 对于2024-02-02 21:55:00+00这条数据，会被归入桶2024-02-02 18:00:00+00（对应区间18:00-22:00），符合4小时递增的逻辑

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