add_continuous_aggregate_policy()
CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.Create a policy that automatically refreshes a continuous aggregate. To view the policies that you set or the policies that already exist, see informational views.
Name | Type | Description |
---|---|---|
continuous_aggregate | REGCLASS | The continuous aggregate to add the policy for |
start_offset | INTERVAL or integer | Start of the refresh window as an interval relative to the time when the policy is executed. NULL is equivalent to MIN(timestamp) of the hypertable. |
end_offset | INTERVAL or integer | End of the refresh window as an interval relative to the time when the policy is executed. NULL is equivalent to MAX(timestamp) of the hypertable. |
schedule_interval | INTERVAL | Interval between refresh executions in wall-clock time. Defaults to 24 hours |
initial_start | TIMESTAMPTZ | Time the policy is first run. Defaults to NULL. If omitted, then the schedule interval is the intervalbetween the finish time of the last execution and the next start. If provided, it serves as the origin with respect to which the next_start is calculated |
timezone | TEXT | A valid time zone. If initial_start is also specified, subsequent executions of the refresh policy will be aligned on its initial start. However, daylight savings time (DST) changes may shift this alignment. Set to a valid time zone if this is an issue you want to mitigate. If omitted, UTC bucketing is performed. Defaults to NULL . |
The start_offset
should be greater than end_offset
.
You must specify the start_offset
and end_offset
parameters differently,
depending on the type of the time column of the hypertable:
- For hypertables with
TIMESTAMP
,TIMESTAMPTZ
, andDATE
time columns, set the offset as anINTERVAL
type. - For hypertables with integer-based timestamps, set the offset as an
INTEGER
type.
Important
While setting end_offset
to NULL
is possible, it is not recommended. By default, querying a continuous aggregate returns data between end_offset
and the current time. There is no need to set end_offset
to NULL
. To learn more about how continuous aggregates use real-time aggregation, see the real-time aggregation section.
Name | Type | Description |
---|---|---|
if_not_exists | BOOLEAN | Set to true to issue a notice instead of an error if the job already exists. Defaults to false. |
Column | Type | Description |
---|---|---|
job_id | INTEGER | TimescaleDB background job ID created to implement this policy |
Add a policy that refreshes the last month once an hour, excluding the latest hour from the aggregate. For performance reasons, we recommend that you exclude buckets that see lots of writes:
SELECT add_continuous_aggregate_policy('conditions_summary',start_offset => INTERVAL '1 month',end_offset => INTERVAL '1 hour',schedule_interval => INTERVAL '1 hour');
Keywords
Found an issue on this page?Report an issue or Edit this page in GitHub.