Temporal Queries and Modifications - Teradata Database

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...ANDqueries, 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.

Note: Note that 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.

For more information on...	See...
Period data types and derived period columns	SQL Data Types and Literals SQL Functions, Operators, Expressions, and Predicates