Teradata Period Datatype
- Period can be defined as duration which is having beginning bound(start date/time) and ending bound(end date/time).
- Teradata support Period datatype of elements types DATE, TIME and TIMESTAMP.
- It is possible that ending bound is not present for a period and this indicate that period is still active or on going.
- Can be effectively used for storing data as SCD(slowly changing dimention) type 2 with start data/time and end date/time.
- This tutorial will describe how to define, insert and perform various operations on data in tables with 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:
- PERIOD(DATE) can be compared with other PERIOD(DATE).
- PERIOD(TIME) can be compared with other PERIOD(TIME).
- PERIOD(TIMESTAMP) can be compared with other PERIOD(TIMESTAMP) only.
Data granularity for each Period Datatypes
- DATE : Granularity for the Period(DATE) datatype is 1 day.
- Time(0) : Granularity for the Period(TIME(0)) datatype is 1 second.
- Time(3) : Granularity for the Period(TIME(3)) datatype is 1 millisecond.
- Time(6) : Granularity for the Period(TIME(6)) datatype is 1 microsecond.
- TIMESTAMP(0) : Granularity for the Period(TIMESTAMP(0)) datatype is 1 second.
- TIMESTAMP(3) : Granularity for the Period(TIMESTAMP(3)) datatype is 1 millisecond.
- TIMESTAMP(6) : Granularity for the Period(TIMESTAMP(6)) datatype is 1 microsecond.
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')
- 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
- 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')
- 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')
- 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')
- 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
- 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
- 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
- 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.