> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# AS OF SYSTEM TIME

The `AS OF SYSTEM TIME timestamp` clause causes statements to execute using the database contents "as of" a specified time in the past.

You can use this clause to read historical data (also known as "[time travel queries](https://www.cockroachlabs.com/blog/time-travel-queries-select-witty_subtitle-the_future/)") and to improve performance by decreasing transaction conflicts. See [Use `AS OF SYSTEM TIME` to decrease conflicts with long-running queries](https://docs.cockroachlabs.com/docs/stable/performance-best-practices-overview#use-as-of-system-time-to-decrease-conflicts-with-long-running-queries).

> [!NOTE]
>
> Historical data is available only within the garbage collection window, which is determined by the `ttlseconds` field in the [replication zone configuration](https://docs.cockroachlabs.com/docs/stable/configure-replication-zones.md).

## Synopsis

The `AS OF SYSTEM TIME` clause is supported in multiple SQL contexts, including but not limited to:

* In [`SELECT` clauses](https://docs.cockroachlabs.com/docs/stable/select-clause), at the very end of the `FROM` sub-clause. The [`FOR`](https://docs.cockroachlabs.com/docs/stable/select-for-update) locking clause is **not** allowed with `AS OF SYSTEM TIME`.
* In [`BACKUP`](https://docs.cockroachlabs.com/docs/stable/backup), after the parameters of the `TO` sub-clause.
* In [`RESTORE`](https://docs.cockroachlabs.com/docs/stable/restore), after the parameters of the `FROM` sub-clause.
* In [`BEGIN`](https://docs.cockroachlabs.com/docs/stable/begin-transaction), after the `BEGIN` keyword.
* In [`SET`](https://docs.cockroachlabs.com/docs/stable/set-transaction), after the `SET TRANSACTION` keyword.
* In [`CREATE MATERIALIZED VIEW`](https://docs.cockroachlabs.com/docs/stable/create-view), after the `AS select_stmt` clause.
* In [`REFRESH MATERIALIZED VIEW`](https://docs.cockroachlabs.com/docs/stable/refresh), after the view name.

`AS OF SYSTEM TIME` cannot be used with:

* [Locking reads](https://docs.cockroachlabs.com/docs/stable/select-for-update.md) (`SELECT ... FOR UPDATE` and `SELECT ... FOR SHARE`).
* [Mutation statements](https://docs.cockroachlabs.com/docs/stable/sql-statements.md#data-manipulation-statements) (such as [`UPDATE`](https://docs.cockroachlabs.com/docs/stable/update) or [`DELETE`](https://docs.cockroachlabs.com/docs/stable/delete)).

The preceding statements return an error: `cannot execute {SQL STATEMENT} in a read-only transaction`.

## Parameters

The `timestamp` argument supports the following formats:

| Format | Notes |
| - | - |
| [`INT`](https://docs.cockroachlabs.com/docs/stable/int) | Nanoseconds since the Unix epoch. |
| negative [`INTERVAL`](https://docs.cockroachlabs.com/docs/stable/interval) | Added to `statement_timestamp()`, and thus must be negative. |
| [`STRING`](https://docs.cockroachlabs.com/docs/stable/string) | A [`TIMESTAMP`](https://docs.cockroachlabs.com/docs/stable/timestamp), [`INT`](https://docs.cockroachlabs.com/docs/stable/int) of nanoseconds, or negative [`INTERVAL`](https://docs.cockroachlabs.com/docs/stable/interval). |
| `follower_read_timestamp()` | A [function](https://docs.cockroachlabs.com/docs/stable/functions-and-operators.md) that returns the [`TIMESTAMP`](https://docs.cockroachlabs.com/docs/stable/timestamp) `statement_timestamp() - 4.2s`. Using this function will set the time as close as possible to the present time while remaining safe for [exact staleness follower reads](https://docs.cockroachlabs.com/docs/stable/follower-reads.md#exact-staleness-reads). |
| `with_min_timestamp(TIMESTAMPTZ, [nearest_only])` | The minimum [timestamp](https://docs.cockroachlabs.com/docs/stable/timestamp.md) at which to perform the [bounded staleness read](https://docs.cockroachlabs.com/docs/stable/follower-reads.md#bounded-staleness-reads). The actual timestamp of the read may be equal to or later than the provided timestamp, but cannot be before the provided timestamp. This is useful to request a read from nearby followers, if possible, while enforcing causality between an operation at some point in time and any dependent reads. This function accepts an optional `nearest_only` argument that will error if the reads cannot be serviced from a nearby replica. |
| `with_max_staleness(INTERVAL, [nearest_only])` | The  maximum staleness interval with which to perform the [bounded staleness read](https://docs.cockroachlabs.com/docs/stable/follower-reads.md#bounded-staleness-reads). The timestamp of the read can be at most this stale with respect to the current time. This is useful to request a read from nearby followers, if possible, while placing some limit on how stale results can be. Note that `with_max_staleness(INTERVAL)` is equivalent to `with_min_timestamp(now() - INTERVAL)`. This function accepts an optional `nearest_only` argument that will error if the reads cannot be serviced from a nearby replica. |

To set `AS OF SYSTEM TIME follower_read_timestamp()` on all implicit and explicit read-only transactions by default, set the `default_transaction_use_follower_reads` [session variable](https://docs.cockroachlabs.com/docs/stable/set-vars.md) to `on`. When `default_transaction_use_follower_reads=on` and follower reads are enabled, all read-only transactions use follower reads.

> [!NOTE]
>
> Although the following format is supported, it is not intended to be used by most users: HLC timestamps can be specified using a [`DECIMAL`](https://docs.cockroachlabs.com/docs/stable/decimal). The integer part is the wall time in nanoseconds. The fractional part is the logical counter, a 10-digit integer. This is the same format as produced by the `cluster_logical_timestamp()` function.

## Examples

### Select historical data (time-travel)

Imagine this example represents the database's current data:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT name, balance
    FROM accounts
   WHERE name = 'Edna Barath';
```

```
+-------------+---------+
|    name     | balance |
+-------------+---------+
| Edna Barath |     750 |
| Edna Barath |    2200 |
+-------------+---------+
```

We could instead retrieve the values as they were on October 3, 2016 at 12:45 UTC:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT name, balance
    FROM accounts
         AS OF SYSTEM TIME '2016-10-03 12:45:00'
   WHERE name = 'Edna Barath';
```

```
+-------------+---------+
|    name     | balance |
+-------------+---------+
| Edna Barath |     450 |
| Edna Barath |    2000 |
+-------------+---------+
```

### Using different timestamp formats

Assuming the following statements are run at `2016-01-01 12:00:00`, they would execute as of `2016-01-01 08:00:00`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t AS OF SYSTEM TIME '2016-01-01 08:00:00'
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t AS OF SYSTEM TIME 1451635200000000000
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t AS OF SYSTEM TIME '1451635200000000000'
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t AS OF SYSTEM TIME '-4h'
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t AS OF SYSTEM TIME INTERVAL '-4h'
```

### Selecting from multiple tables

> [!NOTE]
>
> It is not yet possible to select from multiple tables at different timestamps. The entire query runs at the specified time in the past.

When selecting over multiple tables in a single `FROM` clause, the `AS
OF SYSTEM TIME` clause must appear at the very end and applies to the
entire `SELECT` clause.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t, u, v AS OF SYSTEM TIME '-4h';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t JOIN u ON t.x = u.y AS OF SYSTEM TIME '-4h';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM (SELECT * FROM t), (SELECT * FROM u) AS OF SYSTEM TIME '-4h';
```

### Using `AS OF SYSTEM TIME` in subqueries

To enable time travel, the `AS OF SYSTEM TIME` clause must appear in
at least the top-level statement. It is not valid to use it only in a
[subquery](https://docs.cockroachlabs.com/docs/stable/subqueries.md).

For example, the following is invalid:

```
SELECT * FROM (SELECT * FROM t AS OF SYSTEM TIME '-4h'), u
```

To facilitate the composition of larger queries from simpler queries,
CockroachDB allows `AS OF SYSTEM TIME` in sub-queries under the
following conditions:

* The top level query also specifies `AS OF SYSTEM TIME`.
* All the `AS OF SYSTEM TIME` clauses specify the same timestamp.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM (SELECT * FROM t AS OF SYSTEM TIME '-4h') tp
           JOIN u ON tp.x = u.y
           AS OF SYSTEM TIME '-4h'  -- same timestamp as above - OK.
     WHERE x < 123;
```

### Use `AS OF SYSTEM TIME` in transactions

You can use the [`BEGIN`](https://docs.cockroachlabs.com/docs/stable/begin-transaction) statement to execute the transaction using the database contents "as of" a specified time in the past.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BEGIN AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM orders;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM products;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> COMMIT;
```

Alternatively, you can use the [`SET`](https://docs.cockroachlabs.com/docs/stable/set-transaction) statement to execute the transaction using the database contents "as of" a specified time in the past.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BEGIN;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET TRANSACTION AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM orders;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM products;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> COMMIT;
```

### Use `AS OF SYSTEM TIME` to recover recently lost data

It is possible to recover lost data as a result of an online schema change prior to when [garbage collection](https://docs.cockroachlabs.com/docs/stable/architecture/storage-layer.md#garbage-collection) begins:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DATABASE foo;
```

```
CREATE DATABASE

Time: 3ms total (execution 3ms / network 0ms)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE foo.bar (id INT PRIMARY KEY);
```

```
CREATE TABLE

Time: 4ms total (execution 3ms / network 0ms)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO foo.bar VALUES (1), (2);
```

```
INSERT 2

Time: 5ms total (execution 5ms / network 0ms)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT now();
```

```
              now
--------------------------------
  2022-02-01 21:11:53.63771+00
(1 row)

Time: 1ms total (execution 0ms / network 0ms)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE foo.bar;
```

```
DROP TABLE

Time: 45ms total (execution 45ms / network 0ms)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM foo.bar AS OF SYSTEM TIME '2022-02-01 21:11:53.63771+00';
```

```
  id
------
   1
   2
(2 rows)

Time: 2ms total (execution 2ms / network 0ms)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM foo.bar;
```

```
ERROR: relation "foo.bar" does not exist
SQLSTATE: 42P01
```

> [!CAUTION]
>
> Once garbage collection has occurred, `AS OF SYSTEM TIME` will no longer be able to recover lost data. For more long-term recovery solutions, consider taking either a [full or incremental backup](https://docs.cockroachlabs.com/docs/stable/take-full-and-incremental-backups.md) of your cluster.

## Known limitations

* CockroachDB does not support placeholders in `AS OF SYSTEM TIME`. The time value must be a constant value embedded in the SQL string.
* The `ANALYZE` alias of [`CREATE STATISTICS`](https://docs.cockroachlabs.com/docs/stable/create-statistics) does not support specifying an `AS OF SYSTEM TIME` timestamp. `ANALYZE` statements use `AS OF SYSTEM TIME '-0.001ms'` automatically. For more control over the statistics interval, use the `CREATE STATISTICS` syntax instead.

## See also

* [Select Historical Data](https://docs.cockroachlabs.com/docs/stable/select-clause.md#select-historical-data-time-travel)
* [Time-Travel Queries](https://www.cockroachlabs.com/blog/time-travel-queries-select-witty_subtitle-the_future/)
* [Follower Reads](https://docs.cockroachlabs.com/docs/stable/follower-reads.md)
* [Follower Reads Topology Pattern](https://docs.cockroachlabs.com/docs/stable/topology-follower-reads.md)