Sintaxis de consulta de viaje en el tiempo e indicaciones de fecha y hora

Sintaxis de consulta

Una consulta SELECT con una o más cláusulas temporales es una consulta de viaje en el tiempo. Las consultas de viaje en el tiempo pueden aparecer como sub-SELECTs en las sentencias INSERT, UPDATE, DELETE, MERGE o CREATE TABLE AS SELECT (CTAS).

Además, las consultas de viaje en el tiempo pueden aparecer en una definición de vista (CREATE VIEW, con o sin OR REPLACE) o una definición de procedimiento almacenado (CREATE PROCEDURE, con o sin OR REPLACE). En cualquier caso, las expresiones de indicación de fecha y hora en la sintaxis (por ejemplo, CURRENT_TIMESTAMP - INTERVAL ‘1 day’) no se evalúan en el momento de la definición de vista o procedimiento, sino en el momento en que un usuario o aplicación consulta la vista o llama al procedimiento.

Cualquier referencia de tabla base (el nombre de tabla, con o sin nombre de base de datos y esquema, y con o sin alias) en SELECT o sub-SELECT puede tener una cláusula temporal opcional, que consta de las palabras clave FOR SYSTEM_TIME seguidas de uno de los valores siguientes:

AS OF <TIMESTAMP EXPRESSION>
BEFORE <TIMESTAMP EXPRESSION>
BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>
FROM <TIMESTAMP EXPRESSION 1> TO <TIMESTAMP EXPRESSION 2>

Cada TIMESTAMP EXPRESSION debe ser uno de los siguientes:

Un valor de indicación de fecha y hora literal. Por ejemplo, ‘2022-10-31 20:00:00’.
Parámetro de consulta o variable del lenguaje principal cuyo valor es una indicación de fecha y hora.
Función incorporada que devuelve o convierte implícitamente en una indicación de fecha y hora. Por ejemplo, CURRENT_DATE, CURRENT_TIMESTAMP o (equivalente) NOW(), o CURRENT_TIMESTAMP(subsecond-digits) o (equivalente) NOW(subsecond-digits).
Expresión que se evalúa en una única indicación de fecha y hora para todas las filas de la tabla. Por ejemplo, CURRENT_TIMESTAMP - INTERVAL ‘1 day’. La expresión no puede hacer referencia a columnas de tabla o a una función no determinista (por ejemplo, RANDOM()) o ser una subSELECT.
El identificador especial RETENTION_START_TIMESTAMP, en los casos particulares de AS OF, BETWEEN y FROM (pero no BEFORE, AND o TO). Esto hace referencia a la indicación de fecha y hora de inicio de retención, que es la indicación de fecha y hora de inserción de fila posible más antigua o la indicación de fecha y hora de supresión que está disponible para las consultas de viaje en el tiempo. Para obtener más información sobre las indicaciones de fecha y hora de inicio de retención, insertar indicaciones de fecha y hora y suprimir indicaciones de fecha y hora, consulte Indicaciones de fecha y hora en consultas de viaje en el tiempo.

A partir de

Puede utilizar la subcláusula AS OF cuando desee recuperar el estado de los datos tal como estaban en cualquier momento específico del pasado.

Sintaxis	Descripción
AS OF < EXPRESIÓN DE INDICACIÓN DE FECHA Y HORA 1 >	Incluye todas las filas que eran válidas en la indicación de fecha y hora en la que se evalúa TIMESTAMP EXPRESSION 1, cuya indicación de fecha y hora de inserción es menor o igual que TIMESTAMP EXPRESSION 1, y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 es menor que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error.

Sintaxis

Descripción

AS OF < EXPRESIÓN DE INDICACIÓN DE FECHA Y HORA 1 >

Incluye todas las filas que eran válidas en la indicación de fecha y hora en la que se evalúa TIMESTAMP EXPRESSION 1, cuya indicación de fecha y hora de inserción es menor o igual que TIMESTAMP EXPRESSION 1, y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 es menor que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error.

BEFORE

Puede utilizar la subcláusula BEFORE cuando desee recuperar el estado de los datos tal como estaban justo antes de cualquier hora específica del pasado.

Sintaxis	Descripción
antes<TIMESTAMP EXPRESSION 1>	Incluye todas las filas que eran válidas justo antes de la indicación de fecha y hora en la que se evalúa TIMESTAMP EXPRESSION 1. Cuya indicación de fecha y hora de inserción es estrictamente menor que TIMESTAMP EXPRESSION 1 y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 es menor o igual que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error.

