> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# timescaledb_information.dimensions

> Get information on the dimensions of hypertables

export const TIMESCALE_DB = 'TimescaleDB';

export const HYPERTABLE = 'hypertable';

<Icon icon="tag" iconType="duotone" /> Since [0.1.0][tsdb-0.1.0]

Returns information about the dimensions of a {HYPERTABLE}. {HYPERTABLE}s can be
partitioned on a range of different dimensions. By default, all {HYPERTABLE}s are
partitioned on time, but it is also possible to partition on other dimensions in
addition to time.

For {HYPERTABLE}s that are partitioned solely on time,
`timescaledb_information.dimensions` returns a single row of metadata. For
{HYPERTABLE}s that are partitioned on more than one dimension, the call returns a
row for each dimension.

For time-based dimensions, the metadata returned indicates the integer datatype,
such as BIGINT, INTEGER, or SMALLINT, and the time-related datatype, such as
TIMESTAMPTZ, TIMESTAMP, or DATE. For space-based dimension, the metadata
returned specifies the number of `num_partitions`.

If the {HYPERTABLE} uses time data types, the `time_interval` column is defined.
Alternatively, if the {HYPERTABLE} uses integer data types, the `integer_interval`
and `integer_now_func` columns are defined.

## Samples

Get information about the dimensions of {HYPERTABLE}s.

```sql theme={"dark"}
-- Create a range and hash partitioned hypertable
CREATE TABLE dist_table(time timestamptz, device int, temp float);
SELECT create_hypertable('dist_table', by_range('time', INTERVAL '7 days'));
SELECT add_dimension('dist_table', by_hash('device', 3));

SELECT * from timescaledb_information.dimensions
  ORDER BY hypertable_name, dimension_number;

-[ RECORD 1 ]-----+-------------------------
hypertable_schema | public
hypertable_name   | dist_table
dimension_number  | 1
column_name       | time
column_type       | timestamp with time zone
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
-[ RECORD 2 ]-----+-------------------------
hypertable_schema | public
hypertable_name   | dist_table
dimension_number  | 2
column_name       | device
column_type       | integer
dimension_type    | Space
time_interval     |
integer_interval  |
integer_now_func  |
num_partitions    | 2
```

<Info>
  The `by_range` and `by_hash` dimension builders are an addition to {TIMESCALE_DB} 2.13.
</Info>

Get information about dimensions of a {HYPERTABLE} that has two time-based dimensions.

```sql theme={"dark"}
CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer) WITH (
  tsdb.hypertable,
  tsdb.partition_column = 'a_col'
);
SELECT add_dimension('hyper_2dim', by_range('b_col', INTERVAL '7 days'));

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

-[ RECORD 1 ]-----+----------------------------
hypertable_schema | public
hypertable_name   | hyper_2dim
dimension_number  | 1
column_name       | a_col
column_type       | date
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
-[ RECORD 2 ]-----+----------------------------
hypertable_schema | public
hypertable_name   | hyper_2dim
dimension_number  | 2
column_name       | b_col
column_type       | timestamp without time zone
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
```

## Returns

| 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 a time based or space based dimension                                               |
| `time_interval`     | INTERVAL | Time interval for primary dimension if the column type is a time datatype                   |
| `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 an integer datatype |
| `num_partitions`    | SMALLINT | Number of partitions for the dimension                                                      |

<Info>
  The `time_interval` and `integer_interval` columns are not applicable for space
  based dimensions.
</Info>

[tsdb-0.1.0]: https://github.com/timescale/timescaledb/releases/tag/0.1.0