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

Name Type Description
hypertable_schema TEXT Schema name of the hypertable
hypertable_name TEXT Table name of the hypertable
dimension_number BIGINT Dimension number of the hypertable, starting from 1
column_name TEXT 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 TEXT 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.

2. --Create a time and space partitioned hypertable

4. CREATE TABLE dist_table(time timestamptz, device int, temp float);

6. SELECT create_hypertable('dist_table', 'time',  'device', chunk_time_interval=> INTERVAL '7 days', number_partitions=>3);

8. SELECT * from timescaledb_information.dimensions

10. ORDER BY hypertable_name, dimension_number;

12. -[ RECORD 1 ]-----+-------------------------

14. hypertable_schema | public

16. hypertable_name   | dist_table

18. dimension_number  | 1

20. column_name       | time

22. column_type       | timestamp with time zone

24. dimension_type    | Time

26. time_interval     | 7 days

28. integer_interval  |

30. integer_now_func  |

32. num_partitions    |

34. -[ RECORD 2 ]-----+-------------------------

36. hypertable_schema | public

38. hypertable_name   | dist_table

40. dimension_number  | 2

42. column_name       | device

44. column_type       | integer

46. dimension_type    | Space

48. time_interval     |

50. integer_interval  |

52. integer_now_func  |

54. num_partitions    | 2

Get information about dimensions of a hypertable that has two time-based dimensions.

2. CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer);

4. SELECT table_name from create_hypertable('hyper_2dim', 'a_col');

6. SELECT add_dimension('hyper_2dim', 'b_col', chunk_time_interval=> '7 days');

8. SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';

10. -[ RECORD 1 ]-----+----------------------------

12. hypertable_schema | public

14. hypertable_name   | hyper_2dim

16. dimension_number  | 1

18. column_name       | a_col

20. column_type       | date

22. dimension_type    | Time

24. time_interval     | 7 days

26. integer_interval  |

28. integer_now_func  |

30. num_partitions    |

32. -[ RECORD 2 ]-----+----------------------------

34. hypertable_schema | public

36. hypertable_name   | hyper_2dim

38. dimension_number  | 2

40. column_name       | b_col

42. column_type       | timestamp without time zone

44. dimension_type    | Time

46. time_interval     | 7 days

48. integer_interval  |

50. integer_now_func  |

52. num_partitions    |