AS OF SYSTEM TIME

On this page

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") and to improve performance by decreasing transaction conflicts. See Use AS OF SYSTEM TIME to decrease conflicts with long-running queries.

Synopsis

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

• In SELECT clauses, at the very end of the FROM sub-clause. The FOR locking clause is not allowed with AS OF SYSTEM TIME.
• In BACKUP, after the parameters of the TO sub-clause.
• In RESTORE, after the parameters of the FROM sub-clause.
• In BEGIN, after the BEGIN keyword.
• In SET, after the SET TRANSACTION keyword.

AS OF SYSTEM TIME cannot be used with:

• Locking reads (SELECT ... FOR UPDATE and SELECT ... FOR SHARE).
• Mutation statements (such as UPDATE or DELETE).

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

Parameters

The timestamp argument supports the following formats:

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 to on. When default_transaction_use_follower_reads=on and follower reads are enabled, all read-only transactions use follower reads.

Examples

Select historical data (time-travel)

Imagine this example represents the database's current data:

> 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:

> 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:

> SELECT * FROM t AS OF SYSTEM TIME '2016-01-01 08:00:00'

> SELECT * FROM t AS OF SYSTEM TIME 1451635200000000000

> SELECT * FROM t AS OF SYSTEM TIME '1451635200000000000'

> SELECT * FROM t AS OF SYSTEM TIME '-4h'

> SELECT * FROM t AS OF SYSTEM TIME INTERVAL '-4h'

Selecting from multiple tables

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:

> SELECT * FROM t, u, v AS OF SYSTEM TIME '-4h';

> SELECT * FROM t JOIN u ON t.x = u.y AS OF SYSTEM TIME '-4h';

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

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:

> 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 statement to execute the transaction using the database contents "as of" a specified time in the past.

> BEGIN AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';

> SELECT * FROM orders;

> SELECT * FROM products;

> COMMIT;

Alternatively, you can use the SET statement to execute the transaction using the database contents "as of" a specified time in the past.

> BEGIN;

> SET TRANSACTION AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';

> SELECT * FROM orders;

> SELECT * FROM products;

> 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 begins:

> CREATE DATABASE foo;

CREATE DATABASE


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

> CREATE TABLE foo.bar (id INT PRIMARY KEY);

CREATE TABLE


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

> INSERT INTO foo.bar VALUES (1), (2);

INSERT 2


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

> SELECT now();

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


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

> DROP TABLE foo.bar;

DROP TABLE


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

> 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)

> SELECT * FROM foo.bar;

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

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. #30955
• The ANALYZE alias of 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. #96430

See also

• Select Historical Data
• Time-Travel Queries
• Follower Reads
• Follower Reads Topology Pattern

Was this helpful?