15.00 - Querying Temporal Tables - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Querying Temporal Tables

The following examples demonstrate the basic kinds of temporal queries.

Example : Current Query on a Valid-Time Table

To query a valid-time table for the rows that are valid at the current time (rows that overlap with current time), use the CURRENT VALIDTIME temporal qualifier in the SELECT statement. For example:

   CURRENT VALIDTIME 
   SELECT * 
   FROM Policy
   WHERE Policy_Type = 'AU';

The result is a nontemporal result set. (The result set does not include the valid-time column.)

   Policy_ID  Customer_ID  Policy_Type  Policy_Details
   ---------  -----------  -----------  -----------------
      541077    766492008  AU           STD-CH-344-YXY-00
      541145    616035020  AU           STD-CH-348-YXN-00
      541008    246824626  AU           STD-CH-345-NXY-00

Example : Current Query on a Transaction-Time Table

To query a transaction-time table for the current rows, use the CURRENT TRANSACTIONTIME temporal qualifier in the SELECT statement. For example:

   CURRENT TRANSACTIONTIME 
   SELECT * 
   FROM Policy_Types;

The result is a nontemporal result set. (The result set does not include the transaction-time column.)

   Policy_Name           Policy_Type
   --------------------  -----------
   Premium Automobile    AP
   Basic Homeowner       HM
   Basic Automobile      AU
   Premium Homeowner     HP

Example : Sequenced Query on a Valid-Time Table

To query a valid-time table for the rows that are valid at a specific time period, use the SEQUENCED VALIDTIME temporal qualifier in the SELECT statement. For example:

   SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
   SELECT Policy_ID, Customer_ID
   FROM Policy
   WHERE Policy_Type = 'AU';

The result set is a temporal table that includes rows that are valid for a period of applicability specified by PERIOD '(2009-01-01, 2009-12-31)':

   Policy_ID  Customer_ID  VALIDTIME
   ---------  -----------  ------------------------
      541077    766492008  ('09/12/21', '09/12/31')
      541145    616035020  ('09/12/03', '09/12/31')
      541008    246824626  ('09/10/01', '09/12/31')

Note: The valid-time column for the result set, VALIDTIME, which is automatically appended to the results of a sequenced valid-time query, is different from the valid-time column (Policy_Term) of the temporal table that was queried. The valid time for the results of the query is the intersection of the PA of the query and the original valid-time periods of the rows.

Example : Nonsequenced Query on a Valid-Time Table

To query a valid-time table such that no special temporal treatment is given to the valid-time column, use the NONSEQUENCED VALIDTIME temporal qualifier in the SELECT statement. For example:

NONSEQUENCED VALIDTIME SELECT * FROM Policy;
 Policy_ID  Customer_ID  Policy_Type  Policy_Details       Policy_Term
----------  -----------  -----------  -------------------  -------------------------
    541008    246824626  AP           STD-CH-345-NXY-00     ('09/10/01', '99/12/31')
    540944    123344567  HM           STD-PL-332-YXY-00     ('07/02/03', '99/12/31')
    497201    304779902  AU           STD-CH-524-WXY-00     ('05/02/14', '06/02/14')
    541145    616035020  B            STD-CH-348-YXN-00     ('09/12/03', '10/12/01')
    497201    304779902  AU           STD-CH-524-WXY-01     ('06/02/14', '07/02/14')
    497201    304779902  AU           STD-CH-524-WXY-02     ('07/02/14', '99/12/31')
    541077    766492008  HP           STD-CH-344-YXY-00     ('09/12/21', '99/12/31')

The valid-time column, Policy_Term, is treated as a regular column with a period data type, and appears in the result set. All rows in the table are returned, even if they are history rows.

Example : Nonsequenced Query on a Transaction-Time Table

To query a transaction-time table such that no special semantics are placed on the transaction-time column, use the NONSEQUENCED TRANSACTIONTIME temporal qualifier in the SELECT statement. For example:

   NONSEQUENCED TRANSACTIONTIME SELECT * FROM Policy_Types;
Policy_Name          Policy_Type  Policy_Duration
-------------------  -----------  ----------------------------------------------------------------------
Premium Automobile   AP          ('2012-06-19 20:04:18.470000-07:00', '9999-12-31 23:59:59.999999+00:00')
Basic Homeowner      HM          ('2012-06-19 20:04:32.410000-07:00', '9999-12-31 23:59:59.999999+00:00')
Business             B           ('2012-06-19 20:04:32.680000-07:00', '9999-12-31 23:59:59.999999+00:00')
Basic Automobile     AU          ('2012-06-19 20:04:32.470000-07:00', '9999-12-31 23:59:59.999999+00:00')
Major Medical        M           ('2012-06-19 20:04:32.550000-07:00', '2012-06-19 20:19:54.100000-07:00')
Premium Homeowner    HP          ('2012-06-19 20:04:32.610000-07:00', '9999-12-31 23:59:59.999999+00:00')

Notice that the Major Medical policy type is a closed row. It shows an end transaction time prior to 9999-12-31. This indicates that the row was logically deleted from the table at the ending transaction time, on 2012-06-19. However, because the row is in a table with a transaction-time dimension, the physical row persists in the table as a closed row.

Example : As Of Query on a Valid-Time Table

To get a snapshot of a valid-time table where the valid-time period in the result rows overlap a specific time, use the VALIDTIME AS OF temporal qualifier in the SELECT statement. For example:

VALIDTIME AS OF DATE '2005-03-14' SELECT * FROM Policy;

 Policy_ID  Customer_ID  Policy_Type  Policy_Details
 ---------  -----------  -----------  -------------------
    497201    304779902  AU           STD-CH-524-WXY-00

AS OF also accepts business calendar functions as date input. This request returns all policies that were valid on January 1, 2011. For more information on the business calendar functions, see SQL Functions, Operators, Expressions, and Predicates.

VALIDTIME AS OF TD_YEAR_BEGIN(DATE ‘2011-05-24’) SELECT * FROM Policy;
  Policy_ID  Customer_ID  Policy_Type  Policy_Details
-----------  -----------  -----------  --------------------
     541008    246824626  AP           STD-CH-345-NXY-00
     540944    123344567  HM           STD-PL-332-YXY-00
     497201    304779902  AU           STD-CH-524-WXY-02
     541077    766492008  HP           STD-CH-344_YXY-00

Example : As Of Query on a Transaction-Time Table

To get a snapshot of a transaction-time table where the transaction-time period in the result rows overlap a specific time, use the TRANSACTIONTIME AS OF temporal qualifier in the SELECT statement. For example:

   TRANSACTIONTIME AS OF DATE '2012-06-20' SELECT * FROM Policy_Types;

Policy_Name           Policy_Type
--------------------  -----------
Premium Automobile    AP
Basic Homeowner       HM
Business              B
Basic Automobile      AU
Premium Homeowner     HP

Notice that the Major Medical policy type that was logically deleted from the table on 2012-06-19 does not appear in the result set, though it did appear in Example 5 when the NONSEQUENCED TRANSACTIONTIME qualifier to SELECT was used.