Sintaxe de consulta de viagem no tempo e timestamps

Sintaxe da Consulta

Uma consulta SELECT com uma ou mais cláusulas temporais é uma consulta de viagem no tempo. Consultas de viagem no tempo podem aparecer como sub-SELECTs nas instruções INSERT, UPDATE, DELETE, MERGE ou CREATE TABLE AS SELECT (CTAS).

Também, consultas de viagens no tempo podem aparecer em uma definição de visualização (CREATE VIEW, com ou sem OR REPLACE) ou uma definição de procedimento armazenado (CREATE PROCEDURE, com ou sem OR REPLACE). Em qualquer caso, as expressões timestamp na sintaxe (por exemplo, CURRENT_TIMESTAMP - INTERVAL ‘1 day’) não são avaliadas à vista ou tempo de definição de procedimento, mas no momento em que um usuário ou aplicativo consulta a visualização ou chama o procedimento.

Qualquer referência de tabela base (o nome da tabela, com ou sem banco de dados e nome do esquema, e com ou sem um alias) em um SELECT ou sub-SELECT pode ter uma cláusula temporal opcional, que consiste nas palavras-chave FOR SYSTEM_TIME seguidas de um dos seguintes valores:

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 deve ser um dos seguintes:

Um valor de timestamp literal. Por exemplo, ‘2022-10-31 20:00:00’.
Um parâmetro de consulta ou variável host cujo valor é um timestamp.
Uma função embutida que retorna ou converte implicitamente em um timestamp. Por exemplo, CURRENT_DATE, CURRENT_TIMESTAMP ou (equivalentemente) NOW(), ou CURRENT_TIMESTAMP(subsecond-digits) ou (equivalentemente) NOW(subsecond-digits).
Uma expressão que avalia para um único timestamp para todas as linhas na tabela. Por exemplo, CURRENT_TIMESTAMP - INTERVAL ‘1 day’. A expressão não pode referir-se a colunas de tabelas ou a uma função não determinística (por exemplo, RANDOM()) ou ser um sub-SELECT.
O identificador especial RETENTION_START_TIMESTAMP, nos casos particulares de AS OF, BETWEEN e FROM (mas não BEFORE, AND, ou TO). Isso se refere ao timestamp de início de retenção, que é o timestamp de inserção de linha mais antiga possível ou excluir timestamp que está disponível para consultas de viagem no tempo. Para obter mais informações sobre os timestamps de início de retenção, inserir timestamps e excluir timestamps, consulte Timestamps em consultas de viagem no tempo.

A partir de

Você pode usar a subcláusula AS OF quando quiser recuperar o estado de seus dados como ele estava em qualquer momento específico no passado.

Sintaxe	Descrição
AS DE EXPRESSÃO TIMESTAMP 1>	Inclui todas as linhas que foram válidas no timestamp que o TIMESTAMP EXPRESSION 1 avalia para, cujo timestamp de inserção é menor ou igual a TIMESTAMP EXPRESSION 1, e cujo timestamp de exclusão é NULL ou é maior que TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 for menor do que o timestamp de início de retenção da tabela, um erro será retornado.

ANTES

Você pode usar a subcláusula BEFORE quando quiser recuperar o estado de seus dados como ele era pouco antes de qualquer tempo específico no passado.

Sintaxe	Descrição
Antes<TIMESTAMP EXPRESSION 1>	Inclui todas as linhas que eram válidas pouco antes do timestamp que a TIMESTAMP EXPRESSION 1 avalia. Cujo timestamp de inserção é estritamente menor que TIMESTAMP EXPRESSION 1 e cujo timestamp de exclusão é NULL ou é maior que TIMESTAMP EXPRESSION 1. Se EXPRESSÃO TIMESTAMP 1 for menor ou igual ao timestamp de início de retenção da tabela, um erro será retornado.

DE ...TO e BETWEEN ...E

