CODEWITHSUNDEEP
timescaledb
Harsh Sharma
Harsh Sharma

Reputation: 1

How to create a continuous aggregate refresh policy when dealing with integer time?

When dealing with integer time (in millisecond), what values should be passed to the start_offset and end_offset while creating an automatic refresh policy for a Continuous Aggregate ?.

Let say, I have a Continuous Aggregate minutely aggregated and I want to refresh it every 1 minutes with refresh window:
start_offset - INTERVAL '1 hour '
end_offset - INTERVAL '1 minute'

Should I pass : start_offset = 3600000 (millisecond) & end_offset = 60000 (millisecond) when the time is in millisecond ?

I tried the following query:

SELECT add_continuous_aggregate_policy('order_latency_minutely',
  start_offset => 360000,
  end_offset => 60000,
  schedule_interval => INTERVAL '2 minute')
 ;

Its not working.

I have checked the logs and it says job executed successfully but when I check the materialized hypertable, the data is still not materialized.

Upvotes: 0

Views: 182

Answers (1)

kittur_riyaz
kittur_riyaz

Reputation: 162

Based on start_offset, end_offset of refresh policy and time_bucket interval used in continuous aggregate, timescaledb calculates the window for running the continuous aggregate. Here is an example of log

2023-07-28 11:45:12.209 UTC [2648] LOG: refreshing continuous aggregate "order_latency_minutely" in window [ 2023-07-28 10:00:00+00, 2023-07-28 11:00:00+00 ]

Refresh is running at 11:45 but considered the window to refresh as start: 2023-07-28 10:00:00+00 end : 2023-07-28 11:00:00+00

Please refer the window in the timescaledb log and define the start and endoffset as per the requirement.

Upvotes: 0

Related Questions