[Bug]: Time_bucket can't honour offset parameter when used inside the continuous aggregates

[thesumeetkale]

What type of bug is this?

Other

What subsystems and features are affected?

Continuous aggregate

What happened?

I am having an postgres table with data stored for every minute:

System:
Ubuntu 22.04
Postgres 14
Timescaledb 2.11

Schema:
create table if not exists device_data (
id integer NOT NULL,
eventtime bigint NOT NULL,
values numeric[] NOT NULL,
schedule jsonb NOT NULL,
CONSTRAINT device_data_pkey PRIMARY KEY (id, eventtime)
);

Example:

id(int) time(bigint) values(numeric[]) schedule(jsonb)
1 1672531200 {10,11,12,13,14} {"id": "1001", "offset": 1800, "endtime": "00:30:00", "starttime": "00:30:00"}
1 1672531260 {15,16,17,18,19} {"id": "1001", "offset": 1800, "endtime": "00:30:00", "starttime": "00:30:00"}

SELECT create_hypertable('device_data', 'eventtime', 'id', 5, chunk_time_interval => 604800);

CREATE MATERIALIZED VIEW raw.device_view_1day
WITH (timescaledb.continuous, timescaledb.create_group_indexes) AS
SELECT
d.id AS id,
d.schedule->>'id' AS scheduleid,
timescaledb_extension.time_bucket(86400::bigint, d.eventtime, d.schedule->>'offset') AS dstime,
timescaledb_extension.first(d.values, d.eventtime) AS data
FROM device_data d
WHERE
d.eventtime >= EXTRACT(epoch FROM to_timestamp(d.eventtime::double precision)::date +
(d.schedule->>'starttime')::time without time zone)::bigint
AND
d.eventtime <= EXTRACT(epoch FROM to_timestamp(d.eventtime::double precision)::date +
(d.schedule->>'endtime')::time without time zone)::bigint
GROUP BY 1,2,3
ORDER BY 1,3

TimescaleDB version affected

2.11

PostgreSQL version used

14.1

What operating system did you use?

Ubuntu 22.04 x64

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

Time_bucket does not allow offset parameter inside continuous aggregates view.

[lkshminarayanan]

Hi @thesumeetkale

Using time_bucket with origin/offset parameters in a continuous aggregate is currently not supported. There is already an issue open for this - #2265. However I do think that the error message can be improved, so I have opened a new issue for that - #5828.

[lkshminarayanan]

Closing this as a duplicate of #2265.