17.10 - FROM Clause Examples (ANSI Valid-Time Table Form) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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;

 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.