Creating materialized view using time_bucket to resampling data market

TimescaleDB and PostgreSQL
1

Hello guys, how are you doing?

I have one main table that has the fields “dt” (timestamp), “open”, “high”, “low”, “close” and “volume”. The timeframe of the field “dt” is 120m, line in the example below:

image

See that the field “dt” always starts with 10 AM and finish with 16 PM (the market hours).

I used this script to create the materialized view:

CREATE MATERIALIZED VIEW four_h_bars
WITH (timescaledb.continuous) AS
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
GROUP BY stock_id, "time";

The problem is that I am having different times from the original data, like 08 AM,. Is there some way to fix this and keep the same time of the original data (10am to 16pm)?

Thanks.

2

I forgot to put the result of the view:

image