使用TimescaleDB获取数据表指定时间点的状态

首先创建测试表并插入数据：

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl(date_time TIMESTAMPTZ, position TEXT);
INSERT INTO tbl(date_time, position)
VALUES
    ('2022-01-01 11:00'::TIMESTAMPTZ, 'UP'),
    ('2022-01-01 13:00'::TIMESTAMPTZ, 'LEFT'),
    ('2022-01-05 01:00'::TIMESTAMPTZ, 'DOWN'),
    ('2022-01-08 10:00'::TIMESTAMPTZ, 'RIGHT')
;

需求是查询任意特定时间点对应的position值。

尝试的查询及问题 #

执行以下查询：

WITH tbl_state AS(
    SELECT 
        time_bucket('PT1M', date_time) as bucket,
        date_time,
        position,
        state_agg(date_time, position) as position_state
    FROM
        tbl
    GROUP BY 
        date_time,
        position
)
SELECT
    '2022-01-02 11:30'::TIMESTAMPTZ AS time_of_interest,
    state_at(position_state, '2022-01-02 11:30'::TIMESTAMPTZ) AS position_at_time
FROM
    tbl_state
order by 
    date_time
;

得到结果：

time_of_interest    │ position_at_time 
────────────────────────┼──────────────────
 2022-01-02 11:30:00+00 │ UP
 2022-01-02 11:30:00+00 │ LEFT
 2022-01-02 11:30:00+00 │ ∅
 2022-01-02 11:30:00+00 │ ∅

但期望结果是：

time_of_interest    │ position_at_time 
────────────────────────┼──────────────────
 2022-01-02 11:30:00+00 │ UP

当查询时间改为所有记录之后的时间（如2022-01-09 13:30），执行：

SELECT
    '2022-01-09 13:30'::TIMESTAMPTZ AS time_of_interest,
    state_at(position_state, '2022-01-09 13:30'::TIMESTAMPTZ) AS position_at_time
FROM
    tbl_state
order by 
    date_time

得到结果：

time_of_interest    │ position_at_time 
────────────────────────┼──────────────────
 2022-01-09 13:30:00+00 │ UP
 2022-01-09 13:30:00+00 │ LEFT
 2022-01-09 13:30:00+00 │ DOWN
 2022-01-09 13:30:00+00 │ RIGHT

该结果返回所有原始状态，不符合预期（应返回最后一次状态RIGHT）。

解决方案 #

问题出在分组逻辑：按date_time和position分组会让每条原始记录单独生成一个state_agg，每个聚合仅包含单个状态，无法追踪连续的状态变化。正确做法是将所有数据聚合到一个state_agg中，完整记录状态变更时序。

修正后的查询（以查询2022-01-02 11:30为例）：

WITH tbl_state AS(
    SELECT 
        state_agg(date_time, position) as position_state
    FROM
        tbl
)
SELECT
    '2022-01-02 11:30'::TIMESTAMPTZ AS time_of_interest,
    state_at(position_state, '2022-01-02 11:30'::TIMESTAMPTZ) AS position_at_time
FROM
    tbl_state;

查询2022-01-09 13:30的修正查询：

WITH tbl_state AS(
    SELECT 
        state_agg(date_time, position) as position_state
    FROM
        tbl
)
SELECT
    '2022-01-09 13:30'::TIMESTAMPTZ AS time_of_interest,
    state_at(position_state, '2022-01-09 13:30'::TIMESTAMPTZ) AS position_at_time
FROM
    tbl_state;

此时查询2022-01-02 11:30会返回期望的UP，查询2022-01-09 13:30会返回最后一次状态RIGHT，完全符合需求。

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