Temporal Queries & Modifications | ANSI Temporal Table Support | Vantage - 17.10 - Temporal Queries and Modifications - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)
Vantage supports temporal SQL that allows you to qualify queries based on the durations associated with the rows:
  • AS OF queries consider only those rows that are in effect as of a given point in time.
  • BETWEEN time1 AND time2 queries consider only rows with durations that overlap or immediately succeed a given time period.
  • FROM time1 TO time2 queries are similar to BETWEEN ... AND queries, but they do not include rows where the duration succeeds but does not overlap the given time period.
  • CONTAINED IN(time1, time2) queries qualify rows with durations that lie within the specified time period.

For example:

SELECT Customer_ID, Policy_type 
FROM Policy 
FOR VALIDTIME AS OF DATE’2009-11-24’;

Customer_ID  Policy_Type
-----------  -----------
  246824626  AU

Temporal DELETE and UPDATE modifications can include a FOR PORTION OF qualifier that specifies when during the duration of a row a change or deletion is in effect, so such changes need not apply to the whole row in an all-or-nothing fashion.

Teradata provides rich support for Period data types, including operators, functions, and predicates, which could be used to return similar results. However, Period data types, period functions (BEGIN, END, LAST and INTERVAL), and the period predicate operator MEETS, are not ANSI standard SQL.

Related Information

For more information on... See...
Period data types and derived period columns
  • Teradata Vantage™ - Data Types and Literals, B035-1143
  • Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145