Example: ALTER TABLE to Convert a Nontemporal Table to an ANSI System-Time Table - 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
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantage™

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

CREATE MULTISET TABLE employee_systime (
 eid INTEGER NOT NULL,
 ename VARCHAR(10) NOT NULL,
 deptno INTEGER NOT NULL,
 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL,
 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
 ) PRIMARY INDEX(eid);

INSERT INTO employee_systime VALUES 
 (1001,'Sania',111,TIMESTAMP'2002-01-01 00:00:00.000000-08:00',
                   TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES 
 (1002,'Ash',333,TIMESTAMP'2003-07-01 12:11:00.000000-08:00',
                 TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES 
 (1003,'SRK',111,TIMESTAMP'2004-02-10 00:00:00.000000-08:00',
                 TIMESTAMP'2006-03-01 00:00:00.000000-08:00');
INSERT INTO employee_systime VALUES 
 (1004,'Fred',222, TIMESTAMP'2002-07-01 12:00:00.350000-08:00',
                   TIMESTAMP'2005-05-01 12:00:00.350000-08:00');
INSERT INTO employee_systime VALUES 
 (1005,'Alice',222,TIMESTAMP'2004-12-01 00:12:23.120000-08:00',
                   TIMESTAMP'2005-05-01 12:00:00.450000-08:00');
INSERT INTO employee_systime VALUES 
 (1004,'Fred',555, TIMESTAMP'2005-05-01 12:00:00.350000-08:00',
                   TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES 
 (1005,'Alice',555,TIMESTAMP'2005-05-01 12:00:00.450000-08:00',
                   TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

An unqualified SELECT on the table returns all rows, regardless of whether the row is open or closed in system time, because this is not yet a temporal table:

SELECT * FROM employee_systime;

Output:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002  Ash      333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005  Alice    222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1004  Fred     222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1005  Alice    555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004  Fred     555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1001  Sania    111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1003  SRK      111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00

Two ALTER TABLE statements can change the table into a system-time temporal table:

ALTER TABLE employee_systime
 ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
 ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
               GENERATED ALWAYS AS ROW START
 ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
               GENERATED ALWAYS AS ROW END;
ALTER TABLE employee_systime
 ADD SYSTEM VERSIONING;

Now an unqualified SELECT will show only the rows that are open in system time:

SELECT * FROM employee_systime;

Output:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       333 2003-07-01 12:11:00.000000+00:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000+00:00 9999-12-31 23:59:59.999999+00:00
1001 Fred      222 2002-07-01 12:00:00.350000+00:00 9999-12-31 23:59:59.999999+00:00
1003 Alice     222 2004-12-01 00:12:23.120000+00:00 9999-12-31 23:59:59.999999+00:00

Special temporal qualifiers allow you to display closed rows from system-time tables. For more information see Querying ANSI System-Time Tables.