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

Question

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

Answer 1

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.

Answer 2

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

Check the type of the timestamp column.

Answer 3

I'm sorry for the short answer. This is a problem widely discussed in the Timescale community.

The problem is related to the immutable vs not immutable time bucket function signature dealing with different types of data. So, in resume timestamp is not immutable and timestamptz is immmutable.

Let's navigate on it:

SELECT p.proname
     , PG_GET_FUNCTION_ARGUMENTS(p.oid)
     , CASE p.provolatile
           WHEN 'i'
               THEN 'IMMUTABLE'
           WHEN 's'
               THEN 'STABLE'
           WHEN 'v'
               THEN 'VOLATILE'
       END AS volatility
FROM pg_proc      p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'pg_catalog'
  AND p.proname = 'time_bucket';

will return

  proname  |      pg_get_function_arguments       | volatility
-----------+--------------------------------------+------------
 timestamp | date                                 | IMMUTABLE
 timestamp | timestamp with time zone             | STABLE
 timestamp | date, time without time zone         | IMMUTABLE
 timestamp | timestamp without time zone, integer | IMMUTABLE

As you can see timestamp is not immutable

but if you see timestamptz it' is immutable.

 timestamptz | timestamp with time zone, integer | IMMUTABLE

Maybe this snippet can also shed some light.

SELECT pg_typeof(timezone('UTC', '2024-01-05')::timestamptz)

Resolution: Change your column type to timestamptz.