Example: ALTER TABLE to Convert a Nontemporal Table to a Valid-Time Table - Advanced SQL Engine - Teradata Database

ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kit1592007446534.ditamap
dita:ditavalPath
kit1592007446534.ditaval
dita:id
B035-1186
lifecycle
previous
Product Category
Teradata Vantage™

The following SQL creates a regular nontemporal table, and inserts some rows:

CREATE MULTISET TABLE employee_vt (
 eid INTEGER NOT NULL,
 ename VARCHAR(5) NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE NOT NULL
 ) PRIMARY INDEX(eid);

After rows have been inserted, an unqualified SELECT on the table returns all rows:

SELECT * FROM employee_systime;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1002 Ash    TA05 2003/01/01  2003/12/31
1005 Alice  TW10 2004/12/01  2005/12/01
1010 Mike   TW07 2015/01/01  2016/12/31
1005 Alice  PW11 2005/12/01  9999/12/31
1001 Sania  TW08 2002/01/01  2006/12/31
1004 Fred   PW12 2001/05/01  9999/12/31
1003 SRK    TM02 2004/02/10  2005/02/10


The following ALTER TABLE statement changes the table into a valid-time temporal table:

ALTER TABLE employee_vt
 ADD PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME;

Unlike for system-time tables, an unqualified SELECT of a valid-time table shows all rows in the table, regardless of whether their valid-time period is passed, current, or in the future:

SELECT * FROM employee_systime;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1002 Ash    TA05 2003/01/01  2003/12/31
1005 Alice  TW10 2004/12/01  2005/12/01
1010 Mike   TW07 2015/01/01  2016/12/31
1005 Alice  PW11 2005/12/01  9999/12/31
1001 Sania  TW08 2002/01/01  2006/12/31
1004 Fred   PW12 2001/05/01  9999/12/31
1003 SRK    TM02 2004/02/10  2005/02/09


Automatic temporal behavior is evident in valid-time tables when using temporal qualifiers for SELECT queries, and for UPDATE, and DELETE modifications. For more information, see Querying ANSI Valid-Time Tables and Modifying Rows in ANSI Valid-Time Tables.