Sintaxis

Descripción

antes<TIMESTAMP EXPRESSION 1>

Incluye todas las filas que eran válidas justo antes de la indicación de fecha y hora en la que se evalúa TIMESTAMP EXPRESSION 1. Cuya indicación de fecha y hora de inserción es estrictamente menor que TIMESTAMP EXPRESSION 1 y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 es menor o igual que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error.

DESDE ...TO y BETWEEN ...Y

Puede utilizar FROM ...TO y BETWEEN ...AND subcláusulas para la auditoría de datos o el análisis de tendencias. Utilícelo, cuando necesite obtener toda la transformación histórica, para algunas o todas las filas, durante un periodo de tiempo.

Sintaxis	Descripción
FROM < EXPRESIÓN DE INDICACIÓN DE FECHA Y HORA 1 > TO < EXPRESIÓN DE INDICACIÓN DE FECHA Y HORA 2 >	Incluye todas las filas que eran válidas en cualquier momento desde TIMESTAMP EXPRESSION 1 a TIMESTAMP EXPRESSION 2 (exclusivo), cuya indicación de fecha y hora de inserción es estrictamente menor que TIMESTAMP EXPRESSION 2 y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 es menor o igual que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error. Si TIMESTAMP EXPRESSION 1 es mayor o igual que TIMESTAMP EXPRESSION 2, la consulta no produce filas.
Entre <TIMESTAMP EXPRESSION 1> y <TIMESTAMP EXPRESSION 2>	Incluye todas las filas que eran válidas en cualquier momento entre TIMESTAMP EXPRESSION 1 y TIMESTAMP EXPRESSION 2 (inclusive), cuya indicación de fecha y hora de inserción es menor o igual que TIMESTAMP EXPRESSION 2 y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 es menor que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error. Si TIMESTAMP EXPRESSION 1 es mayor que TIMESTAMP EXPRESSION 2, la consulta no produce filas.

Sintaxis

Descripción

FROM < EXPRESIÓN DE INDICACIÓN DE FECHA Y HORA 1 > TO < EXPRESIÓN DE INDICACIÓN DE FECHA Y HORA 2 >

Incluye todas las filas que eran válidas en cualquier momento desde TIMESTAMP EXPRESSION 1 a TIMESTAMP EXPRESSION 2 (exclusivo), cuya indicación de fecha y hora de inserción es estrictamente menor que TIMESTAMP EXPRESSION 2 y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 es menor o igual que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error. Si TIMESTAMP EXPRESSION 1 es mayor o igual que TIMESTAMP EXPRESSION 2, la consulta no produce filas.

Entre <TIMESTAMP EXPRESSION 1> y <TIMESTAMP EXPRESSION 2>

Incluye todas las filas que eran válidas en cualquier momento entre TIMESTAMP EXPRESSION 1 y TIMESTAMP EXPRESSION 2 (inclusive), cuya indicación de fecha y hora de inserción es menor o igual que TIMESTAMP EXPRESSION 2 y cuya indicación de fecha y hora de supresión es NULL o es mayor que TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 es menor que la indicación de fecha y hora de inicio de retención de la tabla, se devuelve un error. Si TIMESTAMP EXPRESSION 1 es mayor que TIMESTAMP EXPRESSION 2, la consulta no produce filas.

Indicaciones de fecha y hora en las consultas de viaje en el tiempo

Intervalo de tiempo de retención y periodo de tiempo de retención

El intervalo de tiempo de retención de una tabla define el número de días después de las indicaciones de fecha y hora de supresión que las filas históricas (suprimidas) están disponibles para las consultas de viaje en el tiempo. En cualquier momento determinado, el periodo de tiempo de retención finaliza en la indicación de fecha y hora actual (fecha y hora) y se amplía el número de días especificado. Se trata de una ventana de tiempo deslizante que avanza a medida que avanza la hora actual del sistema.

Límite inferior de retención

En su mayor parte, el límite inferior de retención de una tabla es la fecha y la hora en que se definió que la tabla era una tabla temporal. Esto podría haber ocurrido cuando ejecutó el mandato CREATE TABLE, o la última vez que modificó el valor de DATA_VERSION_RETENTION_TIME de cero a distinto de cero.

