CODEWITHSUNDEEP
lagtimescaledbcontinuous-aggregates
Wintermute
Wintermute

Reputation: 393

TimescaleDB Continuous Aggregate lagging behind

In TimescaleDB I have a continuous aggregate which contains daily averages, so bucket size is 1 day:

CREATE MATERIALIZED VIEW sensors_daily
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
AS
SELECT time_bucket_gapfill('1d', time) AS time,
id, 
average(time_weight('LOCF', time, sensor1)) AS sensor1,
average(time_weight('LOCF', time, sensor2)) AS sensor2,
FROM sensors
GROUP BY time_bucket('1d', time), id;

I also created a continuous aggregate policy to keep the last 30 days and update it daily. It looks like this:

SELECT add_continuous_aggregate_policy('sensors_daily',
start_offset => INTERVAL '30 days',
end_offset => INTERVAL '1 day',
schedule_interval => INTERVAL '1 day');

Now here is my problem: I do not get data up to and including yesterday. I also do not get data for the day before yesterday. The view is always 3 days behind.

When I query latest time on 2022-02-17 in the afternoon

select max(time) from sensors_daily;

I get the 14th

2022-02-14 01:00:00.000 +0100

Querying the job via

SELECT * FROM timescaledb_information.job_stats;

I see it runs succesfully every day shortly after midnight.

last_run_started_at: 2022-02-17 00:12:07.208 +0100
last_successful_finish: 2022-02-17 00:12:51.699 +0100
last_run_status: Success 
last_run_duration: 00:00:44.491458
next_start: 2022-02-18 00:12:51.699 +0100

What do I need to change to get daily data up to and including yesterday?

Edit 2022-02-18 Maybe important: timestamps in the sensors source hyper-table are TIMESTAMPTZ (timestamp with timezone, German).

Upvotes: 1

Views: 677

Answers (1)

Wintermute
Wintermute

Reputation: 393

I finally solved this by reducing end_offset and schedule_interval from 1 day to 6 hours in the continuous aggregate policy.

Upvotes: 0

Related Questions