Bitemporal tables support these qualifiers only for system time, as described in “Querying ANSI System-Time Tables” on page 37.
To qualify rows in bitemporal tables by their valid-time periods, use nontemporal SQL in the WHERE clause condition. The SQL can refer to the individual columns that are the beginning and ending bounds of the valid-time derived period column, or you can apply Teradata Database Period data type functions directly to the valid-time derived period column. For more information on Period data type functions, see SQL Functions, Operators, Expressions, and Predicates.
Examples
These example use the following ANSI bitemporal table:
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 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
Example : System-Time AS OF Query on an ANSI Bitemporal Table
The following AS OF query retrieves all table rows that were open at a given point in time.
SELECT *
FROM employee_bitemp
FOR SYSTEM_TIME AS OF TIMESTAMP'2003-12-02 00:00:01.000000-08:00';
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
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
Example : System-Time AS OF Query and Valid-Time Current Query on an ANSI Bitemporal Table
The following query further qualifies the query from the last example by adding a valid-time condition to show only rows with information that is currently in effect (job_end is greater than or equal to the current date).
SELECT *
FROM employee_bitemp
FOR SYSTEM_TIME AS OF TIMESTAMP'2003-12-02 00:00:01.000000-08:00'
WHERE job_end >= CURRENT_DATE;
eid ename deptno terms job_start job_end sys_start sys_end
---- ----- ------ ----- ---------- ---------- -------------------------------- --------------------------------
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
Alternatively, this query could have been used to return the same result:
SELECT *
FROM employee_bitemp
FOR SYSTEM_TIME AS OF TIMESTAMP'2003-12-02 00:00:01.000000-08:00'
WHERE END(job_dur) >= CURRENT_DATE;
Example : System-Time BETWEEN Query and Valid-Time Non-Temporal Query on an ANSI Bitemporal Table
The following query selects rows that have been deleted from the table (sys_end is something less than the maximum system timestamp value), but that were valid at the beginning of 2006 (job_end is greater than 2006-01-01).
SELECT * FROM employee_bitemp
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 00:00:00.000000+00:00' AND
CURRENT_TIMESTAMP
WHERE SYS_END < TIMESTAMP'9999-12-31 23:59:59.999999+00:00' AND
job_end > DATE'2006-01-01';
eid ename deptno terms job_start job_end sys_start sys_end
---- ----- ------ ----- ---------- ---------- -------------------------------- --------------------------------
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