How can we find the time range of a chunk in timescale db?

user_9793021 0
Viewed 855

I have created a hypertable in timescale db and persisted data, and I want to see the time range of the chunks inside. How can I do that ? There are functions like chunk_relation_size_pretty however that shows the size and not time range.

timescaledb postgresql sql
1 Answers

I use this view to get information about chunks in a hypertable. Two versions of it is also available under https://github.com/timescale/timescaledb-extras, so you can check there as well.

CREATE SCHEMA info;

CREATE OR REPLACE VIEW info.chunks AS
SELECT format('%1$I.%2$I', ch.schema_name, ch.table_name)::regclass AS chunk
     , format('%1$I.%2$I', ht.schema_name, ht.table_name)::regclass AS hypertable
     , tstzrange(TIMESTAMPTZ 'epoch' + sl.range_start * INTERVAL '1 microsecond', 
                 TIMESTAMPTZ 'epoch' + sl.range_end * INTERVAL '1 microsecond') AS date_range
     , (SELECT (SELECT spcname FROM pg_tablespace WHERE reltablespace = oid)
          FROM pg_class
     WHERE format('%1$I.%2$I', ch.schema_name, ch.table_name)::regclass = pg_class.oid) AS tablespace
  FROM _timescaledb_catalog.chunk ch
  JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
  JOIN _timescaledb_catalog.dimension di ON di.hypertable_id = ht.id
  JOIN _timescaledb_catalog.chunk_constraint cn ON cn.chunk_id = ch.id
  JOIN _timescaledb_catalog.dimension_slice sl ON cn.dimension_slice_id = sl.id
 WHERE column_type = 'timestamptz'::regtype;
user_7588010
Related