Indicación de fecha y hora de inicio de retención

En el momento de definir una tabla para que sea temporal (cuando se define el límite inferior de retención), no hay filas históricas disponibles durante el periodo de tiempo de retención. Para capturar la noción de hasta qué punto están realmente disponibles las filas históricas (visibles para las consultas de viaje en el tiempo), se define la indicación de fecha y hora de inicio de retención de una tabla. La indicación de fecha y hora de inicio de retención es la mayor de los valores siguientes:

El inicio del periodo de tiempo de retención (la fecha/hora actual menos el intervalo de retención).
Límite inferior de retención.

La indicación de fecha y hora de inicio de retención de una tabla entra en juego en las siguientes operaciones:

Consultas de viaje en el tiempo (SELECT y sub-SELECT)

Si intenta ejecutar consultas para filas históricas que se han suprimido antes de la indicación de fecha y hora de inicio de retención, se devuelve un error.

Si desea consultar los datos históricos tan atrás como sea 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, elimina 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).
GROOM TABLE Las filas históricas que se han suprimido antes de la indicación de fecha y hora de inicio de retención ya no son necesarias para las consultas de viaje en el tiempo y se pueden reclamar.

Indicaciones de fecha y hora de fila y validez

La indicación de fecha y hora de inserción de una fila actual o histórica es la fecha/hora en que se ha confirmado la transacción que inserta la fila. No es la hora en la que se ha ejecutado una sentencia INSERT, UPDATE o MERGE determinada que ha insertado la fila.

Si la transacción de inserción para una fila confirmada antes de la indicación de fecha y hora de inicio de retención, la fila se trata como si se hubiera insertado en la indicación de fecha y hora de inicio de retención. Esto generalmente sólo se aplica a las filas existentes en el momento de modificar una tabla no temporal por una tabla temporal.

Una fila insertada cuya transacción todavía no se ha confirmado no tiene una indicación de fecha y hora de inserción. Una fila de este tipo nunca será visible para una consulta de viaje en el tiempo.

En una consulta de viaje en el tiempo, puede seleccionar la indicación de fecha y hora de inserción utilizando la columna virtual _SYS_START de una tabla temporal.

La indicación de fecha y hora de supresión de una fila histórica es la fecha/hora en que la transacción suprime la fila confirmada. No es la hora en que se ha ejecutado una sentencia DELETE, UPDATE, MERGE o TRUNCATE determinada que ha suprimido la fila.

Si se trunca una tabla temporal, las filas de tabla existentes están disponibles para las consultas de viaje en el tiempo y se tratan como suprimidas a partir del momento en que se confirma la transacción de truncamiento.

Si la transacción de supresión (o truncamiento) se confirma antes de la indicación de fecha y hora de inicio de retención, una fila suprimida se trata como suprimida en la indicación de fecha y hora de inicio de retención. Esto generalmente sólo se aplica a las filas suprimidas existentes en el momento de modificar una tabla no temporal en una tabla temporal; estas filas no son visibles para las consultas de viaje en el tiempo en la tabla.

Una fila histórica puede ser visible para una consulta temporal en la tabla si su indicación de fecha y hora de supresión se encuentra dentro del periodo de retención de la tabla. Si esta condición es verdadera, la fila histórica no se puede eliminar (con GROOM TABLE) de la tabla.

La indicación de fecha y hora de supresión de una fila actual (no suprimida o marcada para supresión pero no confirmada) es NULL.

En una consulta de viaje en el tiempo, puede seleccionar la indicación de fecha y hora de supresión utilizando la columna virtual _SYS_END de una tabla temporal.

Una fila histórica se considera válida desde su indicación de fecha y hora de inserción hasta justo antes de la indicación de fecha y hora de supresión. Una fila actual se considera válida a partir de su indicación de fecha y hora de inserción. Las consultas de viaje en el tiempo utilizan indicaciones de fecha y hora o expresiones de indicación de fecha y hora para devolver sólo las filas (actuales o históricas) que son válidas en un punto en el tiempo o en cualquier punto dentro de un periodo de tiempo.

Es posible que las indicaciones de fecha y hora de inserción y supresión para las filas insertadas y suprimidas recientemente no estén disponibles para las consultas de viaje en el tiempo hasta poco tiempo (generalmente menos de 3 minutos) después de la confirmación de la inserción y supresión de transacciones.