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

# AS OF SYSTEM TIME

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

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 <InternalLink path="performance-best-practices-overview#use-as-of-system-time-to-decrease-conflicts-with-long-running-queries">Use `AS OF SYSTEM TIME` to decrease conflicts with long-running queries</InternalLink>.

<Note>
  Historical data is available only within the garbage collection window, which is determined by the `ttlseconds` field in the <InternalLink path="configure-replication-zones">replication zone configuration</InternalLink>.
</Note>

## Synopsis

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

* In <InternalLink path="select-clause">`SELECT` clauses</InternalLink>, at the very end of the `FROM` sub-clause. The <InternalLink path="select-for-update">`FOR`</InternalLink> locking clause is **not** allowed with `AS OF SYSTEM TIME`.
* In <InternalLink path="backup">`BACKUP`</InternalLink>, after the parameters of the `TO` sub-clause.
* In <InternalLink path="restore">`RESTORE`</InternalLink>, after the parameters of the `FROM` sub-clause.
* In <InternalLink path="begin-transaction">`BEGIN`</InternalLink>, after the `BEGIN` keyword.
* In <InternalLink path="set-transaction">`SET`</InternalLink>, after the `SET TRANSACTION` keyword.

`AS OF SYSTEM TIME` cannot be used with:

* <InternalLink path="select-for-update">Locking reads</InternalLink> (`SELECT ... FOR UPDATE` and `SELECT ... FOR SHARE`).
* <InternalLink path="sql-statements#data-manipulation-statements">Mutation statements</InternalLink> (such as <InternalLink path="update">`UPDATE`</InternalLink> or <InternalLink path="delete">`DELETE`</InternalLink>).

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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| ---------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <InternalLink path="int">`INT`</InternalLink>                    | Nanoseconds since the Unix epoch.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| negative <InternalLink path="interval">`INTERVAL`</InternalLink> | Added to `statement_timestamp()`, and thus must be negative.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| <InternalLink path="string">`STRING`</InternalLink>              | A <InternalLink path="timestamp">`TIMESTAMP`</InternalLink>, <InternalLink path="int">`INT`</InternalLink> of nanoseconds, or negative <InternalLink path="interval">`INTERVAL`</InternalLink>.                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `follower_read_timestamp()`                                      | A <InternalLink path="functions-and-operators">function</InternalLink> that returns the <InternalLink path="timestamp">`TIMESTAMP`</InternalLink> `statement_timestamp() - 4.2s`. Using this function will set the time as close as possible to the present time while remaining safe for <InternalLink path="follower-reads#exact-staleness-reads">exact staleness follower reads</InternalLink>.                                                                                                                                                                                                                                     |
| `with_min_timestamp(TIMESTAMPTZ, [nearest_only])`                | The minimum <InternalLink path="timestamp">timestamp</InternalLink> at which to perform the <InternalLink path="follower-reads#bounded-staleness-reads">bounded staleness read</InternalLink>. 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 <InternalLink path="follower-reads#bounded-staleness-reads">bounded staleness read</InternalLink>. 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` <InternalLink path="set-vars">session variable</InternalLink> 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 <InternalLink path="decimal">`DECIMAL`</InternalLink>. 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.
</Note>

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

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
<InternalLink path="subqueries">subquery</InternalLink>.

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 <InternalLink path="begin-transaction">`BEGIN`</InternalLink> 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 <InternalLink path="set-transaction">`SET`</InternalLink> 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 <InternalLink path="architecture/storage-layer#garbage-collection">garbage collection</InternalLink> 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
```

<Danger>
  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 <InternalLink path="take-full-and-incremental-backups">full or incremental backup</InternalLink> of your cluster.
</Danger>

## Known limitations

* CockroachDB does not support placeholders in `AS OF SYSTEM TIME`<InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink>. The time value must be a constant value embedded in the SQL string.
* The `ANALYZE` alias of <InternalLink path="create-statistics">`CREATE STATISTICS`</InternalLink> does not support specifying an `AS OF SYSTEM TIME`<InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> 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

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