Example: Restoring a Temporal Table to a Prior State - Advanced SQL Engine - Teradata Database

Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
cjo1556732840654.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1182
lifecycle
previous
Product Category
Teradata Vantage™

To return the table back to the state it was in at time x, use the following plan:

Row Row at Time X

Compared to Row Now

Plan
1 Row existed at time X and row exists now. Leave the row as it is.
2 It existed at time X and row exists now. Leave the row as it is.
3 It did not exist at time X. Delete the row.
4 It was closed at time X, and is still closed now. Leave the row as it is.
5 It was closed at time X, and is still closed now. Leave the row as it is.
6 It was open at time X, but is closed now. Leave BEGIN(TT) as it is.

Update END(TT) to UNTIL_CLOSED.

7 It was open at time X, but row is closed now. Leave BEGIN(TT) as it is.

Update END(TT) to UNTIL_CLOSED.

8 It did not yet exist at time X. Delete the row.

The following SQL will realize the plan for each of the different row states.

NONTEMPORAL DELETE  tt_table  where BEGIN(tt_col) >  time X;
NONTEMPORAL UPDATE  tt_table 
  SET  tt_col  = PERIOD(BEGIN(tt_col), UNTIL_CLOSED)
  WHERE END(tt_col) IS NOT UNTIL_CLOSED
  AND   BEGIN(tt_col) <=  time X 
  AND   END(tt_col) >  time X;
Because this solution requires NONTEMPORAL SQL, nontemporal operations on temporal tables must be enabled using the DBS Control utility. Additionally, the user executing this SQL must be granted the NONTEMPORAL privilege. For more information on DBS Control see Teradata Vantage™ - Database Utilities , B035-1102 . For more information on NONTEMPORAL operations, see NONTEMPORAL Privilege.