FROM Clause Examples (ANSI Valid-Time 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
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantage™

A simple SELECT on a valid-time table with no temporal qualifiers shows all rows in the table, regardless of the valid-time periods. Assume this valid-time table has a valid-time derived period column, job_dur, that represents the duration from job_start to job_end.

SELECT * FROM employee_systime;

Output:

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1002 Ash    TA05 2003/01/01  2003/12/31
1005 Alice  TW11 2004/12/01  2005/12/01
1010 Mike   TW07 2015/01/01  2016/12/31
1005 Alice  PW11 2005/12/01  9999/12/31
1001 Sania  TW08 2002/01/01  2006/12/31
1004 Fred   PW12 2001/05/01  9999/12/31
1003 SRK    TM02 2004/02/10  2005/02/09

Rows with job_end dates that are 9999-12-31 are valid indefinitely.

Notice two rows for Alice with non-overlapping dates, indicating that the terms for Alice’s job contract changed as of December, 1, 2005. Even though one row ends at 2005-12-01 and the other starts at 2005-12-01, the rows do not “overlap” because the true duration of the period derived from the job_start and job_end columns is considered to be inclusive of the beginning bound and exclusive of the ending bound.

Temporal qualifiers allow you to qualify rows that are, were, or will be valid at any time or during any period you specify.