CODEWITHSUNDEEP
sqlpostgresqltimescaledb
Denise
Denise

Reputation: 1088

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

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.

Upvotes: 2

Views: 1794

Answers (2)

TimY
TimY

Reputation: 5416

I think this is what you are looking for:

SELECT * FROM timescaledb_information.chunks;

Upvotes: 0

Mats Kindahl
Mats Kindahl

Reputation: 2075

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;

Upvotes: 0

Related Questions