AS OF SYSTEM TIME

Contribute

• View Page Source
• Edit This Page
• Report Doc Issue

On this page

TheAS 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. SeeUseAS OF SYSTEM TIME to decrease conflicts with long-running queries.

Synopsis

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

• InSELECT clauses, at the very end of theFROM sub-clause. TheFOR locking clause isnot allowed withAS OF SYSTEM TIME.
• InBACKUP, after the parameters of theTO sub-clause.
• InRESTORE, after the parameters of theFROM sub-clause.
• InBEGIN, after theBEGIN keyword.
• InSET, after theSET TRANSACTION keyword.

AS OF SYSTEM TIME cannot be used with:

• Locking reads (SELECT ... FOR UPDATE andSELECT ... FOR SHARE).
• Mutation statements (such asUPDATE orDELETE).

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

Parameters

Thetimestamp argument supports the following formats:

To setAS OF SYSTEM TIME follower_read_timestamp() on all implicit and explicit read-only transactions by default, set thedefault_transaction_use_follower_readssession variable toon. Whendefault_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:

>SELECTname,balanceFROMaccountsWHEREname='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:

>SELECTname,balanceFROMaccountsASOFSYSTEMTIME'2016-10-03 12:45:00'WHEREname='Edna Barath';

+-------------+---------+|    name     | balance |+-------------+---------+| Edna Barath |     450 || Edna Barath |    2000 |+-------------+---------+

Using different timestamp formats

Assuming the following statements are run at2016-01-01 12:00:00, they would execute as of2016-01-01 08:00:00:

>SELECT*FROMtASOFSYSTEMTIME'2016-01-01 08:00:00'

>SELECT*FROMtASOFSYSTEMTIME1451635200000000000

>SELECT*FROMtASOFSYSTEMTIME'1451635200000000000'

>SELECT*FROMtASOFSYSTEMTIME'-4h'

>SELECT*FROMtASOFSYSTEMTIMEINTERVAL'-4h'

Selecting from multiple tables

When selecting over multiple tables in a singleFROM clause, theASOF SYSTEM TIME clause must appear at the very end and applies to theentireSELECT clause.

For example:

>SELECT*FROMt,u,vASOFSYSTEMTIME'-4h';

>SELECT*FROMtJOINuONt.x=u.yASOFSYSTEMTIME'-4h';

>SELECT*FROM(SELECT*FROMt),(SELECT*FROMu)ASOFSYSTEMTIME'-4h';

UsingAS OF SYSTEM TIME in subqueries

To enable time travel, theAS 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 allowsAS OF SYSTEM TIME in sub-queries under thefollowing conditions:

• The top level query also specifiesAS OF SYSTEM TIME.
• All theAS OF SYSTEM TIME clauses specify the same timestamp.

For example:

>SELECT*FROM(SELECT*FROMtASOFSYSTEMTIME'-4h')tpJOINuONtp.x=u.yASOFSYSTEMTIME'-4h'-- same timestamp as above - OK.WHEREx<123;

UseAS OF SYSTEM TIME in transactions

You can use theBEGIN statement to execute the transaction using the database contents "as of" a specified time in the past.

>BEGINASOFSYSTEMTIME'2019-04-09 18:02:52.0+00:00';

>SELECT*FROMorders;

>SELECT*FROMproducts;

>COMMIT;

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

>BEGIN;

>SETTRANSACTIONASOFSYSTEMTIME'2019-04-09 18:02:52.0+00:00';

>SELECT*FROMorders;

>SELECT*FROMproducts;

>COMMIT;

UseAS 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 whengarbage collection begins:

>CREATEDATABASEfoo;

CREATE DATABASETime: 3ms total (execution 3ms / network 0ms)

>CREATETABLEfoo.bar(idINTPRIMARYKEY);

CREATE TABLETime: 4ms total (execution 3ms / network 0ms)

>INSERTINTOfoo.barVALUES(1),(2);

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

>SELECTnow();

              now--------------------------------  2022-02-01 21:11:53.63771+00(1 row)Time: 1ms total (execution 0ms / network 0ms)

>DROPTABLEfoo.bar;

DROP TABLETime: 45ms total (execution 45ms / network 0ms)

>SELECT*FROMfoo.barASOFSYSTEMTIME'2022-02-01 21:11:53.63771+00';

  id------   1   2(2 rows)Time: 2ms total (execution 2ms / network 0ms)

>SELECT*FROMfoo.bar;

ERROR: relation "foo.bar" does not existSQLSTATE: 42P01

Known limitations

• CockroachDB does not support placeholders inAS OF SYSTEM TIME. The time value must be a constant value embedded in the SQL string.#30955
• TheANALYZE alias ofCREATE STATISTICS does not support specifying anAS OF SYSTEM TIME timestamp.ANALYZE statements useAS OF SYSTEM TIME '-0.001ms' automatically. For more control over the statistics interval, use theCREATE STATISTICS syntax instead.#96430

See also

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