CODEWITHSUNDEEP
offsetmaterialized-viewstimescaledbhypertablecontinuous-aggregates
Swapnil
Swapnil

Reputation: 15

TimeScaleDB continuous aggregate refresh policy end offset is not working as expected

I am new to TimeScaleDB, I have created a continuous aggregate view as

CREATE MATERIALIZED VIEW minute_data 
WITH (timescaledb.continuous)
AS
SELECT
   time_bucket('1 min', time_stamp) as bucket,
   thing_key,
   avg(pulse_l) as avg_pulse_l,
   avg(pulse_h) as avg_pulse_h,
   max(pulse_l) as max_pulse_l,
   max(pulse_h) as max_pulse_h,
   min(pulse_l) as min_pulse_l,
   min(pulse_h) as min_pulse_h,
   count(thing_key) as counts,
   sum(pulse_l) as sum_pulse_l,
   sum(pulse_h) as sum_pulse_h
FROM
 water_meter
GROUP BY thing_key, bucket
WITH NO DATA;

I have created refresh a policy for this view as:

SELECT add_continuous_aggregate_policy('minute_data',
  start_offset => INTERVAL '1 day',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 minute');

Even after setting the end offset to 1 hour I am getting the latest added data in the aggregate.

Refresh LOGS:

 2021-07-14 08:09:39.597 UTC [1268] LOG:  refreshing continuous aggregate "minute_data" in window [ 2021-07-13 08:10:00+00, 2021-07-14 07:09:00+00 ]

But the materialized view contains the data outside the end offset, Although start offset setting is working perfectly

below is the data in materialized view: Materialized view

EDIT: Also I am getting the latest data even before the policy refreshes.

Upvotes: 0

Views: 1357

Answers (2)

jonatasdp
jonatasdp

Reputation: 1412

Now I understand what you mean. And, this is the expected behavior of the query as it live-aggregates all the results. You can watch the results even if the bucket is still open.

Keep in mind that you can do some simple comparison with your actual time to know if the candle is open or not.

Let's try to explore it:

tsdb=> select now();
┌───────────────────────────────┐
│              now              │
├───────────────────────────────┤
│ 2021-07-16 12:50:48.471078+00 │
└───────────────────────────────┘
(1 row)

tsdb=> select time_bucket('1 min', now());
┌────────────────────────┐
│      time_bucket       │
├────────────────────────┤
│ 2021-07-16 12:51:00+00 │
└────────────────────────┘
(1 row)
                                                         ^
tsdb=> select time_bucket('1 min', now()) < now() - interval '1 min' as closed_bucket;
┌───────────────┐
│ closed_bucket │
├───────────────┤
│ f             │
└───────────────┘
(1 row)

Probably you can use some extra column with a similar expression to filter the only closed_bucket.

Upvotes: 0

jonatasdp
jonatasdp

Reputation: 1412

The policy above will run every minute (schedule_interval). When it runs, it will materialize data from between 1 day (start_offset) and 1 hour (end_offset) of the time it executes, according to the query which defined the continuous aggregate.

Even after setting the end offset to 1 hour I am getting the latest added data in the aggregate.

Refreshing the information every minute will make the latest information available in the view every one minute. I see the log time is 08:09:39 and your latest data is from 08:08:00. What is inconsistent there?

Would you mind sharing a bit more details of your expectations here?

Upvotes: 0

Related Questions