Temporal Queries & Modifications | ANSI Temporal Table Support | Vantage - 17.00 - Temporal Queries and Modifications - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K
Teradata Database 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 Database 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