Gap Filling OHLCV (Open High Low Close Volume) in TimescaleDB
SELECT "tickerId", "ts", coalesce("open", "close") "open", coalesce("high", "close") "high", coalesce("low", "close") "low", coalesce("close", "close") "close", coalesce("volume", 0) "volume", coalesce("count", 0) "count"FROM ( SELECT "tickerId", time_bucket_gapfill('1 hour', at) "ts", first(price, "eId") "open", MAX(price) "high", MIN(price) "low", locf(last(price, "eId")) "close", SUM(volume) "volume", COUNT(1) "count" FROM "PublicTrades" WHERE at >= date_trunc('day', now() - INTERVAL '1 year') AND at < NOW() GROUP BY "tickerId", "ts" ORDER BY "tickerId", "ts" DESC LIMIT 100 ) AS PNotice: eId is Exchange Public Trade ID
You need to specify in each column how to perform the gapfilling. My guess is that you probably want to use locf. See:
https://docs.timescale.com/latest/api#time_bucket_gapfillhttps://docs.timescale.com/latest/api#locf