Teradata Period Datatype

Teradata Period Datatype


Sample table having Period datatype and how to insert data in tables with Period datatype.

CREATE MULTISET TABLE samplePeriodTable (
  id                      INTEGER,
  validity_dates          PERIOD(DATE),
  validity_time_0         PERIOD(TIME(0)),
  validity_time_3         PERIOD(TIME(3)),
  validity_time_6         PERIOD(TIME(6)),
  validity_timestamp_0    PERIOD(TIMESTAMP(0)),
  validity_timestamp_3    PERIOD(TIMESTAMP(3)),
  validity_timestamp_6    PERIOD(TIMESTAMP(6))
)
PRIMARY INDEX (id);

--Data
INSERT INTO samplePeriodTable(1,
                              PERIOD(CURRENT_DATE-10,CURRENT_DATE),
                              PERIOD(CURRENT_TIME(0)-INTERVAL '10' MINUTE,CURRENT_TIME(0)),
                              PERIOD(CURRENT_TIME(3)-INTERVAL '10' MINUTE,CURRENT_TIME(3)),
                              PERIOD(CURRENT_TIME(6)-INTERVAL '10' MINUTE,CURRENT_TIME(6)),
                              PERIOD(CURRENT_TIMESTAMP(0)-INTERVAL '10' MINUTE,CURRENT_TIMESTAMP(0)),
                              PERIOD(CURRENT_TIMESTAMP(3)-INTERVAL '10' MINUTE,CURRENT_TIMESTAMP(3)),
                              PERIOD(CURRENT_TIMESTAMP(6)-INTERVAL '10' MINUTE,CURRENT_TIMESTAMP(6))
);


Period Datatype Comparison: Two Period datatype values can be compared if their element types are of same date/time. For example:

Data granularity for each Period Datatypes

Teradata provides multiple functions to perform various operations on Period datatype.
Lets create a table having Period data type with sample data for better understanding Period datatype functions/operations.

CREATE MULTISET TABLE person_insurance_policy (
  person_id       INTEGER,
  policy_id INTEGER,
  validity_dates       PERIOD(DATE))
PRIMARY INDEX (person_id);

--Data
INSERT INTO person_insurance_policy VALUES(11,110,period(date '2018-02-03', date '2018-02-10'));
INSERT INTO person_insurance_policy VALUES(11,110,period(date '2018-02-10', UNTIL_CHANGED));
INSERT INTO person_insurance_policy VALUES(10,100,period(date '2018-01-03', date '2018-01-11'));
INSERT INTO person_insurance_policy VALUES(10,100,period(date '2018-01-11', date '2018-06-06'));
INSERT INTO person_insurance_policy VALUES(10,100,period(date '2018-06-06', date '2018-08-06'));
INSERT INTO person_insurance_policy VALUES(10,100,period(date '2018-08-06', UNTIL_CHANGED));
INSERT INTO person_insurance_policy VALUES(12, 105, PERIOD(CURRENT_DATE,  UNTIL_CHANGED));
INSERT INTO person_insurance_policy VALUES(16, 101, PERIOD(CURRENT_DATE-10,  CURRENT_DATE));
Note: UNTIL_CHANGED can be used while populating a record to indicate that period has no end date or date/time and it is still open.

SELECT * FROM person_insurance_policy ORDER BY person_id,validity_dates;

  person_id    policy_id  validity_dates
