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.