Interrogation des données d'historique

Vous pouvez exécuter les requêtes à l'aide de la ligne de commande ou de l'éditeur de requête dans la console Web.

La définition de table suivante est utilisée pour les exemples de requête.

CREATE TABLE PRODUCT (PRODUCTID INTEGER, DESC VARCHAR (100), PRICE DECIMAL) DATA_VERSION_RETENTION_TIME 30;

Les lignes suivantes sont insérées à des moments différents. Les temps de validation des insertions (horodatages d'insertion ou valeurs _SYS_START ) sont indiqués dans les commentaires 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

Affichage des données avec les horodatages d'insertion et de suppression

Cette commande SELECT affiche les données de table avec les valeurs d'horodatage d'insertion et de suppression associées à l'instant où la requête a été émise. Les horodatages _SYS_START et _SYS_END ne sont disponibles que dans les requêtes de déplacement dans le temps, d'où l'utilisation de AS OF NOW ().

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF NOW();

Exemple :

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)

Interrogation des données pour une heure spécifique avec le paramètre AS OF

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF <TIMESTAMP EXPRESSION>;

Exemple :

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)

Dans cet exemple, le prix de Shoes a été modifié après l'horodatage AS OF spécifié. Le système renvoie la ligne valide précédente.

Voir aussi la sous-clause AS OF.

Interrogation des données pour une heure spécifique avec AVANT

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BEFORE <TIMESTAMP EXPRESSION>;

Exemple :

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)

Dans cet exemple, le prix de Shoes a été modifié après ou à l'horodatage BEFORE. Le système renvoie la ligne valide précédente.

Voir aussi la sous-clause BEFORE.

Interrogation des données pour toutes les lignes sur une période

Avec le ...Sous-clause TO

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME FROM <TIMESTAMP EXPRESSION 1> TO “<TIMESTAMP EXPRESSION 2>” WHERE <condition>;

Si vous souhaitez interroger les données d'historique aussi loin que possible, vous pouvez utiliser le mot clé RETENTION_START_TIMESTAMP dans vos requêtes de voyage dans le temps. Dans ce cas, vous pouvez éviter d'avoir à essayer de calculer vous-même l'horodatage approprié. Par extension, vous réduisez le risque d'erreur si la valeur s'avère trop ancienne (plus ancienne que l'horodatage de début de conservation).

Exemple :

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)

Dans cet exemple, la requête a recherché toutes les modifications apportées à PRODUCTID 1004 au cours d'une période de temps spécifiée, à l'exclusion de l'horodatage TO.

Voir aussi le FROM ...TO sous-clause.

Avec le ENTRE ...Sous-clause AND

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>;

Exemple :

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)

Dans cet exemple, la requête a recherché toutes les modifications apportées à la table du produit pendant une certaine période de temps, jusqu'à l'horodatage AND inclus.

Voir aussi le ENTRE ...ET sous-clause.

Récupération des 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;

Exemple :

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;

Dans cet exemple, vous soupçonnez que des modifications en bloc incorrectes ont été apportées à la table PRODUCT et vous souhaitez les rétablir.

Restauration des lignes mises à jour

UPDATE <table> SET <col> = <expression> [, <col> = <expression>...]
  FROM (SELECT <col> [, <col> ...] FROM <fromlist> WHERE <condition> FOR SYSTEM_TIME <temporal_clause>) AS <alias>;

Exemple :

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;

Dans cet exemple, le prix d'un produit a été mis à jour de manière incorrecte et devait être restauré.

Voir aussi Time travel query syntax and timestamps.

Restauration des lignes supprimées

INSERT INTO <table>
  SELECT * FROM <table>
  WHERE <condition> FOR SYSTEM_TIME <temporal_clause>;

Exemple :

INSERT INTO PRODUCT
  SELECT * FROM PRODUCT
  WHERE PRODUCTID=’1004’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 12:45:07’;

Dans cet exemple, un produit a été supprimé de manière incorrecte et a dû être restauré.

Syntaxe de requête de déplacement dans le temps et horodatages.