1

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).

Wintermute
  • 394
  • 4
  • 19
  • I am not sure if related to your problem, but there is a specific feature for [real-time aggregates](https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/real-time-aggregates/). You can also force refresh aggregates by hand. – Mikko Ohtamaa Feb 18 '22 at 11:05
  • Is it updating anything if you [manually refresh](https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/refresh-policies/#manually-refresh-a-continuous-aggregate) the caggs? – jonatasdp Feb 18 '22 at 13:03
  • @jonatasdp just tried it. It is kindof weird: When I did refresh 30 days up to today (Feb 18) `CALL refresh_continuous_aggregate('sensors_daily', '2022-01-19', '2022-02-18');` then latest data is '2022-02-16 01:00:00.000 +0100' When I repeat with end set to '2022-02-19' (1 day later, tomorrow), the latest entry I get is '2022-02-17 01:00:00.000 +0100' – Wintermute Feb 18 '22 at 16:58
  • 1
    @MikkoOhtamaa I am aware of this feature, thanks for mentioning. I explicitly disabled real time aggregates via `timescaledb.materialized_only=true` in the create view because I am not interested in real-time data; 1 day old will be perfectly fine. – Wintermute Feb 18 '22 at 17:05
  • Have you checked the hours' difference between your server time zone and the german you're using? maybe it's what is shifting back one day. If you try to isolate your query and only get like limit 1 ordered by date, do you see the date you want? – jonatasdp Feb 21 '22 at 13:06
  • @jonatasdp nope, that's not it. The continuous aggregate is 3 days 'behind'. Today, 22nd I get 19th as latest day. – Wintermute Feb 22 '22 at 12:58
  • My apologies in advance about this stupid question, but, did you really have the data over this time? If you run the query from continuous aggregates, do you get data from yesterday? If you can create a minimum reproducible example, I'd encourage you to fill an issue in the Timescale GitHub. – jonatasdp Feb 24 '22 at 14:28
  • @jonatsdp your help is welcome, thank you! Yes, I *do have* the data I am looking for in the source hyper table. As for reproduction... well I get 15k sensor values every 5 minutes and I have overall data for 1+ year - this would be a little too much for a minimum rerproducible example, I guess. ;-) I will see what I can to reproduce on a smaller scale. – Wintermute Feb 24 '22 at 19:05

1 Answers1

0

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

Wintermute
  • 394
  • 4
  • 19