Temporal Data Types | Temporal Table Support | Teradata Vantage - 17.10 - Temporal Data Types - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Teradata provides temporal table support at the data type level with period data types. A period is an anchored duration that represents a set of contiguous time granules within the duration. It has a beginning bound (defined by the value of a beginning element) and an ending bound (defined by the value of an ending element). Beginning and ending elements can be DATE, TIME, or TIMESTAMP types, but both must be the same type.

The duration that a period represents starts from the beginning bound and extends up to, but does not include, the ending bound.

If the element type is DATE or TIMESTAMP, the ending bound can have a special value of UNTIL_CHANGED, where Vantage interprets the ending bound of the period as forever, or without end.

As a first step toward adding temporal table support to the Policy table, the application for the insurance company can create the Policy table with a PERIOD(DATE) column to record when rows are valid.

   Policy_ID INTEGER,
   Customer_ID INTEGER,
   Policy_Type CHAR(2) NOT NULL,
   Policy_Details CHAR(40),
   Validity PERIOD(DATE)

Although the Policy table is a nontemporal table, the application can use the built-in support that Vantage provides for period types, including period constructors, literals, operators, functions, and predicates.

For example, to add a row to the table, the application could use the period constructor to specify a value for the Validity column.

   (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));

To retrieve rows from the Policy table that became valid on a specific date, the application could use the BEGIN function like this:

SELECT * FROM Policy WHERE BEGIN(Validity) = DATE '2010-01-01';