22 Sep 19 · npack ·       Add to Favorites  

Querying Bigquery table FOR SYSTEM_TIME AS OF - Time travel

FOR SYSTEM_TIME AS OF references the historical versions of the table definition and rows that were current at timestamp_expression. The value of timestamp_expression has to be within last 7 days.

The following query returns a historical version of the table from one hour ago.

SELECT * FROM table
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

The following query returns a historical version of the table at an absolute point in time.

SELECT * FROM table
FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';

npack

posted on 22 Sep 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied