Consulta de datos históricos
Puede ejecutar las consultas utilizando la línea de mandatos o el editor de consultas dentro de la consola web.
La siguiente definición de tabla se utiliza para las consultas de ejemplo.
CREATE TABLE PRODUCT (PRODUCTID INTEGER, DESC VARCHAR (100), PRICE DECIMAL) DATA_VERSION_RETENTION_TIME 30;
Las filas siguientes se insertan en momentos diferentes. Las horas de confirmación de las inserciones (las indicaciones de fecha y hora de inserción o los valores _SYS_START ) se indican en los comentarios de 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
Mostrar datos con las indicaciones de fecha y hora de inserción y supresión
Este mandato SELECT muestra los datos de tabla con los valores de indicación de fecha y hora de inserción y supresión asociados en ese instante en que se emitió la consulta. Las indicaciones de fecha y hora _SYS_START y _SYS_END sólo están disponibles en las consultas de viaje en el tiempo, de ahí el uso de AS OF NOW ().
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF NOW();
Ejemplo:
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)
Consulta de datos para una hora específica con AS OF
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF <TIMESTAMP EXPRESSION>;
Ejemplo:
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)
En este ejemplo, el precio de Shoes se ha modificado después de la indicación de fecha y hora AS OF especificada. El sistema devuelve la fila válida anterior.
Consulte también la subcláusula AS OF.
Consulta de datos para una hora específica con BEFORE
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BEFORE <TIMESTAMP EXPRESSION>;
Ejemplo:
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)
En este ejemplo, el precio para Shoes se ha modificado después o en la indicación de fecha y hora BEFORE. El sistema devuelve la fila válida anterior.
Consulte también la subcláusula BEFORE.
Consulta de datos para todas las filas durante un periodo de tiempo
Con el FROM ...subcláusula TO
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME FROM <TIMESTAMP EXPRESSION 1> TO “<TIMESTAMP EXPRESSION 2>” WHERE <condition>;
Si desea consultar los datos históricos lo más atrás posible, puede utilizar la palabra clave RETENTION_START_TIMESTAMP en las consultas de viaje en el tiempo. Si lo hace, puede evitar tener que intentar calcular la indicación de fecha y hora correcta por su cuenta. Por extensión, reduce el riesgo de que se produzca un error si el valor resulta ser demasiado antiguo (más antiguo que la indicación de fecha y hora de inicio de retención).
Ejemplo:
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)
En este ejemplo, la consulta ha buscado todos los cambios que se han producido para PRODUCTID 1004 durante un periodo de tiempo especificado, sin incluir la indicación de fecha y hora TO.
Consulte también FROM ... subcláusula TO.
Con el BETWEEN ...subcláusula AND
SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>;
Ejemplo:
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)
En este ejemplo, la consulta ha buscado todos los cambios que se han producido en la tabla de productos durante un determinado periodo de tiempo, hasta la indicación de fecha y hora AND inclusive.
Consulte también el BETWEEN ...AND subcláusula.
Recuperación de tablas
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;
Ejemplo:
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;
En este ejemplo, sospecha que se han realizado cambios masivos incorrectos en la tabla PRODUCT y desea revertirlos.
Restauración de filas actualizadas
UPDATE <table> SET <col> = <expression> [, <col> = <expression>...]
FROM (SELECT <col> [, <col> ...] FROM <fromlist> WHERE <condition> FOR SYSTEM_TIME <temporal_clause>) AS <alias>;
Ejemplo:
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;
En este ejemplo, el precio de un producto se ha actualizado incorrectamente y es necesario restaurarlo.
Consulte también Sintaxis de consulta de viaje en el tiempo e indicaciones de fecha y hora.
Restauración de filas suprimidas
INSERT INTO <table>
SELECT * FROM <table>
WHERE <condition> FOR SYSTEM_TIME <temporal_clause>;
Ejemplo:
INSERT INTO PRODUCT
SELECT * FROM PRODUCT
WHERE PRODUCTID=’1004’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 12:45:07’;
En este ejemplo, un producto se ha suprimido incorrectamente y es necesario restaurarlo.
Sintaxis de consulta de viaje en el tiempo e indicaciones de fecha y hora.