Consultando dados históricos
É possível executar as consultas usando a linha de comandos ou o editor de consulta dentro do console da Web
A definição de tabela a seguir é usada para as consultas de exemplo.
CREATE TABLE PRODUCT (PRODUCTID INTEGER, DESC VARCHAR (100), PRICE DECIMAL) DATA_VERSION_RETENTION_TIME 30;
As linhas a seguir são inseridas em horários diferentes. Os tempos de commit das inserções (os valores de inserção timestamps ou _SYS_START ) são indicados em comentários SQL.
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
Mostrando dados com os timestamps de inserção e exclusão
Este comando SELECT mostra os dados da tabela com os valores de timestamp de inserção e exclusão associados naquele instante em que a consulta foi emitida. Os timestamps _SYS_START e _SYS_END estão disponíveis apenas em consultas de viagem no tempo, daí o uso de AS OF NOW ().
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF NOW();
Exemplo:
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)
Consultando dados para um tempo específico com AS DE
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF <TIMESTAMP EXPRESSION>;
Exemplo:
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)
Neste exemplo, o preço para Shoes foi modificado após o timestamp AS OF especificado. O sistema retorna a linha válida anterior.
Veja também a subcláusula AS OF.
Consultando dados para um tempo específico com BEFORE
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BEFORE <TIMESTAMP EXPRESSION>;
Exemplo:
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)
Neste exemplo, o preço para Shoes foi modificado após ou no timestamp BEFORE. O sistema retorna a linha válida anterior.
Veja também a subcláusula BEFORE.
Consultando dados para todas as linhas ao longo de um período de tempo
Com o FROM ...TO subcláusula
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME FROM <TIMESTAMP EXPRESSION 1> TO “<TIMESTAMP EXPRESSION 2>” WHERE <condition>;
Se você deseja consultar dados históricos o mais atrás possível, você pode usar a palavra-chave RETENTION_START_TIMESTAMP em suas consultas de viagem no tempo. Se você fizer isso, você pode evitar ter que tentar computar o timestamp certo por conta própria. Por extensão, você reduz o risco de se escorrer em um erro se o valor acaba por ser muito antigo (mais antigo do que o timestamp de início de retenção).
Exemplo:
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)
Neste exemplo, a consulta procurou por todas as alterações que aconteceram para o ID do produto 1004 durante um período especificado de tempo, não incluindo o timestamp TO.
Veja também o FROM ...TO subcláusula.
Com o BETWEEN ...E subcláusula
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>;
Exemplo:
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)
Neste exemplo, a consulta procurou por todas as alterações que aconteceram com a tabela do produto durante um determinado período de tempo, até e incluindo o timestamp AND.
Veja também o BETWEEN ...AND subcláusula.
Recuperação de tabelas
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;
Exemplo:
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;
Neste exemplo, você suspeitou que alterações incorretas em massa foram feitas na tabela PRODUTO, e você quis revertá-las.
Restaurando linhas atualizadas
UPDATE <table> SET <col> = <expression> [, <col> = <expression>...]
FROM (SELECT <col> [, <col> ...] FROM <fromlist> WHERE <condition> FOR SYSTEM_TIME <temporal_clause>) AS <alias>;
Exemplo:
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;
Neste exemplo, um preço do produto foi incorretamente atualizado e necessário para ser restaurado.
Veja também sintaxe de consulta de viagens de tempo e timestamps.
Restaurando linhas deletadas
INSERT INTO <table>
SELECT * FROM <table>
WHERE <condition> FOR SYSTEM_TIME <temporal_clause>;
Exemplo:
INSERT INTO PRODUCT
SELECT * FROM PRODUCT
WHERE PRODUCTID=’1004’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 12:45:07’;
Neste exemplo, um produto foi incorretamente excluído e necessário para ser restaurado.