Example: ALTER TABLE to Convert a Nontemporal Table to an ANSI Bitemporal 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_bitemp (
 eid INTEGER NOT NULL,
 ename VARCHAR(5),
 deptno INTEGER NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE 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_bitemp VALUES 

 (1001,'Sania',111,'TW08',DATE'2002-01-01',DATE'2006-12-31',
  TIMESTAMP'2002-01-01 00:00:00.000000-08:00',
  TIMESTAMP'2002-07-01 12:00:00.350000+00:00');
INSERT INTO employee_bitemp VALUES 
 (1004,'Fred',222,'PW12', DATE'2001-05-01',DATE'9999-12-31',
  TIMESTAMP'2001-05-01 12:00:00.350000-08:00',
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_bitemp VALUES
 (1002,'Ash',333,'TA05',DATE'2003-01-01',DATE'2003-12-31',
  TIMESTAMP'2003-01-01 12:11:00.000000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_bitemp VALUES 
 (1003,'SRK',111,'TM02',DATE'2004-02-10',DATE'2005-02-10', 
  TIMESTAMP'2004-02-10 00:00:00.000000-08:00', 
  TIMESTAMP'2004-12-01 00:12:23.120000+00:00');
INSERT INTO employee_bitemp VALUES 
 (1005,'Alice',222,'TW10',DATE'2004-12-01',DATE'9999-12-31',
  TIMESTAMP'2004-12-01 12:00:00.450000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_bitemp VALUES 
 (1010,'Mike',444,'TW07',DATE'2015-01-01',DATE'2016-12-31',
  TIMESTAMP'2004-12-01 00:12:23.120000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

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

SELECT * FROM employee_bitemp;

Output:

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

The following ALTER TABLE statement adds valid time to the table:

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

A simple select from the table still returns all rows, as it would from any valid-time table:

SELECT * FROM employee_bitemp;

Output:

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

The following ALTER TABLE statements add system time to the table:

ALTER TABLE employee_bitemp
 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_bitemp
 ADD SYSTEM VERSIONING;

Because the table now includes a system-time dimension, rows that had dates for sys_end that were not 9999-12-31 23:59:59.999999+0:0 are considered closed rows, logically deleted from the database, so now a simple select shows only four rows:

SELECT * FROM employee_bitemp;

Output:

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00