Question 359348 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – ERROR: continuous aggregate view must include a valid time bucket function

I’m encountering an issue while working with PostgreSQL and TimescaleDB. I’m attempting to create a continuous aggregate view, but I keep receiving the following error message:
ERROR: continuous aggregate view must include a valid time bucket function

    CREATE MATERIALIZED VIEW BN_hourly_bars WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', datetime, '15 minutes'::INTERVAL) AS bucket_start,
    stock_code,
    exchange_code,
    product_type,
    expiry_date,
    "right" AS right_option,
    strike_price,
    FIRST(open, datetime) AS open_price,
    MAX(high) AS max_high_price,
    MIN(low) AS min_low_price,
    LAST(close, datetime) AS close_price,
    SUM(volume) AS total_volume,
    SUM(open_interest) AS open_interest
FROM
    ticks_table2
GROUP BY
    bucket_start,
    stock_code,
    exchange_code,
    product_type,
    expiry_date,
    right_option,
    strike_price;

candle start time is : 9:15

Let me know how to fix. Thanks

Tags:

2

Answers


  1. 0

    Error might be due to the second argument of the time_bucket function, which specifies the duration. In your query, you’re using ‘1 hour‘ as the duration, which seems fine, but you’re also specifying ’15 minutes’::INTERVAL as an additional parameter.

    Try removing ‘15 minutes’::INTERVAL from the time_bucket function and see if that resolves the issue.

    Login or Signup to reply.
  2. 0

    The problem is not in the arguments order but in the function return which is not immutable.

    Check the type of the timestamp column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search