Query dei dati cronologici
È possibile eseguire le query utilizzando la riga comandi o l'editor di query all'interno della console Web.
La seguente definizione di tabella viene utilizzata per le interrogazioni di esempio.
CREATE TABLE PRODUCT (PRODUCTID INTEGER, DESC VARCHAR (100), PRICE DECIMAL) DATA_VERSION_RETENTION_TIME 30;
Le seguenti righe vengono inserite in momenti diversi. I tempi di commit degli inserimenti (le date / ore di inserimento o i valori _SYS_START ) sono indicati nei commenti 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
Visualizzazione dei dati con le date / ore di inserimento ed eliminazione
Questo comando SELECT mostra i dati della tabella con i valori di data / ora di inserimento ed eliminazione associati in quel momento quando è stata emessa la query. Le date / ore _SYS_START e _SYS_END sono disponibili solo nelle query di viaggio nel tempo, quindi l'utilizzo di AS OF NOW ().
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF NOW();
Esempio:
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)
Query dei dati per un tempo specifico con AS OF
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF <TIMESTAMP EXPRESSION>;
Esempio:
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 questo esempio, il prezzo per Shoes è stato modificato dopo la data / ora AS OF specificata. Il sistema restituisce la riga valida precedente.
Vedere anche la clausola secondaria AS OF.
Interrogazione dei dati per un'ora specifica con BEFORE
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BEFORE <TIMESTAMP EXPRESSION>;
Esempio:
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 questo esempio, il prezzo per Shoes è stato modificato dopo o alla data / ora BEFORE. Il sistema restituisce la riga valida precedente.
Vedere anche la clausola secondaria BEFORE.
Query dei dati per tutte le righe in un periodo di tempo
Con il DA ...sottoclausola TO
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME FROM <TIMESTAMP EXPRESSION 1> TO “<TIMESTAMP EXPRESSION 2>” WHERE <condition>;
Se si desidera eseguire la query dei dati cronologici il più lontano possibile, è possibile utilizzare la parola chiave RETENTION_START_TIMESTAMP nelle query di viaggio nel tempo. Se lo fai, puoi evitare di dover provare a calcolare da solo la data / ora corretta. Per estensione, si riduce il rischio di incorrere in un errore se il valore risulta troppo vecchio (più vecchio della data / ora di avvio della conservazione).
Esempio:
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 questo esempio, la query ha cercato tutte le modifiche apportate per PRODUCTID 1004 durante un periodo di tempo specificato, senza includere la data/ora TO.
Consultare anche il DA ...TO clausola secondaria.
Con il BETWEEN ...Clausola secondaria AND
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>;
Esempio:
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 questo esempio, la query ha ricercato tutte le modifiche che si sono verificate nella tabella del prodotto durante un determinato periodo di tempo, fino alla data/ora AND inclusa.
Consultare anche BETWEEN ...AND clausola secondaria.
Recupero delle tabelle
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;
Esempio:
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 questo esempio, si sospetta che siano state apportate modifiche di massa non corrette alla tabella PRODUCT e si desidera ripristinarle.
Ripristino delle righe aggiornate
UPDATE <table> SET <col> = <expression> [, <col> = <expression>...]
FROM (SELECT <col> [, <col> ...] FROM <fromlist> WHERE <condition> FOR SYSTEM_TIME <temporal_clause>) AS <alias>;
Esempio:
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 questo esempio, il prezzo di un prodotto è stato aggiornato in modo non corretto e doveva essere ripristinato.
Vedere anche Time travel query syntax and timestamps.
Ripristino delle righe eliminate
INSERT INTO <table>
SELECT * FROM <table>
WHERE <condition> FOR SYSTEM_TIME <temporal_clause>;
Esempio:
INSERT INTO PRODUCT
SELECT * FROM PRODUCT
WHERE PRODUCTID=’1004’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 12:45:07’;
In questo esempio, un prodotto è stato eliminato in modo non corretto e doveva essere ripristinato.