Você pode usar o FROM ...TO e BETWEEN ...AND subcláusulas para auditoria de dados ou análise de tendências. Use-o, quando for necessário obter toda a transformação histórica, para algumas ou todas as linhas, durante um período de tempo.

Sintaxe	Descrição
FROM < TIMESTAMP EXPRESSION 1> PARA < TIMESTAMP EXPRESSION 2>	Inclui todas as linhas que foram válidas a qualquer momento da TIMESTAMP EXPRESSION 1 para TIMESTAMP EXPRESSION 2 (exclusivo), cujo timestamp de inserção é estritamente inferior a TIMESTAMP EXPRESSION 2 e cujo timestamp de exclusão é NULL ou é maior que TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 for menor ou igual ao timestamp de início de retenção da tabela, um erro será retornado. Se TIMESTAMP EXPRESSION 1 for maior ou igual a TIMESTAMP EXPRESSION 2, a consulta não produz linhas.
Entre o <TIMESTAMP EXPRESSION 1> e o <TIMESTAMP EXPRESSION 2>	Inclui todas as linhas que foram válidas a qualquer momento entre a TIMESTAMP EXPRESSION 1 e TIMESTAMP EXPRESSION 2 (inclusive), cujo timestamp de inserção é menor ou igual a TIMESTAMP EXPRESSION 2 e cujo timestamp de exclusão é NULL ou é maior que TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 for menor do que o timestamp de início de retenção da tabela, um erro será retornado. Se TIMESTAMP EXPRESSION 1 for maior que TIMESTAMP EXPRESSION 2, a consulta não produz linhas.

Sintaxe

Descrição

FROM < TIMESTAMP EXPRESSION 1> PARA < TIMESTAMP EXPRESSION 2>

Inclui todas as linhas que foram válidas a qualquer momento da TIMESTAMP EXPRESSION 1 para TIMESTAMP EXPRESSION 2 (exclusivo), cujo timestamp de inserção é estritamente inferior a TIMESTAMP EXPRESSION 2 e cujo timestamp de exclusão é NULL ou é maior que TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 for menor ou igual ao timestamp de início de retenção da tabela, um erro será retornado. Se TIMESTAMP EXPRESSION 1 for maior ou igual a TIMESTAMP EXPRESSION 2, a consulta não produz linhas.

Entre o <TIMESTAMP EXPRESSION 1> e o <TIMESTAMP EXPRESSION 2>

Inclui todas as linhas que foram válidas a qualquer momento entre a TIMESTAMP EXPRESSION 1 e TIMESTAMP EXPRESSION 2 (inclusive), cujo timestamp de inserção é menor ou igual a TIMESTAMP EXPRESSION 2 e cujo timestamp de exclusão é NULL ou é maior que TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 for menor do que o timestamp de início de retenção da tabela, um erro será retornado. Se TIMESTAMP EXPRESSION 1 for maior que TIMESTAMP EXPRESSION 2, a consulta não produz linhas.

Timestamps em consultas de viagem no tempo

Intervalo de tempo de retenção e tempo de retenção

O intervalo de tempo de retenção de uma tabela define o número de dias passados seus timestamps de exclusão que as linhas históricas (excluídas) estão disponíveis para consultas de viagem no tempo. Em um determinado momento, o período de tempo de retenção termina no timestamp atual (data e hora) e estende de volta o número determinado de dias. Trata-se de uma janela de tempo deslizante que avança à medida que o tempo atual do sistema avança.

Retenção inferior amarrada

Para a maior parte, uma retenção de tabela inferior ligada é a data e o horário em que a tabela foi definida para ser uma tabela temporal. Isso poderia ter sido quando você executou o comando CREATE TABLE, ou a última vez que alterou a tabela's DATA_VERSION_RETENTION_TIME de zero a zero.

Timestamp de início de retenção

