17.10 - Example: AS OF Query on 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 AS OF query retrieves all table rows that were open and active at the point in time specified in the query, regardless of whether these rows are open or closed now.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME AS OF TIMESTAMP'2005-01-01 00:00:01.000000-08:00';

 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
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
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00

Apart from the sys_end values, the data in the rows reflect the state of the table as it existed on 2005-01-01. At that time Fred and Alice belonged to deptno 222, and SRK was still employed (the SRK row had not yet been deleted).

Because the rows for Fred, Alice, and SRK have sys_end values less than 9999-12-31 23:59:59.999999+00:00, you know that this information from 2005-01-01 is no longer current. One or more changes to these rows occurred after the AS OF date, and the time of the first of those changes is reflected by the sys_end value. Rows with sys_end dates of 9999-12-31 23:59:59.999999+00:00 have not been changed or deleted since the AS OF date.

A query of the table AS OF 2005-05-02, would reflect a table query that occurred immediately after Fred and Alice had changed departments:

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME AS OF TIMESTAMP'2005-01-01 00:00:01.000000-08:00';

 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
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
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

Because AS OF queries reflect the state of the table at a given moment in time, they cannot indicate the nature of the changes that occurred to the closed rows that are returned. To determine the nature of changes to rows in a system-time table, you need to see the row as it existed both before and after the change. The remaining temporal query qualifiers, BETWEEN ... AND, FROM TO, and CONTAINED IN, allow you to specify spans of valid time, rather than instances. If, during the specified span, a row has been changed, both the old and new versions of the row are returned, which allows you to determine the nature of the change that caused the row to be closed in system time.