timescaledb_information.dimensions

Get metadata about the dimensions of hypertables, returning one row of metadata for each dimension of a hypertable. For a time-and-space-partitioned hypertable, for example, two rows of metadata will be returned for the hypertable.

A time-based dimension column has either an integer datatype (bigint, integer, smallint) or a time related datatype (timestamptz, timestamp, date). The time_interval column is defined for hypertables that use time datatypes. Alternatively, for hypertables that use integer datatypes, the integer_interval and integer_now_func columns are defined.

For space based dimensions, metadata is returned that specifies their number of num_partitions. The time_interval and integer_interval columns are not applicable for space based dimensions.

Available Columns

NameDescription
hypertable_schema(NAME) Schema name of the hypertable
hypertable_name(NAME) Table name of the hypertable
dimension_number(BIGINT) Dimension number of the hypertable, starting from 1
column_name(NAME) Name of the column used to create this dimension
column_type(REGTYPE) Type of the column used to create this dimension
dimension_type(TEXT) Is this time based or space based dimension?
time_interval(INTERVAL) Time interval for primary dimension if the column type is based on Postgres time datatypes
integer_interval(BIGINT) Integer interval for primary dimension if the column type is an integer datatype
integer_now_func(NAME) integer_now function for primary dimension if the column type is integer based datatype
num_partitions(SMALLINT) Number of partitions for the dimension

Sample Usage

Get information about the dimensions of hypertables.

  1. --Create a time and space partitioned hypertable
  2. CREATE TABLE dist_table(time timestamptz, device int, temp float);
  3. SELECT create_hypertable('dist_table', 'time',  'device', chunk_time_interval=> INTERVAL '7 days', number_partitions=>3);
  5. SELECT * from timescaledb_information.dimensions
  6.   ORDER BY hypertable_name, dimension_number;
  8. -[ RECORD 1 ]-----+-------------------------
  9. hypertable_schema | public
  10. hypertable_name   | dist_table
  11. dimension_number  | 1
  12. column_name       | time
  13. column_type       | timestamp with time zone
  14. dimension_type    | Time
  15. time_interval     | 7 days
  16. integer_interval  | 
  17. integer_now_func  | 
  18. num_partitions    | 
  19. -[ RECORD 2 ]-----+-------------------------
  20. hypertable_schema | public
  21. hypertable_name   | dist_table
  22. dimension_number  | 2
  23. column_name       | device
  24. column_type       | integer
  25. dimension_type    | Space
  26. time_interval     | 
  27. integer_interval  | 
  28. integer_now_func  | 
  29. num_partitions    | 2

Get information about dimensions of a hypertable that has 2 time based dimensions

  1. CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer);
  2. SELECT table_name from create_hypertable('hyper_2dim', 'a_col');
  3. SELECT add_dimension('hyper_2dim', 'b_col', chunk_time_interval=> '7 days');
  5. SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';
  7. -[ RECORD 1 ]-----+----------------------------
  8. hypertable_schema | public
  9. hypertable_name   | hyper_2dim
  10. dimension_number  | 1
  11. column_name       | a_col
  12. column_type       | date
  13. dimension_type    | Time
  14. time_interval     | 7 days
  15. integer_interval  | 
  16. integer_now_func  | 
  17. num_partitions    | 
  18. -[ RECORD 2 ]-----+----------------------------
  19. hypertable_schema | public
  20. hypertable_name   | hyper_2dim
  21. dimension_number  | 2
  22. column_name       | b_col
  23. column_type       | timestamp without time zone
  24. dimension_type    | Time
  25. time_interval     | 7 days
  26. integer_interval  | 
  27. integer_now_func  | 
  28. num_partitions    |