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

Question

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.

Answer 1

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.