No momento de definir uma tabela para ser temporal (quando o limite inferior de retenção é definido), não há linhas históricas disponíveis sobre o período de tempo de retenção. Para capturar a noção de como linhas históricas de volta estão realmente disponíveis (visíveis para consultas de viagem no tempo), é definido um timestamp de início de retenção de uma tabela. O timestamp de início de retenção é o maior dos valores a seguir:

O início do período de tempo de retenção (data / hora atual menos o intervalo de retenção).
A retenção inferior amarrada.

O timestamp de início de retenção de uma tabela entra em jogo nas seguintes operações:

Consultas de viagem no tempo (SELECT e sub-SELECT)

Se você tentar executar consultas para linhas históricas que foram excluídas antes do timestamp de início de retenção, um erro é retornado.

Se você deseja consultar dados históricos o mais atrás possível, você pode usar a palavra-chave RETENTION_START_TIMESTAMP em 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ê elimora o risco de se escorrer em um erro se o valor se tornar muito antigo (mais antigo do que o timestamp de início de retenção).
GROOM TABLE As linhas históricas que foram deletadas antes do timestamp de início de retenção não são mais necessárias para consultas de viagens de tempo e podem ser rereclamadas.

Linha timestamps e validade

O timestamp de inserção de uma linha atual ou histórica é a data / hora em que a transação insere a linha cometida. Não é o momento em que uma determinada instrução INSERT, UPDATE ou MERGE que inseriu a linha foi executada.

Se a transação de inserção para uma linha confirmada antes do timestamp de início de retenção, a linha é tratada como tendo sido inserida no timestamp de início de retenção. Isso geralmente se aplica apenas às linhas existentes no momento de alterar uma tabela não temporal para uma tabela temporal.

Uma linha inserida cuja transação ainda não foi confirmada não tem um timestamp de inserção. Tal linha nunca será visível para uma consulta de viagem no tempo.

Em uma consulta de viagem no tempo, você pode selecionar o timestamp de inserção usando a coluna virtual _SYS_START de uma tabela temporal.

O timestamp de exclusão de uma linha histórica é a data / hora em que a transação excluindo a linha cometida. Não é o momento em que uma determinada instrução DELETE, UPDATE, MERGE ou TRUNCATE que excluiu a linha foi executada.

Se uma tabela temporal for truncada, as linhas de tabelas existentes estão disponíveis para consultas de viagem no tempo e são tratadas como tendo sido excluídas a partir do momento em que a transação de truncamento tenha sido confirmada.

Se a transação de exclusão (ou truncamento) cometida antes do timestamp de início de retenção, uma linha excluída é tratada como tendo sido excluída no timestamp de início de retenção. Isso geralmente se aplica apenas às linhas deletadas existentes no momento de alterar uma tabela não temporal para uma tabela temporal; tais linhas não são visíveis a consultas de viagem no tempo contra a tabela.

Uma linha histórica pode ser visível para uma consulta temporal contra a tabela se o seu timestamp de exclusão cair dentro do período de retenção da tabela. Se esta condição for verdadeira, a linha histórica não pode ser removida (com GROOM TABLE) da tabela.

O timestamp de exclusão de uma linha atual (não excluído, ou marcado para exclusão mas não cometida) é NULL.

Em uma consulta de viagem no tempo, você pode selecionar o timestamp de exclusão usando a coluna virtual _SYS_END de uma tabela temporal.

Uma linha histórica é considerada válida a partir de seu timestamp de inserção até pouco antes do timestamp de exclusão. Uma linha atual é considerada válida a partir de seu timestamp de inserção para frente. As consultas de viagem no tempo usam timestamps ou expressões timestamp para apenas retornar linhas (atuais ou históricas) que são válidas em um ponto no tempo ou em qualquer ponto dentro de um período de tempo.

Os timestamps de inserção e exclusão para linhas recentemente inseridas e excluídas podem não estar disponíveis para consultas de viagens de tempo até um curto tempo (geralmente sob 3 minutes) após a inserção e exclusão de transações.