Missing rows in a continuous aggregate timescaledb table

55 views Asked by At

I have a tick table in timescaledb as follows from which I create a materialized view for 1-minute OHLC bars with its corresponding refresh policy. However, my tick_1min table does not end up having the correct OHLC bar and there are missing bars in the table, as can be seen in below.

CREATE TABLE tick
(
    tstamp         TIMESTAMP WITH TIME ZONE NOT NULL,
    tstamp_micro   INT                      NOT NULL,
    exchange       VARCHAR(100)             NOT NULL,
    symbol         VARCHAR(100)             NOT NULL,
    price          DOUBLE PRECISION         NOT NULL,
    vwap           DOUBLE PRECISION         NOT NULL,
    size           INT                      NOT NULL,
    volume_bought  INT                      NOT NULL,
    volume_sold    INT                      NOT NULL,
    side           SMALLINT                 NOT NULL,
    context        VARCHAR(200) DEFAULT NULL
);
SELECT create_hypertable('tick', 'tstamp');

materialized view for 1-minute OHLC bars

CREATE MATERIALIZED VIEW tick_1min
    WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', tstamp) AS bucket,
       symbol,
       first(tick.price, tick."tstamp")  AS open,
       max(tick.price)                   AS high,
       min(tick.price)                   AS low,
       last(tick.price, tick."tstamp")   AS close
FROM tick
GROUP BY symbol, bucket;

-- Refresh policy
SELECT add_continuous_aggregate_policy('tick_1min',
                                       start_offset => INTERVAL '1 day', --
                                       end_offset => INTERVAL '1 minute',
                                       schedule_interval => INTERVAL '1 minute');

-- Manual refresh
CALL refresh_continuous_aggregate('tick_1min', now() - interval '1 week', now())

Missing rows in tick_1min, e.g., data from 22:25:00 to 22:28:00 is not added.

2024-02-21 22:23:00.000 -0800   MESH4   5008.25 5008.25 5008.25 5008.25
2024-02-21 22:24:00.000 -0800   MESH4   5008.0  5008.25 5008.0  5008.25
2024-02-21 22:29:00.000 -0800   MESH4   5008.0  5008.0  5008.0  5008.0
2024-02-21 22:31:00.000 -0800   MESH4   5008.25 5008.25 5008.25 5008.25
2024-02-21 22:32:00.000 -0800   MESH4   5008.25 5008.25 5008.25 5008.25
2024-02-21 22:34:00.000 -0800   MESH4   5008.0  5008.0  5008.0  5008.0
2024-02-21 22:38:00.000 -0800   MESH4   5008.25 5008.25 5008.25 5008.25
2024-02-21 22:41:00.000 -0800   MESH4   5008.25 5008.25 5008.25 5008.25
2024-02-21 22:44:00.000 -0800   MESH4   5008.0  5008.25 5008.0  5008.25
2024-02-21 22:45:00.000 -0800   MESH4   5008.25 5008.25 5008.25 5008.25
2024-02-21 22:49:00.000 -0800   MESH4   5008.0  5008.0  5008.0  5008.0

I am expecting that even if the tick data is missing for that period, it still produces a new bar with Open=High=Low=Closed=Previous closed.

Could someone kindly let me know how to fix this?

0

There are 0 answers