17.10 - Example: ALTER TABLE to Convert a Nontemporal Table to an ANSI System-Time Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

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;

 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;

 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.