Temporal Statements | Temporal Table Support | Teradata Vantage - 17.05 - Temporal Statements - 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

Using the period data type and the temporal column attributes in a CREATE TABLE statement, the application for the insurance company can create the Policy table as a temporal table with a valid-time column to record when rows are valid.

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

The application can easily add a row to the table.

INSERT INTO Policy
   (Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
   VALUES (541008, 246824626, 'AU', 'STD-CH-345-NXY-00',
           PERIOD(DATE '2009-10-01', UNTIL_CHANGED));

Similarly, the application can easily query the table. The result of the following query is the rows that are valid at the current time (where the value of the Validity column overlaps with the current time):

CURRENT VALIDTIME SELECT * FROM Policy;