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

TimescaleDB and PostgreSQL
1

Hi.
I’m trying to create this CA:

create materialized view sum_day 
with (timescaledb.continuous) as
select id, 
       time_bucket('1 day', timecol - interval '1 minute') as myday,
       avg(t)::decimal(5,2) as t, 
       max(tx)::decimal(5,2) as tx
from mytable group by 1,2
with no data;

I get the error
ERROR: continuous aggregate view must include a valid time bucket function.
If I omit the calculation ( - interval ‘1 minute’ ) from the time_bucket, it works.
The SQL- statement runs fine with the correct results. Is this kind of CA not supported?

Thx in advance,
-S

2

Hi @Snorri_Bergmann, yes. if you need to correct the time_bucket reference, you can also use the origin param to fix the date as you want.

Just curious, why is it necessary to shift on minute to past?

3

Thanks @jonatasdp.
Well, the reason is that the record(s) with midnight timestamp should be calculated with the previous day :frowning:
I will read up on the origin parameter,
Have a nice weekend!

4

Hi Snori, in this case you can use the data type timestamptz to set the time zone and use time bucket with time zone parameter.

5

@jonatasdp how would that work?
In case I have data every 10 minutes, I only want the midnight value to be calculated with the previous day, not all the data points for that given hour.

6

Actually, whenever I put some additional parameter in the time_bucket aggregate I get this error, both for
origin and offset :frowning:
Best regards,
-S

7

I only want the midnight value to be calculated with the previous day, not all the data points for that given hour.

Got it. I thought it was just a shift issue. I’m not sure how to approach this with continuous aggregations. I’d create a background action that just simulates continuous aggregates manually.

I get this error,

what error? not sure if you mention it as the previous one, can you please share the error message here?

Have a great weekend too!

8

The previous error message. (ERROR: continuous aggregate view must include a valid time bucket function.)
It seems you can not create CA with any time calculations in the bucket. Is that a fair assumption?
Cheers,
-S