FROM Clause Examples (ANSI Bitemporal Table Form) - Analytics Database - Teradata Vantage

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';

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
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;

Output:

 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';

Output:

 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