How to aggregate 1 minute candle data having OHLCV into higher timeframe candles (say 5 min, 30 min, 1 hr etc.) using TimeScaleDB?

Question

I am storing this data in TimescaleDB. And my 1 min candles data is not 24x7, but a continuous block which is somewhere in the middle of day (market open - close). Also, assume this market timings are not regular and can be varying if I go back in history.

So I would need a way preferably to aggregate this data using sql queries !

e.g.

For 2023/09/11, I have data from 8:00 AM - 11:10 AM.

• Then 30 min candles should be 8:00 - 8:30, 8:30 - 9:00, .... , 11:00-11:10
• Similarly 1 hr candles should be 8:00-9:00, ... , 11:00-11:10

For 2023/09/12, I have data from 7:40 AM - 10:15 AM.

• Then 30 min candles should be 7:40 - 8:10, 8:10 - 8:40, .... , 11:10-11:15
• Similarly 1 hr candles should be 7:40-8:40, ... , 10:40-11:15

And my aggregate should look like below: 30 min

2023-09-11 8:00 o h l c v 2023-09-11 8:30 o h l c v .. .. 2023-09-11 11:00 o h l c v

2023-09-12 7:40 o h l c v 2023-09-12 8:10 o h l c v

snapshot of my aggregate

I tried aggregates but it will bucket it w.r.t its own 30 min bucket which starts from start of the day

2023-09-12 00:00 o h l c v 2023-09-12 00:30 o h l c v 2023-09-12 01:00 o h l c v ... ... ..

Answer 1

You can use the candlestick_agg to build the aggregation and then you can rollup it to larger timeframes.

Example:

\echo
 drop materialized view candlestick_1d cascade;
 drop materialized view candlestick_1h cascade;
 drop materialized view candlestick_1m cascade;

DROP TABLE "ticks" CASCADE;

CREATE TABLE "ticks" ("time" timestamp with time zone not null, "symbol" text, "price" decimal, "volume" float);

SELECT create_hypertable('ticks', 'time', chunk_time_interval => INTERVAL '1 day');

ALTER TABLE ticks SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time',
  timescaledb.compress_segmentby = 'symbol'
);
CREATE MATERIALIZED VIEW candlestick_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1m', time),
       "ticks"."symbol",
       candlestick_agg(time, price, volume) as candlestick
FROM "ticks"
GROUP BY 1, 2
ORDER BY 1
WITH DATA;

And you can rollup to larger time frames:

CREATE MATERIALIZED VIEW candlestick_1h
WITH (timescaledb.continuous ) AS
SELECT time_bucket('1 hour', "time_bucket"),
       symbol,
       rollup(candlestick) as candlestick 
FROM "candlestick_1m"
GROUP BY 1, 2
ORDER BY 1
WITH NO DATA;

CREATE MATERIALIZED VIEW candlestick_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', "time_bucket"),
       symbol,
       rollup(candlestick) as candlestick
FROM "candlestick_1h"
GROUP BY 1, 2
ORDER BY 1
WITH DATA;

For the time_bucket issue, try to use the origin param.