System info: timescaledb 2.11.0 community, Debian 11, pg15 15.3-1.pgdg110+1
I have the following table:
Table "public.smartmeter"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
timest | timestamp with time zone | | not null |
kwh_180 | double precision | | |
l1_volt | double precision | | |
l2_volt | double precision | | |
l3_volt | double precision | | |
l1_a | double precision | | |
l2_a | double precision | | |
l3_a | double precision | | |
ul2_ul1 | smallint | | |
ul3_ul1 | smallint | | |
il1_ul1 | smallint | | |
il2_ul2 | smallint | | |
il3_ul3 | smallint | | |
freq | real | | |
leistung | smallint | | |
difference | real | | |
kwh_280 | double precision | | |
Example entry:
timest | kwh_180 | l1_volt | l2_volt | l3_volt | l1_a | l2_a | l3_a | ul2_ul1 | ul3_ul1 | il1_ul1 | il2_ul2 | il3_ul3 | freq | leistung
------------------------+------------+---------+---------+---------+------+------+------+---------+---------+---------+---------+---------+------+---------
2023-07-02 23:56:50+02 | 36436.0597 | 233.2 | 233.4 | 232.6 | 1.89 | 0.25 | 2.35 | 124 | 241 | 38 | 52 | 23 | 49.9 | 887
And a Continuous aggreate:
CREATE MATERIALIZED VIEW ca_kwh_tag
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 day', timest, 'Europe/Vienna') as time,
first(smartmeter.kwh_180, timest) as "first",
last(smartmeter.kwh_180, timest) as "last",
last(smartmeter.kwh_180, timest) - first(smartmeter.kwh_180, timest) as "diff"
FROM smartmeter GROUP BY time ORDER by time;
Data into the smartmeter table is inserted every second.
I tried to setup a policy to refresh the CA:
SELECT add_continuous_aggregate_policy('ca_kwh_tag',
start_offset => INTERVAL '3 days',
end_offset => INTERVAL '10 min',
schedule_interval => INTERVAL '10 min');
However, this is not refreshing the aggreate. I get the timestamp out of range error. I am not sure what the correct offsets are, it works with end_offset => NULL but this takes about 1 minute to calculate. Which is about the same as a normal materialized view refresh.
What are the correct parameters, or is there something I need to change with the aggregate?