The 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").
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 theFROM
sub-clause. - In
BACKUP
, after the parameters of theTO
sub-clause. - In
RESTORE
, after the parameters of theFROM
sub-clause.
Currently, CockroachDB does not support AS OF SYSTEM TIME
in
explicit transactions. This limitation may be
lifted in the future.
Parameters
The timestamp
argument supports the following formats:
Format | Notes |
---|---|
INT |
Nanoseconds since the Unix epoch. |
STRING |
A TIMESTAMP or INT number of nanoseconds. |
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'
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 '2016-01-01 08:00:00';
> SELECT * FROM t JOIN u ON t.x = u.y AS OF SYSTEM TIME '2016-01-01 08:00:00';
> SELECT * FROM (SELECT * FROM t), (SELECT * FROM u) AS OF SYSTEM TIME '2016-01-01 08:00:00';
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 '2016-01-01 08:00:00'), 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 '2016-01-01 08:00:00') tp
JOIN u ON tp.x = u.y
AS OF SYSTEM TIME '2016-01-01 08:00:00' -- same timestamp as above - OK.
WHERE x < 123;
See Also
Tech Note
HLC timestamps can be specified using a 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.