Given a hypertable
SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');
SELECT add_retention_policy('conditions', INTERVAL '6 months');
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?
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 from dimensions view:
select time_interval from timescaledb_information.dimensions where hypertable_name = 'conditions';
┌───────────────┐
│ time_interval │
├───────────────┤
│ 1 day │
└───────────────┘
(1 row)