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

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
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