TimescaleDB - get retention policy and chunk_time_interval for a table
Shows how to fix timescaleDB - get retention policy and chunk_time_interval for a table. Use it when you hit this exact problem. Skip it when your error message or symptom looks different.
TL;DR
You can get this information about retention policies through the jobs view: SELECT schedule\interval, config FROM timescaledb\information.jobs WHERE hypertable\name = 'conditions' AND timescaledb\information.jobs.proc\name = 'policy\retention'; The output will be something like this: ┌───────────────────┬───────────────────────────────────────────────┐ │ schedule\interval │ config │ ├───────────────────┼───────────────────────────────────────────────┤ │ 1 day │ {"drop\after": "6 mons", "hypertable\_id": 26} │ └───────────────────┴───────────────────────────────────────────────┘ To get
Steps
- You can get this information about retention policies through the jobs view:
SELECT schedule_interval, config FROM timescaledb_information.jobs WHERE hypertable_name = 'conditions' AND timescaledb_information.jobs.proc_name = 'policy_retention';The output will be something like this:┌───────────────────┬───────────────────────────────────────────────┐ │ schedule_interval │ config │ ├───────────────────┼───────────────────────────────────────────────┤ │ 1 day │ {"drop_after": "6 mons", "hypertable_id": 26} │ └───────────────────┴───────────────────────────────────────────────┘To get the hypertable, you can get the info fromdimensionsview:select time_interval from timescaledb_information.dimensions where hypertable_name = 'conditions'; ┌───────────────┐ │ time_interval │ ├───────────────┤ │ 1 day │ └───────────────┘ (1 row)
2. in TimescaleDB, how can I retrieve the length of both intervals (`chunk_time_interval` and retention policy interval) using a select statement, providing the table name?
## When to use
Use this skill when you run into "TimescaleDB - get retention policy and chunk_time_interval for a table".
## When not to use
If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.
## Versions
No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.
## Why this happens
The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.