> ## 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.

# show_chunks()

> Show the chunks belonging to a hypertable

export const CAGG = 'continuous aggregate';

export const HYPERTABLE_CAP = 'Hypertable';

export const HYPERTABLE = 'hypertable';

export const CHUNK = 'chunk';

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

Get the list of {CHUNK}s associated with a {HYPERTABLE}. This function accepts the same
arguments as [`drop_chunks`][drop_chunks].

## Samples

* **Get the list of all {CHUNK}s associated with a table**:

  ```sql theme={"dark"}
  SELECT show_chunks('conditions');
  ```

* **For {HYPERTABLE}s with mixed-case names, include double quotes within the string literal**:

  ```sql theme={"dark"}
  SELECT show_chunks('"MyMixedCaseTable"');
  ```

* **Or with schema qualification**:

  ```sql theme={"dark"}
  SELECT show_chunks('public."MyMixedCaseTable"');
  ```

* **Get all {CHUNK}s from the {HYPERTABLE} called `conditions` that are older than 3 months**:

  ```sql theme={"dark"}
  SELECT show_chunks('conditions', older_than => INTERVAL '3 months');
  ```

* **Get all {CHUNK}s from the {HYPERTABLE} called `conditions` created earlier than 3 months before**:

  ```sql theme={"dark"}
  SELECT show_chunks('conditions', created_before => INTERVAL '3 months');
  ```

* **Get all {CHUNK}s from the {HYPERTABLE} `conditions` created in the previous month**:

  ```sql theme={"dark"}
  SELECT show_chunks('conditions', created_after => INTERVAL '1 month');
  ```

* **Get all {CHUNK}s from the {HYPERTABLE} `conditions` created before 2017**:

  ```sql theme={"dark"}
  SELECT show_chunks('conditions', older_than => DATE '2017-01-01');
  ```

## Arguments

The syntax is:

```sql theme={"dark"}
SELECT show_chunks(
    relation = '<hypertable_or_cagg_name>',
    older_than = <interval>,
    newer_than = <interval>,
    created_before = <interval>,
    created_after = <interval>
);
```

| Name             | Type     | Default | Required | Description                                                                                      |
| ---------------- | -------- | ------- | -------- | ------------------------------------------------------------------------------------------------ |
| `relation`       | REGCLASS | -       | ✔        | {HYPERTABLE_CAP} or {CAGG} from which to select {CHUNK}s.                                        |
| `older_than`     | ANY      | -       | ✖        | Specification of cut-off point where any {CHUNK}s older than this timestamp should be shown.     |
| `newer_than`     | ANY      | -       | ✖        | Specification of cut-off point where any {CHUNK}s newer than this timestamp should be shown.     |
| `created_before` | ANY      | -       | ✖        | Specification of cut-off point where any {CHUNK}s created before this timestamp should be shown. |
| `created_after`  | ANY      | -       | ✖        | Specification of cut-off point where any {CHUNK}s created after this timestamp should be shown.  |

The `older_than` and `newer_than` parameters can be specified in two ways:

* **interval type:** The cut-off point is computed as `now() -
  older_than` and similarly `now() - newer_than`. An error is returned if an
  INTERVAL is supplied and the time column is not one of a TIMESTAMP,
  TIMESTAMPTZ, or DATE.

* **timestamp, date, or integer type:** The cut-off point is explicitly given
  as a TIMESTAMP / TIMESTAMPTZ / DATE or as a SMALLINT / INT / BIGINT. The
  choice of timestamp or integer must follow the type of the {HYPERTABLE}'s time
  column.

The `created_before` and `created_after` parameters can be specified in two ways:

* **interval type:** The cut-off point is computed as `now() -
  created_before` and similarly `now() - created_after`.  This uses
  the {CHUNK} creation time for the filtering.

* **timestamp, date, or integer type:** The cut-off point is
  explicitly given as a `TIMESTAMP` / `TIMESTAMPTZ` / `DATE` or as a
  `SMALLINT` / `INT` / `BIGINT`. The choice of integer value
  must follow the type of the {HYPERTABLE}'s partitioning column. Otherwise
  the {CHUNK} creation time is used for the filtering.

When both `older_than` and `newer_than` arguments are used, the
function returns the intersection of the resulting two ranges. For
example, specifying `newer_than => 4 months` and `older_than => 3
months` shows all {CHUNK}s between 3 and 4 months old.
Similarly, specifying `newer_than => '2017-01-01'` and `older_than => '2017-02-01'` shows all {CHUNK}s between '2017-01-01' and
'2017-02-01'. Specifying parameters that do not result in an
overlapping intersection between two ranges results in an error.

When both `created_before` and `created_after` arguments are used, the
function returns the intersection of the resulting two ranges. For
example, specifying `created_after => 4 months` and `created_before => 3
months` shows all {CHUNK}s created between 3 and 4 months from now.
Similarly, specifying `created_after => '2017-01-01'` and `created_before => '2017-02-01'` shows all {CHUNK}s created between '2017-01-01' and
'2017-02-01'. Specifying parameters that do not result in an
overlapping intersection between two ranges results in an error.

<Note>
  The `created_before`/`created_after` parameters cannot be used together with
  `older_than`/`newer_than`.
</Note>

## Returns

| Column       | Type     | Description                               |
| ------------ | -------- | ----------------------------------------- |
| show\_chunks | REGCLASS | Name of the {CHUNK} matching the criteria |

This function returns a set of rows, one for each {CHUNK} that matches the specified criteria.

On failure, an error is returned:

| Error                                        | Description                                                                    |
| -------------------------------------------- | ------------------------------------------------------------------------------ |
| `invalid hypertable or continuous aggregate` | The specified relation is not a valid {HYPERTABLE} or {CAGG}                   |
| `invalid time range`                         | The specified time range parameters do not result in a valid overlapping range |

[drop_chunks]: /api-reference/timescaledb/hypertables/drop_chunks

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