CODEWITHSUNDEEP
postgresqltimescaledb
Thiago Felipe Silveira
Thiago Felipe Silveira

Reputation: 13

postgres + timescaledb + timebucket + marketdata

how are you doing?

I have one table with stock prices where I have the columns "stock_id", "dt" (timestamp), "open", "high", "low", "close" and "volume" like the example below:

enter image description here

Note that the field "dt" has a timeframe of 15m and the market time starts at 10am and ends at 18pm.

I would like to create a materialized view using the function time_bucket (or another one that could result in my requirement) with another time frame. Like 4 hours, for example:

SELECT stock_id, time_bucket(INTERVAL '4 hour', dt) AS "time", first(open, dt) as open, max(high) as high, min(low) as low, last(close, dt) as close, sum(volume) as volume FROM stock_prices where stock_id = 269 GROUP BY stock_id, "time" order by "time" DESC;

Result:

enter image description here

Note that the "dt" field starts with 8am, but I need that starts always in 10am and ends in 18pm (market time).

If I use another time frames like 1 hour, 2 hour, it works fine. Example:

enter image description here

Could you help me?

Thanks a lot!

I tried to use time_bucket_gapfill and did not work also.

Upvotes: 1

Views: 157

Answers (1)

jonatasdp
jonatasdp

Reputation: 1412

You can use continuous_aggregates for it. Here is a complete example using some random data:


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",
       toolkit_experimental.candlestick_agg(time, price, volume) as candlestick
FROM "ticks"
GROUP BY 1, 2
ORDER BY 1
WITH NO DATA;

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

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

    INSERT INTO ticks
    SELECT time, 'SYMBOL', 1 + (random()*30)::int, 100*(random()*10)::int
    FROM generate_series(TIMESTAMP '2022-01-01 00:00:00',
                    TIMESTAMP '2022-02-01 00:01:00',
                INTERVAL '15 min') AS time;

If can use toolkit_experimental.rollup() to group by in a long time frame.

Note that the candlestick object needs to be accessed by function for each attribute.


SELECT time_bucket,
  symbol,
  toolkit_experimental.open(candlestick),
  toolkit_experimental.high(candlestick),
  toolkit_experimental.low(candlestick),
  toolkit_experimental.close(candlestick),
  toolkit_experimental.volume(candlestick)
FROM candlestick_1d
WHERE time_bucket BETWEEN '2022-01-01' and '2022-01-07';

To build the where clause to filter only by the right date you'll need to use extract(hour from...).

Example:

select extract(hour from TIMESTAMP '2022-01-19 10:00');
┌─────────┐
│ extract │
├─────────┤
│      10 │
└─────────┘

And then, for your case, you can build an extra where cl ause in your materialized view to compose this desired scenario.

Upvotes: 1

Related Questions