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.