Timescaledb: Error creating continuous aggregate view with time_bucket origin/offset parameter

[grafolean]

Relevant system information:

• OS: Ubuntu
• PostgreSQL version (output of postgres --version): 12.3
• TimescaleDB version (output of \dx in psql): 1.7.1
• Installation method: Docker (timescale/timescaledb:latest-pg12)

Describe the bug
I am trying to create a continuous aggregate view using time_bucket with origin parameter set, but I am getting error. It works without origin or offset.

To Reproduce
Steps to reproduce the behavior:

1. Try to create a continuous aggregate view on a table using origin parameter of time_bucket:

# CREATE VIEW test_hourly WITH (timescaledb.continuous) AS SELECT TIME_BUCKET('1 hours'::interval, ts::timestamp, '1970-01-01'::timestamp) AS period, AVG(value), MIN(value), MAX(value) FROM measurements GROUP BY period;

1. See error:

ERROR:  no valid bucketing function found for continuous aggregate query

1. Observe that it works when origin is not specified:

# CREATE VIEW test_hourly WITH (timescaledb.continuous) AS SELECT TIME_BUCKET('1 hours'::interval, ts::timestamp) AS period, AVG(value), MIN(value), MAX(value) FROM measurements GROUP BY period;
CREATE VIEW

Expected behavior
Parameter origin should be allowed (and obeyed).

Actual behavior
Creating a continuous aggregate view fails.

Screenshots
/

Additional context

• same behaviour can be observed with offset (so it can't be used as a workaround)
• (off topic) it would probably be a good idea to document the default value (2000-01-03) of origin in time_bucket docs

[fredericgermain]

I upvote this.
It'd be nice to have aggregation by day not aligned at midnight UTC.

[alefebvre-berner]

I also upvote this. It would be very helpfull to me.
Also please consider having the offset being pass as a column of the table as the timestamp is, not only a fixed value.

[DavidPavlicek]

This is a major feature... definitely needs to be implemented... +1

[erimatnor]

Related to https://github.com/timescale/timescaledb/issues/414