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 are 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

NameTypeDescription
hypertable_schemaTEXTSchema name of the hypertable
hypertable_nameTEXTTable name of the hypertable
dimension_numberBIGINTDimension number of the hypertable, starting from 1
column_nameTEXTName of the column used to create this dimension
column_typeREGTYPEType of the column used to create this dimension
dimension_typeTEXTIs this time based or space based dimension?
time_intervalINTERVALTime interval for primary dimension if the column type is based on Postgres time datatypes
integer_intervalBIGINTInteger interval for primary dimension if the column type is an integer datatype
integer_now_funcTEXTinteger_now function for primary dimension if the column type is integer based datatype
num_partitionsSMALLINTNumber 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);
  4. SELECT * from timescaledb_information.dimensions
  5. ORDER BY hypertable_name, dimension_number;
  6. -[ RECORD 1 ]-----+-------------------------
  7. hypertable_schema | public
  8. hypertable_name | dist_table
  9. dimension_number | 1
  10. column_name | time
  11. column_type | timestamp with time zone
  12. dimension_type | Time
  13. time_interval | 7 days
  14. integer_interval |
  15. integer_now_func |
  16. num_partitions |
  17. -[ RECORD 2 ]-----+-------------------------
  18. hypertable_schema | public
  19. hypertable_name | dist_table
  20. dimension_number | 2
  21. column_name | device
  22. column_type | integer
  23. dimension_type | Space
  24. time_interval |
  25. integer_interval |
  26. integer_now_func |
  27. num_partitions | 2

Get information about dimensions of a hypertable that has two 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');
  4. SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';
  5. -[ RECORD 1 ]-----+----------------------------
  6. hypertable_schema | public
  7. hypertable_name | hyper_2dim
  8. dimension_number | 1
  9. column_name | a_col
  10. column_type | date
  11. dimension_type | Time
  12. time_interval | 7 days
  13. integer_interval |
  14. integer_now_func |
  15. num_partitions |
  16. -[ RECORD 2 ]-----+----------------------------
  17. hypertable_schema | public
  18. hypertable_name | hyper_2dim
  19. dimension_number | 2
  20. column_name | b_col
  21. column_type | timestamp without time zone
  22. dimension_type | Time
  23. time_interval | 7 days
  24. integer_interval |
  25. integer_now_func |
  26. num_partitions |