AS OF SYSTEM TIME timestamp clause causes statements to execute
using the database contents "as of" a specified time in the past.
This clause can be used to read historical data (also known as "time travel queries") and can also be advantageous for performance as it decreases transaction conflicts. For more details, see SQL Performance Best Practices.
Historical data is available only within the garbage collection window, which is determined by the
ttlseconds field in the replication zone configuration.
AS OF SYSTEM TIME clause is supported in multiple SQL contexts,
including but not limited to:
SELECTclauses, at the very end of the
BACKUP, after the parameters of the
RESTORE, after the parameters of the
Currently, CockroachDB does not support
AS OF SYSTEM TIME in
explicit transactions. This limitation may be
lifted in the future.
timestamp argument supports the following formats:
||Nanoseconds since the Unix epoch.|
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
> 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
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
OF SYSTEM TIME clause must appear at the very end and applies to the
> 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';
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
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,
AS OF SYSTEM TIME in sub-queries under the
- The top level query also specifies
AS OF SYSTEM TIME.
- All the
AS OF SYSTEM TIMEclauses specify the same timestamp.
> 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;
Although the following format is supported, it is not intended to be used by most users.
HLC timestamps can be specified using a
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