Temporal Columns | ANSI Temporal Table Support | Teradata Vantage - Temporal Columns - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

Teradata provides temporal table support at the column level using derived period columns. A period is an anchored duration that represents a set of contiguous time granules within the duration. It has a beginning and ending bound, defined by two DateTime type columns in the table.

In a temporal table, the values from the beginning and ending bound columns are combined in a third, derived column that represents the period of time, or duration they delimit.

Now the application for the insurance company can create the Policy table with a derived period column to record the period during which each policy (row) is valid.

CREATE TABLE Policy(
 Policy_ID INTEGER,
 Customer_ID INTEGER,
 Policy_Type CHAR(2) NOT NULL,
 Policy_Details CHAR(40),
 Policy_Begin DATE NOT NULL,
 Policy_End DATE NOT NULL,
 PERIOD FOR Policy_Duration(Policy_Begin,Policy_End) AS VALIDTIME
  )
 PRIMARY INDEX(Policy_ID);

The derived period column is used internally. The value for each row is created and maintained automatically by Vantage based on the table definition. Data is never inserted explicitly for the derived period column, and that column is never itself returned or queried.

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

SELECT * FROM Policy;

Output:

 Policy_ID  Customer_ID  Policy_Type  Policy_Details       Policy_Begin  Policy_End
-----------  -----------  -----------  ------------------- ------------  ----------
     541008    246824626  AU           STD-CH-345-NXY-00     2009/10/01  2010/10/01