17.05 - Overview - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

This section provides examples of various operations that involve temporal tables.

Some of the examples use the following tables:

   CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Policy_Term PERIOD(DATE) NOT NULL AS VALIDTIME
      )
   PRIMARY INDEX(Policy_ID);

   CREATE MULTISET TABLE Policy_Types (
      Policy_Name VARCHAR(20),
      Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX (Policy_Name);

   CREATE MULTISET TABLE Policy_History(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX(Policy_ID);

Policy is a valid-time table, Policy_Types is a transaction-time table, and Policy_History is a bitemporal table. These tables are simplified forms of tables that might be used by an insurance application.