---
title: drop_chunks() | Tiger Data Docs
description: Delete chunks by time range
---

Since [0.1.0](https://github.com/timescale/timescaledb/releases/tag/0.1.0)

Removes data chunks whose time range falls completely before (or after) a specified time. Shows a list of the chunks that were dropped, in the same style as the `show_chunks` [function](/docs/reference/timescaledb/hypertables/show_chunks/index.md).

chunks are constrained by a start and end time and the start time is always before the end time. A chunk is dropped if its end time is older than the `older_than` timestamp or, if `newer_than` is given, its start time is newer than the `newer_than` timestamp.

Note that, because chunks are removed if and only if their time range falls fully before (or after) the specified timestamp, the remaining data may still contain timestamps that are before (or after) the specified one.

chunks can only be dropped based on their time intervals. They cannot be dropped based on a hash partition.

## Samples

- **Drop all chunks from hypertable `conditions` older than 3 months**:

  ```
  SELECT drop_chunks('conditions', INTERVAL '3 months');
  ```

  Example output:

  ```
                drop_chunks
  ----------------------------------------
   _timescaledb_internal._hyper_3_5_chunk
   _timescaledb_internal._hyper_3_6_chunk
   _timescaledb_internal._hyper_3_7_chunk
   _timescaledb_internal._hyper_3_8_chunk
   _timescaledb_internal._hyper_3_9_chunk
  (5 rows)
  ```

- **Drop all chunks from hypertable `conditions` created before 3 months**:

  ```
  SELECT drop_chunks('conditions', created_before => now() -  INTERVAL '3 months');
  ```

- **Drop all chunks more than 3 months in the future from hypertable `conditions`**:

  This is useful for correcting data ingested with incorrect clocks:

  ```
  SELECT drop_chunks('conditions', newer_than => now() + interval '3 months');
  ```

- **Drop all chunks from hypertable `conditions` before 2017**:

  ```
  SELECT drop_chunks('conditions', '2017-01-01'::date);
  ```

- **Drop all chunks from hypertable `conditions` before 2017, where time column is given in milliseconds from the UNIX epoch**:

  ```
  SELECT drop_chunks('conditions', 1483228800000);
  ```

- **Drop all chunks older than 3 months ago and newer than 4 months ago from hypertable `conditions`**:

  ```
  SELECT drop_chunks('conditions', older_than => INTERVAL '3 months', newer_than => INTERVAL '4 months')
  ```

- **Drop all chunks created 3 months ago and created 4 months before from hypertable `conditions`**:

  ```
  SELECT drop_chunks('conditions', created_before => INTERVAL '3 months', created_after => INTERVAL '4 months')
  ```

- **Drop all chunks older than 3 months ago across all hypertables**:

  ```
  SELECT drop_chunks(format('%I.%I', hypertable_schema, hypertable_name)::regclass, INTERVAL '3 months')
    FROM timescaledb_information.hypertables;
  ```

## Arguments

The syntax is:

```
SELECT drop_chunks(
    relation = '<hypertable_or_cagg_name>',
    older_than = <interval>,
    newer_than = <interval>,
    verbose = true | false,
    created_before = <interval>,
    created_after = <interval>
);
```

| Name             | Type     | Default | Required | Description                                                                                      |
| ---------------- | -------- | ------- | -------- | ------------------------------------------------------------------------------------------------ |
| `relation`       | REGCLASS | -       | ✔        | hypertable or continuous aggregate from which to drop chunks.                                    |
| `older_than`     | ANY      | -       | ✖        | Specification of cut-off point where any chunks older than this timestamp should be removed.     |
| `newer_than`     | ANY      | -       | ✖        | Specification of cut-off point where any chunks newer than this timestamp should be removed.     |
| `verbose`        | BOOLEAN  | `FALSE` | ✖        | Setting to true displays messages about the progress of the drop\_chunks command.                |
| `created_before` | ANY      | -       | ✖        | Specification of cut-off point where any chunks created before this timestamp should be removed. |
| `created_after`  | ANY      | -       | ✖        | Specification of cut-off point where any chunks created after this timestamp should be removed.  |

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 relative to the current 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.

Warning

When using just an interval type, the function assumes that you are removing things *in the past*. If you want to remove data in the future, for example to delete erroneous entries, use a timestamp.

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` drops all chunks between 3 and 4 months old. Similarly, specifying `newer_than => '2017-01-01'` and `older_than => '2017-02-01'` drops all chunks 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` drops all chunks created between 3 and 4 months from now. Similarly, specifying `created_after`=> ‘2017-01-01’`and`created\_before` => '2017-02-01'` drops all chunks 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`.

## Returns

| Column       | Type | Description                                                                 |
| ------------ | ---- | --------------------------------------------------------------------------- |
| drop\_chunks | TEXT | The name of each chunk that was dropped. Returns one row per dropped chunk. |

The function returns a set of chunk names in the format `_timescaledb_internal._hyper_X_Y_chunk`, where each row represents a chunk that was successfully dropped. If no chunks match the specified criteria, the function returns an empty result set.