-----------  -----------  ----------------------------
         10          100  ('2018-01-03', '2018-01-11')
         10          100  ('2018-01-11', '2018-06-06')
         10          100  ('2018-06-06', '2018-08-06')
         10          100  ('2018-08-06', '9999-12-31') <---UNTIL_CHANGED record
         11          110  ('2018-02-03', '2018-02-10')
         11          110  ('2018-02-10', '9999-12-31') <---UNTIL_CHANGED record
         12          105  ('2018-09-15', '9999-12-31') <---UNTIL_CHANGED record
         16          101  ('2018-09-05', '2018-09-15')


  1. IS UNTIL_CHANGED: This clause can be used to find records which are still open and don't have any end date.
    
    SELECT * FROM person_insurance_policy WHERE END(validity_dates) IS UNTIL_CHANGED;
      person_id    policy_id  validity_dates
    -----------  -----------  ----------------------------
             11          110  ('2018-02-10', '9999-12-31')
             10          100  ('2018-08-06', '9999-12-31')
             12          105  ('2018-09-18', '9999-12-31')
    

    Update end date for a period which don't have ending bound
    
    UPDATE person_insurance_policy
    SET validity_dates= PERIOD(BEGIN(validity_dates), DATE '2018-09-10')
    WHERE policy_id=110
    AND person_id=11
    AND END(validity_dates) IS UNTIL_CHANGED;
    
     *** Update completed. One row changed. 
    
    
    SELECT * FROM person_insurance_policy WHERE policy_id=110 AND person_id=11
    
      person_id    policy_id  validity_dates
    -----------  -----------  ----------------------------
             11          110  ('2018-02-03', '2018-02-10')
             11          110  ('2018-02-10', '2018-09-10')
    
    Note: Record which was open earlier for person_id 11, is now closed with date 2018-09-10


  2. BEGIN: This function returns start value of the Period argument.
    
    SELECT person_id,
           policy_id, 
           BEGIN(validity_dates),
           validity_dates
    FROM person_insurance_policy
    ORDER BY person_id,validity_dates;
    
      person_id    policy_id  BEGIN(validity_dates)  validity_dates
    -----------  -----------  ---------------------  ----------------------------
             10          100             2018-01-03  ('2018-01-03', '2018-01-11')
             10          100             2018-01-11  ('2018-01-11', '2018-06-06')
             10          100             2018-06-06  ('2018-06-06', '2018-08-06')
             10          100             2018-08-06  ('2018-08-06', '9999-12-31')
             11          110             2018-02-03  ('2018-02-03', '2018-02-10')
             11          110             2018-02-10  ('2018-02-10', '9999-12-31')
             12          105             2018-09-15  ('2018-09-15', '9999-12-31')
             16          101             2018-09-05  ('2018-09-05', '2018-09-15')
    
    


  3. END: This function returns end value of the Period argument.
    
    SELECT person_id,
           policy_id,
           END(validity_dates),
           validity_dates
    FROM person_insurance_policy
    ORDER BY person_id,validity_dates;
    
      person_id    policy_id  END(validity_dates)  validity_dates
    -----------  -----------  -------------------  ----------------------------
             10          100           2018-01-11  ('2018-01-03', '2018-01-11')
             10          100           2018-06-06  ('2018-01-11', '2018-06-06')
             10          100           2018-08-06  ('2018-06-06', '2018-08-06')
             10          100           9999-12-31  ('2018-08-06', '9999-12-31')
             11          110           2018-02-10  ('2018-02-03', '2018-02-10')
             11          110           9999-12-31  ('2018-02-10', '9999-12-31')
             12          105           9999-12-31  ('2018-09-15', '9999-12-31')
             16          101           2018-09-15  ('2018-09-05', '2018-09-15')
    


  4. EXPAND ON: This can be used to flatten period argument to rows which is equivalent to number of grains present in passed period argument.
    
    SELECT person_id,policy_id, BEGIN(begin_date),END(begin_date),validity_dates
    FROM person_insurance_policy
    WHERE person_id=16
    AND   policy_id=101
    AND   END(validity_dates) IS NOT UNTIL_CHANGED
    EXPAND ON validity_dates AS begin_date 
    
      person_id    Policy_id  BEGIN(begin_date)  END(begin_date)  validity_dates
    -----------  -----------  -----------------  ---------------  ----------------------------
             16          101         2018-09-08       2018-09-09  ('2018-09-08', '2018-09-18')
             16          101         2018-09-09       2018-09-10  ('2018-09-08', '2018-09-18')
             16          101         2018-09-10       2018-09-11  ('2018-09-08', '2018-09-18')
             16          101         2018-09-11       2018-09-12  ('2018-09-08', '2018-09-18')
             16          101         2018-09-12       2018-09-13  ('2018-09-08', '2018-09-18')
             16          101         2018-09-13       2018-09-14  ('2018-09-08', '2018-09-18')
             16          101         2018-09-14       2018-09-15  ('2018-09-08', '2018-09-18')
             16          101         2018-09-15       2018-09-16  ('2018-09-08', '2018-09-18')
             16          101         2018-09-16       2018-09-17  ('2018-09-08', '2018-09-18')
             16          101         2018-09-17       2018-09-18  ('2018-09-08', '2018-09-18')
    

    EXPAND ON with BY ANCHOR <WEEKDAY>: This will flatten period argument to rows and return only rows with particular week day as specified.
    
    SELECT person_id,policy_id, BEGIN(begin_date),validity_dates
    FROM person_insurance_policy
    WHERE person_id=16
    AND   policy_id=101
    AND   END(validity_dates) IS NOT UNTIL_CHANGED
    EXPAND ON validity_dates AS begin_date BY ANCHOR MONDAY;
         
      person_id    Policy_id  BEGIN(begin_date) validity_dates
    -----------  -----------  ----------------- ----------------------------
             16          101         2018-09-10 ('2018-09-08', '2018-09-18')
             16          101         2018-09-17 ('2018-09-08', '2018-09-18')
    


  5. LAST: This function returns value which 1 granule less than the end value of the period argument. Both END and LAST functions return the same value for a record that is UNTIL_CHANGED.
    
    SELECT person_id,
           policy_id,
           BEGIN(validity_dates),
           END(validity_dates),
           LAST(validity_dates) 
    FROM person_insurance_policy ORDER BY person_id,validity_dates;
    
      person_id    policy_id  BEGIN(validity_dates)  END(validity_dates)  LAST(validity_dates)
    -----------  -----------  ---------------------  -------------------  --------------------
             10          100             2018-01-03           2018-01-11            2018-01-10
             10          100             2018-01-11           2018-06-06            2018-06-05
             10          100             2018-06-06           2018-08-06            2018-08-05
             10          100             2018-08-06           9999-12-31            9999-12-31
             11          110             2018-02-03           2018-02-10            2018-02-09
             11          110             2018-02-10           9999-12-31            9999-12-31
             12          105             2018-09-18           9999-12-31            9999-12-31
    


  6. NEXT: This functions return value which is 1 granule more than the passed date(either start or end date od period argument). All open records(i.e. records with end date as UNTIL_CHANGED) should be filter before apply this function on End date.
    
    SELECT person_id,
           policy_id,
           BEGIN(validity_dates),
           END(validity_dates),
           NEXT(BEGIN(validity_dates)) 
     FROM person_insurance_policy ORDER BY person_id,validity_dates;
    
      person_id    policy_id  BEGIN(validity_dates)  END(validity_dates)  NEXT(BEGIN(validity_dates))
    -----------  -----------  ---------------------  -------------------  ---------------------------
             10          100             2018-01-03           2018-01-11                   2018-01-04
             10          100             2018-01-11           2018-06-06                   2018-01-12
             10          100             2018-06-06           2018-08-06                   2018-06-07
             10          100             2018-08-06           9999-12-31                   2018-08-07
             11          110             2018-02-03           2018-02-10                   2018-02-04
             11          110             2018-02-10           9999-12-31                   2018-02-11
             12          105             2018-09-18           9999-12-31                   2018-09-19
             13          120             2008-01-11           2018-06-06                   2008-01-12
    


  7. PRIOR: This functions return value which is 1 granule less than the passed period element(either start or end of period argument). PRIOR functions works both on start and end bound which makes it different from LAST function.
    
    SELECT person_id,
           policy_id,
           BEGIN(validity_dates),
           END(validity_dates),
           PRIOR(BEGIN(validity_dates)) PRIOR_BEGIN,
           PRIOR(END(validity_dates)) PRIOR_END 
    FROM person_insurance_policy ORDER BY person_id,validity_dates;
    
    person_id policy_id BEGIN(validity_dates) END(validity_dates) PRIOR_BEGIN PRIOR_END
    --------- --------- --------------------- ------------------- ----------- -----------
           10       100            2018-01-03          2018-01-11  2018-01-02  2018-01-10
           10       100            2018-01-11          2018-06-06  2018-01-10  2018-06-05
           10       100            2018-06-06          2018-08-06  2018-06-05  2018-08-05
           10       100            2018-08-06          9999-12-31  2018-08-05  9999-12-30
           11       110            2018-02-03          2018-02-10  2018-02-02  2018-02-09
           11       110            2018-02-10          9999-12-31  2018-02-09  9999-12-30
           12       105            2018-09-18          9999-12-31  2018-09-17  9999-12-30
           13       120            2008-01-11          2018-06-06  2008-01-10  2018-06-05
    


  8. INTERVAL: This function returns the difference between the ending and beginning bounds of a Period argument in terms of specific Interval identifier.
    
    INSERT INTO person_insurance_policy VALUES(13,120,period(date '2008-01-11', date '2018-06-06'));
    
    
    SELECT
        person_id,
        policy_id,
        validity_dates,
        INTERVAL(validity_dates) YEAR(4)   AS YearDiff,
        INTERVAL(validity_dates) MONTH(4)  AS MonthDiff,
        INTERVAL(validity_dates) DAY(4) AS DayDiff
    FROM person_insurance_policy
    WHERE END(validity_dates) IS NOT UNTIL_CHANGED     -- Only those that are ended else it will fail with "Interval field overflow."
    
      person_id    policy_id  validity_dates                YearDiff  MonthDiff  DayDiff
    -----------  -----------  ----------------------------  --------  ---------  -------
             11          110  ('2018-02-03', '2018-02-10')         0          0        7
             10          100  ('2018-01-03', '2018-01-11')         0          0        8
             13          120  ('2008-01-11', '2018-06-06')        10        125     3799
             10          100  ('2018-01-11', '2018-06-06')         0          5      146
             10          100  ('2018-06-06', '2018-08-06')         0          2       61
    
    Note: Difference in number of days, (Hour,Minute & second in case of time & timestamp period type) should not exceed 9999 else the query will fail with error "Interval field overflow" Hour,Minute & second arguments are not valid to be used with DATE Period type.