For the following examples, assume the queries are issued against the following system-versioned system-time table named employee_systime that contains a mix of open and closed rows:
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 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
Rows with sys_end dates that are not 9999-12-31 23:59:59.999999+00:00 are closed in system time. They have either been logically deleted from the table or modified since they were first added to the table, but they remain in the table as a permanent record of prior states of the table. They can not participate in most SQL operations, such as UPDATEs and DELETEs, but using temporal qualifiers they can be retrieved from system-time tables.
In this case, the table shows that the department values (column deptno) for Fred and Alice were changed on 2005-05-01, which is the end date of the closed Fred and Alice rows and the start date of the new rows for them that contain the new information.
SRK also shows a sys_end date prior to 9999-12-31 23:59:59.999999+00:00, but because there is no open row remaining in the table for SRK, the sys_end date indicates when the row was (logically) deleted from the table.
A simple SELECT with no temporal qualifiers shows only the open, active rows in a system-time table. Open rows are indicated by system-time periods with ending bounds of 9999-12-31 23:59:59.999999+00:00.
SELECT * FROM employee_systime;
Output:
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 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
This reflects the state of the information today, at the moment the query is processed. Temporal qualifiers allow you to see a snapshot of the table as it existed at any prior time, and return results that were true for former states of the table.