Example: Valid-Time DELETE from an ANSI Bitemporal Table - Analytics Database - Teradata Vantage

DELETE FROM employee_bitemp
FOR PORTION OF job_dur FROM DATE’2009-01-01’ TO DATE’2010-01-01’
WHERE ename=’Fred’;

Output:

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-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

Because the PA of the DELETE statement was within the PV of the affected row, two rows result, just as for a deletion on a 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-12-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 2009/01/01  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2010/01/01 9999/12/31  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00

In system time these rows are both considered to be new rows added to the table, which is reflected by their sys_start values that show the time that the middle portion of the Fred row valid time was deleted. To see all that occurred in system time to the table as a result of the “partial” deletion, use a temporal query that shows all rows, open and closed:

SELECT * FROM employee_bitemp
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 00:00:00.000000+00:00' AND CURRENT_TIMESTAMP;

Output:

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------
1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-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 2014-02-28 21:06:33.460000-08:00
1004 Fred     222  PW12  2001/05/01 2009/01/01  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2010/01/01 9999/12/31  2014-02-28 21:06:33.460000-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

Now you can see there are actually three physical Fred rows in the table. The two new rows that were added to account for Fred’s time before and after the deletion, and the original Fred row has been logically deleted from the table, as reflected by the sys_end time that shows the time of the change. The valid-time bounds in the deleted row are those of the original Fred row, before the deletion.