AS OF SYSTEM TIME

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

This clause can be used to read historical data (also known as "timetravel queries") and can also be advantageous for performance as it decreasestransaction conflicts. For more details, see SQL Performance BestPractices.

Note:

Historical data is available only within the garbage collection window, which is determined by the ttlseconds field in the replication zone configuration.

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.
• In BACKUP, after the parameters of the TO sub-clause.
• In RESTORE, after the parameters of the FROM sub-clause.
• New in v19.1 In BEGIN, after the BEGIN keyword.
• New in v19.1 In SET, after the SET TRANSACTION keyword.

Parameters

The timestamp argument supports the following formats:

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

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 theentire 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 inat least the top-level statement. It is not valid to use it only in asubquery.

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 thefollowing 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;

Using 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;

See also

• Select Historical Data
• Time-Travel Queries

Tech note

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. Theinteger part is the wall time in nanoseconds. The fractional part isthe logical counter, a 10-digit integer. This is the same format asproduced by the cluster_logical_timestamp() function.

Was this page helpful?
YesNo