Querying historical data
You can run the queries by using the command-line or the query editor inside the web console.
The following table definition is used for the example queries.
CREATE TABLE PRODUCT (PRODUCTID INTEGER, DESC VARCHAR (100), PRICE DECIMAL) DATA_VERSION_RETENTION_TIME 30;
The following rows are inserted at different times. The commit times of the inserts (the insert timestamps or _SYS_START values) are indicated in SQL comments.
INSERT INTO PRODUCT VALUES(1001, 'Jacket', 102.00); -- 2020-10-23 16:00:00
INSERT INTO PRODUCT VALUES(1002, 'Gloves', 20.50); -- 2020-10-23 16:05:00
INSERT INTO PRODUCT VALUES(1003, 'Hat', 18.99); -- 2020-10-23 16:10:00
INSERT INTO PRODUCT VALUES(1004, 'Shoes', 125.25); -- 2020-10-23 16:15:00
Showing data with the insert and delete timestamps
This SELECT command shows the table data with the associated insert and delete timestamp values at that instant when the query was issued. The _SYS_START and _SYS_END timestamps are available only in time travel queries, hence the use of AS OF NOW().
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF NOW();
Example:
SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME AS OF NOW();
PRODUCTID | DESCRIPTION | PRICE | _SYS_START | _SYS_END
----------+-------------+---------+---------------------+-----------
1001 | Jacket | 102.00 | 2020-10-23 16:00:00 |
1002 | Gloves | 20.50 | 2020-10-23 16:05:00 |
1003 | Hat | 18.99 | 2020-10-23 16:10:00 |
1004 | Shoes | 125.25 | 2020-10-23 16:15:00 |
(4 rows)
Querying data for a specific time with AS OF
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF <TIMESTAMP EXPRESSION>;
Example:
SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME AS OF '2020-10-23 16:30:00';
PRODUCTID | DESCRIPTION | PRICE | _SYS_START | _SYS_END
-----------+-------------+--------+---------------------+---------------------
1001 | Jacket | 102.00 | 2020-10-23 16:00:00 |
1002 | Gloves | 20.50 | 2020-10-23 16:05:00 |
1003 | Hat | 18.99 | 2020-10-23 16:10:00 |
1004 | Shoes | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
(4 rows)
In this example, the price for Shoes has been modified after the specified AS OF timestamp. The system returns the previous valid row.
See also the AS OF subclause.
Querying data for a specific time with BEFORE
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BEFORE <TIMESTAMP EXPRESSION>;
Example:
SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME BEFORE '2020-10-23 17:00:00';
PRODUCTID | DESCRIPTION | PRICE | _SYS_START | _SYS_END
-----------+-------------+--------+---------------------+---------------------
1001 | Jacket | 102.00 | 2020-10-23 16:00:00 |
1002 | Gloves | 20.50 | 2020-10-23 16:05:00 |
1003 | Hat | 18.99 | 2020-10-23 16:10:00 |
1004 | Shoes | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
(4 rows)
In this example, the price for Shoes has been modified after or at the BEFORE timestamp. The system returns the previous valid row.
See also the BEFORE subclause.
Querying data for all rows over a time period
With the FROM...TO subclause
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME FROM <TIMESTAMP EXPRESSION 1> TO “<TIMESTAMP EXPRESSION 2>” WHERE <condition>;
If you want to query historical data as far back as possible, you can use the RETENTION_START_TIMESTAMP keyword in your time travel queries. If you do this, you can avoid having to try to compute the right timestamp on your own. By extension, you reduce the risk of running into an error if the value turns out to be too old (older than the retention start timestamp).
Example:
SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME FROM RETENTION_START_TIMESTAMP TO '2020-10-23 17:10:00' WHERE PRODUCTID = 1004;
PRODUCTID | DESCRIPTION | PRICE | _SYS_START | _SYS_END
-----------+-------------+--------+---------------------+---------------------
1004 | Shoes | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
1004 | Shoes | 100.00 | 2020-10-23 17:00:00 |
(2 rows)
In this example, the query searched for all the changes that happened for PRODUCTID 1004 during a specified period of time, not including the TO timestamp.
See also the FROM...TO subclause.
With the BETWEEN...AND subclause
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>;
Example:
SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME BETWEEN '2020-10-23 16:00:00' AND '2020-10-23 17:10:00';
PRODUCTID | DESCRIPTION | PRICE | _SYS_START | _SYS_END
-----------+-------------+--------+---------------------+---------------------
1001 | Jacket | 102.00 | 2020-10-23 16:00:00 |
1002 | Gloves | 20.50 | 2020-10-23 16:05:00 |
1003 | Hat | 18.99 | 2020-10-23 16:10:00 |
1004 | Shoes | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
1004 | Shoes | 100.00 | 2020-10-23 17:00:00 |
(5 rows)
In this example, the query searched for all the changes that happened to the product table during a certain period of time, up to and including the AND timestamp.
See also the BETWEEN...AND subclause.
Recovering tables
BEGIN;
ALTER TABLE <table_name> RENAME TO <new_table_name>;
CREATE TABLE <table_ name> AS
SELECT * FROM <new_table_name> FOR SYSTEM_TIME <temporal_clause>;
DROP TABLE <new_table_name>; -- or, keep it for diagnostics
COMMIT;
Example:
BEGIN;
ALTER TABLE PRODUCT RENAME TO PRODUCT_BAK;
CREATE TABLE PRODUCT AS
SELECT * FROM FLIGHT_BAK FOR SYSTEM_TIME AS OF '2022-11-01 11:30:00';
DROP TABLE FLIGHT_BAK; -- or, keep it for diagnostics
COMMIT;
In this example, you suspected that incorrect bulk changes were made to the PRODUCT table, and you wanted to revert them.
Restoring updated rows
UPDATE <table> SET <col> = <expression> [, <col> = <expression>...]
FROM (SELECT <col> [, <col> ...] FROM <fromlist> WHERE <condition> FOR SYSTEM_TIME <temporal_clause>) AS <alias>;
Example:
UPDATE PRODUCT SET PRICE=P.PRICE
FROM (SELECT PRICE FROM PRODUCT WHERE PRODUCTID=’1002’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 09:22:41’) AS P;
In this example, a product’s price was incorrectly updated and needed to be restored.
See also Time travel query syntax and timestamps.
Restoring deleted rows
INSERT INTO <table>
SELECT * FROM <table>
WHERE <condition> FOR SYSTEM_TIME <temporal_clause>;
Example:
INSERT INTO PRODUCT
SELECT * FROM PRODUCT
WHERE PRODUCTID=’1004’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 12:45:07’;
In this example, a product was incorrectly deleted and needed to be restored.