Error while using origin parameter in time_bucket

TimescaleDB and PostgreSQL
, ,
1

I am building a materialized view using continuous aggregate while also passing origin parameter. Here is the command I use for time-bucketing:
time_bucket('7 day', time, TIMESTAMPTZ '2022-01-03') AS date . I use this command exactly as I found in Timescaledb docs (here: https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/#sample-usage)
However, I get the following error:
ERROR: continuous aggregate view must include a valid time bucket function

My time column in the hypertable is with timezone. I tried the origin parameter with a TIMESTAMP, instead of TIMESTAMPTZ, still the same error.

My continuous aggregate works fine if I exclude the origin parameter. However, I need the origin parameter. It would be great if I can get some help here. Thanks!

Further update:
For example,
the following command works:

CREATE MATERIALIZED VIEW one_week_candle
WITH (timescaledb.continuous) AS
SELECT symbol,
time_bucket('7 day', time) AS date,
FIRST(open, time) as open,
MAX(high) as high,
MIN(low) as low,
LAST(close, time) as close
FROM stockdata
GROUP BY symbol, date;

But the following gives me error:

CREATE MATERIALIZED VIEW one_week_candle
WITH (timescaledb.continuous) AS
SELECT symbol,
time_bucket('7 day', time, TIMESTAMPTZ '2022-01-03') AS date,
FIRST(open, time) as open,
MAX(high) as high,
MIN(low) as low,
LAST(close, time) as close
FROM stockdata
GROUP BY symbol, date;
2

The issue is this. time_bucket with offset and origin parameters does not work in a MATERIALIZED VIEW. Is this known and documented? I cannot find it mentioned anywhere.

3

Thanks for raising the issue ituralde! I’m going to tag this as a docs request and file a GitHub issue for us to look into it! Meanwhile our engineers may be able to help with more concrete suggestions for your use case.

(Great user name, by the way, if that’s the reference I think it is :slight_smile:)

4

Ok! I did find the Error creating continuous aggregate view with time_bucket origin/offset parameter · Issue #2265 · timescale/timescaledb · GitHub on github. I had previously written that it was closed. My bad. The issue is open. And you are thinking right. The greatest general of the Third Age. :wink:

1 Like
5

The issue has been resolved. The crux is

  1. If timestamp in column and you want ORIGIN in your CAGG, one should use time_bucket_ng. This is documented yes.
  2. Casting a string with timestamptz is not required. You can specify time and the timezone as a string. A working example is given below
    For me, the confusion came when examples with SELECT command specified the casting of ORIGIN parameter using TIMESTAMPTZ.
CREATE MATERIALIZED VIEW two_hour_candle_new
WITH (timescaledb.continuous) AS 
SELECT symbol, 
timescaledb_experimental.time_bucket_ng('2 hour', time, '2017-01-02 09:45', 'Asia/Calcutta') as date,
FIRST(open, time) as open,
MAX(high) as high,
MIN(low) as low,
LAST(close, time) as close
FROM stockdata
GROUP BY symbol,date;
NOTICE:  refreshing continuous aggregate "two_hour_candle_new"
HINT:  Use WITH NO DATA if you do not want to refresh the continuous aggregate on creation.