refresh_continuous_aggregate()
CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.Refresh all buckets of a continuous aggregate in the refresh window given by
window_start
and window_end
.
A continuous aggregate materializes aggregates in time buckets. For example,
min, max, average over 1 day worth of data, and is determined by the time_bucket
interval. Therefore, when
refreshing the continuous aggregate, only buckets that completely fit within the
refresh window are refreshed. In other words, it is not possible to compute the
aggregate over, for an incomplete bucket. Therefore, any buckets that do not
fit within the given refresh window are excluded.
The function expects the window parameter values to have a time type that is
compatible with the continuous aggregate's time bucket expression—for
example, if the time bucket is specified in TIMESTAMP WITH TIME ZONE
, then the
start and end time should be a date or timestamp type. Note that a continuous
aggregate using the TIMESTAMP WITH TIME ZONE
type aligns with the UTC time
zone, so, if window_start
and window_end
is specified in the local time
zone, any time zone shift relative UTC needs to be accounted for when refreshing
to align with bucket boundaries.
Name | Type | Description |
---|---|---|
continuous_aggregate | REGCLASS | The continuous aggregate to refresh. |
window_start | INTERVAL, TIMESTAMPTZ, INTEGER | Start of the window to refresh, has to be before window_end . |
window_end | INTERVAL, TIMESTAMPTZ, INTEGER | End of the window to refresh, has to be after window_start . |
You must specify the window_start
and window_end
parameters differently,
depending on the type of the time column of the hypertable. For hypertables with
TIMESTAMP
, TIMESTAMPTZ
, and DATE
time columns, set the refresh window as
an INTERVAL
type. For hypertables with integer-based timestamps, set the
refresh window as an INTEGER
type.
Note
A NULL
value for window_start
is equivalent to the lowest changed element in the raw hypertable of the CAgg. A NULL
value for window_end
is equivalent to the largest changed element in raw hypertable of the CAgg. As changed element tracking is performed after the initial CAgg refresh, running CAgg refresh without window_start
and window_end
covers the entire time range.
Warning
Note that it's not guaranteed that all buckets will be updated: refreshes will not take place when buckets are materialized with no data changes or with changes that only occurred in the secondary table used in the JOIN.
Refresh the continuous aggregate conditions
between 2020-01-01
and
2020-02-01
exclusive.
CALL refresh_continuous_aggregate('conditions', '2020-01-01', '2020-02-01');
Alternatively, incrementally refresh the continuous aggregate conditions
between 2020-01-01
and 2020-02-01
exclusive, working in 12h
intervals:
DO$$DECLARErefresh_interval INTERVAL = '12h'::INTERVAL;start_timestamp TIMESTAMPTZ = '2020-01-01T00:00:00Z';end_timestamp TIMESTAMPTZ = start_timestamp + refresh_interval;BEGINWHILE start_timestamp < '2020-02-01T00:00:00Z' LOOPCALL refresh_continuous_aggregate('conditions', start_timestamp, end_timestamp);COMMIT;RAISE NOTICE 'finished with timestamp %', end_timestamp;start_timestamp = end_timestamp;end_timestamp = end_timestamp + refresh_interval;END LOOP;END$$;
Keywords
Found an issue on this page?Report an issue or Edit this page in GitHub.