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?