Exploring Teradata 13's PERIOD Functions

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Exploring Teradata 13's PERIOD Functions

The PERIOD data type was introduced in Teradata 13.0. This follow-on article builds on the first article (Exploring Teradata 13's PERIOD Data Type) and will again make heavy use of examples, but this time we will dig into the functions that are part of this new feature set.

If you have not read the first article, consider doing that now and then returning here. The examples here will use the tables introduced in the first article and add a few more.

PERIOD Functions and NULL

One fact that is true for all the PERIOD functions is that <function>(<PERIOD column>) will return NULL for any PERIOD value that is NULL.

Examples and Code

Since the concepts were already explained, let's get right into the functions and examples.

BEGIN()

BEGIN "returns the beginning bound of the Period argument."

BEGIN() in the SELECT Clause

INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-01-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-02-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(CURRENT_DATE, UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1004, 'SC', NULL);

SELECT
person_id,
enrolled_period,
BEGIN(enrolled_period) AS BeginPeriod
FROM person_coaching_period
ORDER BY person_id, coaching_program, enrolled_period;

Results

person_id enrolled_period BeginPeriod
1001 (2010-01-01, 9999-12-31) 1/1/2010
1002 (2010-02-01, 9999-12-31) 2/1/2010
1003 (2010-11-03, 9999-12-31) 11/3/2010
1004 NULL NULL

BEGIN() in the WHERE Clause

-- INSERT from above

SELECT * FROM person_coaching_period
WHERE BEGIN(enrolled_period) = DATE '2010-02-01';

SELECT * FROM person_coaching_period
WHERE BEGIN(enrolled_period) = CURRENT_DATE;

SELECT * FROM person_coaching_period
WHERE BEGIN(enrolled_period) IS NULL;

Results

person_id coaching_program enrolled_period
1002 SC (2010-02-01, 9999-12-31)
person_id coaching_program enrolled_period
1003 SC (2010-11-03, 9999-12-31)
person_id coaching_program enrolled_period
1004 SC NULL

END()

END "returns the ending bound of the Period argument."

END() in the SELECT Clause

INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-01-01', DATE '2010-06-30'));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-02-01', DATE '2010-12-31'));
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(DATE '2010-03-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1004, 'SC', NULL);

SELECT
person_id,
enrolled_period,
END(enrolled_period) AS EndPeriod
FROM person_coaching_period
ORDER BY person_id, coaching_program, enrolled_period;

Results

person_id enrolled_period EndPeriod
1001 (2010-01-01, 2010-06-30) 6/30/2010
1002 (2010-02-01, 2010-12-31) 12/31/2010
1003 (2010-03-01, 9999-12-31) 12/31/9999
1004 NULL NULL

END() in the WHERE Clause

-- INSERT from above

SELECT * FROM person_coaching_period
WHERE END(enrolled_period) = DATE '2010-12-31';

SELECT * FROM person_coaching_period
WHERE END(enrolled_period) = DATE '2010-03-01'; -- No match for END

SELECT * FROM person_coaching_period
WHERE END(enrolled_period) IS UNTIL_CHANGED; -- All open records

SELECT * FROM person_coaching_period
WHERE END(enrolled_period) IS NULL;

Results

END(enrolled_period) = DATE '2010-12-31'

person_id coaching_program enrolled_period
1002 SC (2010-02-01, 2010-12-31)

END(enrolled_period) = DATE '2010-03-01'

person_id coaching_program enrolled_period

END(enrolled_period) IS UNTIL_CHANGED

person_id coaching_program enrolled_period
1003 SC (2010-03-01, 9999-12-31)

END(enrolled_period) IS NULL

person_id coaching_program enrolled_period
1004 SC NULL

A Note on [IS|IS NOT] UNTIL_CHANGED and NULL Values

In this query from above:

SELECT * FROM person_coaching_period
WHERE END(enrolled_period) IS UNTIL_CHANGED; -- All open records

it is important to understand that "IS UNTIL_CHANGED" also filters out records with a NULL Period.

To include records with NULL Period values when using IS UNTIL_CHANGED or IS NOT UNTIL_CHANGED, simply add "OR <Period column> IS NULL".

LAST()

LAST "returns the last value of the Period argument (that is, the ending bound minus one granule of the element type of the argument)."

LAST() in the SELECT Clause with DATE Column

INSERT INTO person_coaching_period VALUES (1001, 'WM', PERIOD(DATE '2010-08-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-08-01', CURRENT_DATE + 1)); -- Today ends period
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(DATE '2010-01-01', DATE '2010-07-31'));
INSERT INTO person_coaching_period VALUES (1004, 'WM', NULL);

SELECT
person_id,
enrolled_period,
LAST(enrolled_period) AS LastDate
FROM person_coaching_period
ORDER BY person_id;

Results ("12:00:00 AM" has been removed from LastDate entries for readability.)

person_id enrolled_period LastDate
1001 ('2010-08-01', '9999-12-31') 12/31/9999
1002 ('2010-08-01', '2010-11-30') 11/29/2010
1003 ('2010-01-01', '2010-07-31') 7/30/2010
1004 NULL NULL

LAST() in the WHERE Clause with DATE Column

-- INSERT from above

SELECT * FROM person_coaching_period
WHERE LAST(enrolled_period) = DATE '2010-07-31';

SELECT * FROM person_coaching_period
WHERE LAST(enrolled_period) = CURRENT_DATE;

SELECT * FROM person_coaching_period
WHERE LAST(enrolled_period) IS NULL; -- All records with NULL for PERIOD

Results

First query: no results. No records have a LAST() value of '2010-07-31'.

Second query: The results here illustrate how the Closed/Open concept (explained in the first article) interacts with LAST(). We inserted a record with an end date of "CURRENT_DATE + 1". Here, we are looking for records "WHERE LAST(enrolled_period) = CURRENT_DATE".

The first date that is excluded is tomorrow; the last date that is included is today.

person_id coaching_program enrolled_period
1002 SC ('2010-08-01', '2010-11-30')

Third query: Again, a function on a NULL Period returns NULL.

person_id coaching_program enrolled_period
1004 WM NULL

LAST() in the SELECT Clause with TIMESTAMP Column

INSERT INTO person_time_clock VALUES (1001, PERIOD(TIMESTAMP '2010-04-21 08:00:00',        TIMESTAMP '2010-04-21 17:00:00'));
INSERT INTO person_time_clock VALUES (1002, PERIOD(TIMESTAMP '2010-04-22 07:58:21.388218', TIMESTAMP '2010-04-22 17:14:58.824329'));
INSERT INTO person_time_clock VALUES (1003, PERIOD(CURRENT_TIMESTAMP, UNTIL_CHANGED));

SELECT *
FROM person_time_clock
ORDER BY person_id, work_period;

Results

person_id work_period
1001 ('2010-04-21 08:00:00.000000', '2010-04-21 17:00:00.000000')
1002 ('2010-04-22 07:58:21.388218', '2010-04-22 17:14:58.824329')
1003 ('2010-11-29 10:59:13.950000', '9999-12-31 23:59:59.999999')

Query

SELECT
person_id,
BEGIN(work_period) AS FirstOfShift,
END(work_period) AS EndOfShift,
LAST(work_period) AS LastOfShift
FROM person_time_clock
ORDER BY person_id, work_period;

Results

This last query illustrates BEGIN(), END(), and LAST() values all together.

person_id FirstOfShift EndOfShift LastOfShift
1001 04/21/2010 8:00:00.000000 04/21/2010 5:00:00.000000 04/21/2010 4:59:59.999999
1002 04/22/2010 7:58:21.388218 04/22/2010 5:14:58.824329 04/22/2010 5:14:58.824328
1003 11/29/2010 10:59:13.950000 12/31/9999 11:59:59.999999 12/31/9999 11:59:59.999999

Observations

  • LAST(), as advertised, returns "the ending bound minus one granule of the element type of the argument". In this case, that one granule is very small.
  • END() and LAST() both return the same value for a record that is UNTIL_CHANGED.

INTERVAL()

INTERVAL "finds the difference between the ending and beginning bounds of a Period argument and returns this difference as the duration of the argument in terms of a specified interval qualifier."

INSERT

INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2009-03-01',    DATE '2009-08-01'));
INSERT INTO person_coaching_period VALUES (1001, 'WM', PERIOD(DATE '2010-01-01', DATE '2010-07-01'));
INSERT INTO person_coaching_period VALUES (1002, 'WM', PERIOD(DATE '2010-08-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(DATE '2009-04-01', DATE '2009-09-01'));
INSERT INTO person_coaching_period VALUES (1004, 'WM', PERIOD(DATE '2010-03-01', DATE '2010-10-01'));
INSERT INTO person_coaching_period VALUES (1004, 'SC', NULL);
INSERT INTO person_coaching_period VALUES (1005, 'WM', PERIOD(DATE '2008-01-01', DATE '2010-12-31'));
INSERT INTO person_coaching_period VALUES (1006, 'SC', PERIOD(DATE '2010-06-01', UNTIL_CHANGED));

Query: Find the duration of coaching enrollments that have ended

SELECT
person_id,
coaching_program AS pgm,
enrolled_period,
INTERVAL(enrolled_period) YEAR AS YearCt,
INTERVAL(enrolled_period) MONTH AS MonthCt,
INTERVAL(enrolled_period) DAY(4) AS DayCt -- Note that DAY does not work (overflow)
FROM person_coaching_period
WHERE END(enrolled_period) IS NOT UNTIL_CHANGED -- Only those that are ended
ORDER BY person_id, coaching_program, enrolled_period;

Results

person_id pgm enrolled_period YearCt MonthCt DayCt
1001 SC ('2009-03-01', '2009-08-01') 0 5 153
1001 WM ('2010-01-01', '2010-07-01') 0 6 181
1003 SC ('2009-04-01', '2009-09-01') 0 5 153
1004 WM ('2010-03-01', '2010-10-01') 0 7 214
1005 WM ('2008-01-01', '2010-12-31') 2 35 1095

Query: Find the average duration of coaching enrollments that have ended by program

SELECT
coaching_program,
AVG(INTERVAL(enrolled_period) DAY(4)) AS AvgDaysEnrolled
FROM person_coaching_period
WHERE END(enrolled_period) IS NOT UNTIL_CHANGED -- Only those that are ended
GROUP BY coaching_program
ORDER BY coaching_program;

coaching_program AvgDaysEnrolled
SC 153
WM 497

Query: Find records that are closed and were open for at least 90 days

SELECT
person_id,
coaching_program,
enrolled_period
FROM person_coaching_period
WHERE END(enrolled_period) IS NOT UNTIL_CHANGED -- Only those that are ended
AND INTERVAL(enrolled_period) DAY(4) >= 90
ORDER BY person_id, coaching_program, enrolled_period;

Results

person_id coaching_program enrolled_period
1001 SC ('2009-03-01', '2009-08-01')
1001 WM ('2010-01-01', '2010-07-01')
1003 SC ('2009-04-01', '2009-09-01')
1004 WM ('2010-03-01', '2010-10-01')
1005 WM ('2008-01-01', '2010-12-31')

Query: Find records that are closed and were open for more than 6 months

SELECT
person_id,
coaching_program,
enrolled_period
FROM person_coaching_period
WHERE END(enrolled_period) IS NOT UNTIL_CHANGED -- Only those that are ended
AND INTERVAL(enrolled_period) MONTH > 6
ORDER BY person_id, coaching_program, enrolled_period;

Results

person_id coaching_program enrolled_period
1004 WM ('2010-03-01', '2010-10-01')
1005 WM ('2008-01-01', '2010-12-31')

PRIOR()

PRIOR "returns the preceding value of the argument such that there is one granule of the argument type between the returned value and the argument."

PRIOR() in the SELECT Clause

INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-03-01', DATE '2010-08-01'));
INSERT INTO person_coaching_period VALUES (1001, 'WM', PERIOD(DATE '2010-08-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-08-01', CURRENT_DATE + 1)); -- Today ends period
INSERT INTO person_coaching_period VALUES (1004, 'SC', NULL);

SELECT
person_id,
coaching_program AS pgm,
enrolled_period,
LAST(enrolled_period) AS LastDate,
PRIOR(END(enrolled_period)) AS PriorEndDate
FROM person_coaching_period
ORDER BY person_id, coaching_program, enrolled_period;

Results ("12:00:00 AM" has been removed from LastDate and PriorEndDate entries for readability.)

person_id pgm enrolled_period LastDate PriorEndDate
1001 SC ('2010-03-01', '2010-08-01') 7/31/2010 7/31/2010
1001 WM ('2010-08-01', '9999-12-31') 12/31/9999 12/30/9999
1002 SC ('2010-08-01', '2010-11-30') 11/29/2010 11/29/2010
1004 SC NULL NULL NULL

Observations

Notice that LastDate and PriorEndDate are the same for the first and third rows. It differs by a day for the UNTIL_CHANGED entry, but that makes sense when you think about the order of processing.

Since these (generally) produce the same result, does LAST() have value other than keeping you from having to type both PRIOR and END? Yes, because PRIOR() works on both END and BEGIN.

Query: Find the last date before the coaching period began

SELECT
person_id,
coaching_program AS pgm,
enrolled_period,
BEGIN(enrolled_period) AS BeginDate,
PRIOR(BEGIN(enrolled_period)) AS LastDateBeforeEnrolled
FROM person_coaching_period
ORDER BY person_id, coaching_program, enrolled_period;

Results ("12:00:00 AM" has been removed from BeginDate and LastDateBeforeEnrolled entries for readability.)

person_id pgm enrolled_period BeginDate LastDateBeforeEnrolled
1001 SC ('2010-03-01', '2010-08-01') 3/1/2010 2/28/2010
1001 WM ('2010-08-01', '9999-12-31') 8/1/2010 7/31/2010
1002 SC ('2010-08-01', '2010-11-30') 8/1/2010 7/31/2010
1004 SC NULL NULL NULL

PRIOR() in the WHERE Clause

SELECT * FROM person_coaching_period
WHERE PRIOR(END(enrolled_period)) = DATE '2010-07-31';

Results

person_id coaching_program enrolled_period
1001 SC ('2010-03-01', '2010-08-01')

NEXT()

NEXT "returns the succeeding value of the argument such that there is one granule of the argument type between the argument and the returned value."

Be aware that you need to exclude UNTIL_CHANGED records when you query for NEXT(END(<period>)). Without that, you will receive this error: "Invalid date supplied".

NEXT() in the SELECT Clause

INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-03-01',    DATE '2010-08-01'));
INSERT INTO person_coaching_period VALUES (1001, 'WM', PERIOD(DATE '2010-08-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-08-01', CURRENT_DATE + 1)); -- Today ends period
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(DATE '2010-04-01', DATE '2010-09-01'));
INSERT INTO person_coaching_period VALUES (1004, 'WM', NULL);
INSERT INTO person_coaching_period VALUES (1005, 'SC', PERIOD(DATE '2010-06-01', DATE '2010-10-01'));

SELECT
person_id,
enrolled_period,
NEXT(BEGIN(enrolled_period)) AS DateAfterEnrollmentOpened,
NEXT(END(enrolled_period)) AS DateAfterEnrollmentClosed
FROM person_coaching_period
WHERE END(enrolled_period) IS NOT UNTIL_CHANGED
ORDER BY person_id, coaching_program, enrolled_period;

Results ("12:00:00 AM" has been removed from LastDate entries for readability.)

person_id enrolled_period DateAfterEnrollmentOpened DateAfterEnrollmentClosed
1001 ('2010-03-01', '2010-08-01') 3/2/2010 8/2/2010
1002 ('2010-08-01', '2010-12-03') 8/2/2010 12/4/2010
1003 ('2010-04-01', '2010-09-01') 4/2/2010 9/2/2010
1005 ('2010-06-01', '2010-10-01') 6/2/2010 10/2/2010

NEXT() in the WHERE Clause

-- INSERT from above

SELECT *
FROM person_coaching_period
WHERE END(enrolled_period) IS NOT UNTIL_CHANGED -- "Invalid date supplied" if not included
AND NEXT(END(enrolled_period)) = '2010-08-02'
ORDER BY person_id, coaching_program, enrolled_period;

Results

person_id coaching_program enrolled_period
1001 SC ('2010-03-01', '2010-08-01')

P_INTERSECT()

P_INTERSECT "returns the portion of the Period expressions that is common between the Period expressions if they overlap."

P_INTERSECT() in the JOIN Clause

INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-03-01', DATE '2010-08-01'));
INSERT INTO person_coaching_period VALUES (1001, 'WM', PERIOD(DATE '2010-08-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-08-01', CURRENT_DATE + 1)); -- Today ends period
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(DATE '2010-04-01', DATE '2010-09-01'));
INSERT INTO person_coaching_period VALUES (1004, 'WM', NULL);
INSERT INTO person_coaching_period VALUES (1005, 'SC', PERIOD(DATE '2010-06-01', DATE '2010-10-01'));

INSERT INTO coaching_period VALUES ('2010Q1', PERIOD(DATE '2010-01-01', DATE '2010-04-01'));
INSERT INTO coaching_period VALUES ('2010Q2', PERIOD(DATE '2010-04-01', DATE '2010-07-01'));
INSERT INTO coaching_period VALUES ('2010Q3', PERIOD(DATE '2010-07-01', DATE '2010-10-01'));
INSERT INTO coaching_period VALUES ('2010Q4', PERIOD(DATE '2010-10-01', DATE '2011-01-01'));

-- The idea here is that we have multiple coaching periods and multiple quarters.

-- Find records that overlap with quarters
SELECT
pcp.person_id,
pcp.coaching_program,
pcp.enrolled_period,
cp.period_name
FROM person_coaching_period AS pcp
JOIN coaching_period AS cp
ON cp.program_period P_INTERSECT pcp.enrolled_period IS NOT NULL
WHERE 1 = 1
AND cp.period_name = '2010Q2'
ORDER BY
pcp.enrolled_period,
cp.period_name,
pcp.person_id,
pcp.coaching_program;

Results

person_id coaching_program enrolled_period period_name
1001 SC ('2010-03-01', '2010-08-01') 2010Q2
1003 SC ('2010-04-01', '2010-09-01') 2010Q2
1005 SC ('2010-06-01', '2010-10-01') 2010Q2

LDIFF()

LDIFF "returns the portion of the first Period expression that exists before the beginning of the second Period expression when the Period expressions overlap."

Table with PERIOD(DATE)

CREATE MULTISET TABLE person_employment_period (
person_id INTEGER NOT NULL,
employment_period PERIOD(DATE) FORMAT 'YYYY-MM-DD' NULL
)
PRIMARY INDEX (person_id);

This table stores data about an employee (person_id), and their period of employment.

LDIFF() in the SELECT Clause

INSERT INTO person_employment_period VALUES (1001,       PERIOD(DATE '2008-04-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-03-01', DATE '2010-08-01'));

INSERT INTO person_employment_period VALUES (1002, PERIOD(DATE '2008-01-01', DATE '2010-10-01'));
INSERT INTO person_coaching_period VALUES (1002, 'WM', PERIOD(DATE '2010-08-01', DATE '2010-09-01'));

INSERT INTO person_employment_period VALUES (1003, PERIOD(DATE '2008-01-01', DATE '2010-07-01'));

INSERT INTO person_employment_period VALUES (1004, PERIOD(DATE '2009-11-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1004, 'SC', PERIOD(DATE '2010-04-01', UNTIL_CHANGED));

-- Find period between starting employment and starting coaching
SELECT
    pep.person_id,
    BEGIN(pep.employment_period)                    AS BeginEmploy,
    pcp.coaching_program                            AS pgm,
    BEGIN(pcp.enrolled_period)                      AS BeginCoach,
    pep.employment_period LDIFF pcp.enrolled_period AS EmployToCoachDelayPeriod
FROM person_employment_period   AS pep
JOIN person_coaching_period     AS pcp
  ON pep.person_id = pcp.person_id
--WHERE END(pcp.enrolled_period) IS NOT UNTIL_CHANGED       -- Not needed here
ORDER BY
    pep.person_id,
    pep.employment_period,
    pcp.enrolled_period,
    pcp.coaching_program;

Results ("12:00:00 AM" has been removed from LastDate entries for readability.)

person_id BeginEmploy pgm BeginCoach EmployToCoachDelayPeriod
1001 4/1/2008 SC 3/1/2010 ('2008-04-01', '2010-03-01')
1002 1/1/2008 WM 8/1/2010 ('2008-01-01', '2010-08-01')
1004 11/1/2009 WC 4/1/2010 ('2009-11-01', '2010-04-01')
SELECT
pep.person_id,
pcp.coaching_program AS pgm,
INTERVAL(pep.employment_period LDIFF pcp.enrolled_period) MONTH AS MonthCt
FROM person_employment_period AS pep
JOIN person_coaching_period AS pcp
ON pep.person_id = pcp.person_id
ORDER BY
pep.person_id,
pcp.coaching_program;

Results

person_id pgm MonthCt
1001 SC 23
1002 WM 31
1004 SC 5

RDIFF()

RDIFF "returns the portion of the first Period expression that exists from the end of the second Period expression when the Period expressions overlap."

RDIFF() in the SELECT Clause

INSERT INTO person_employment_period VALUES (1001,       PERIOD(DATE '2008-04-01', DATE '2010-08-12'));
INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-03-01', DATE '2010-08-01'));

INSERT INTO person_employment_period VALUES (1002, PERIOD(DATE '2008-01-01', DATE '2010-10-01'));
INSERT INTO person_coaching_period VALUES (1002, 'WM', PERIOD(DATE '2010-08-01', DATE '2010-09-01'));

INSERT INTO person_employment_period VALUES (1003, PERIOD(DATE '2008-01-01', DATE '2010-07-01'));

INSERT INTO person_employment_period VALUES (1004, PERIOD(DATE '2009-11-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1004, 'SC', PERIOD(DATE '2010-04-01', UNTIL_CHANGED));

-- Find period between completing coaching and ending employment
SELECT
pep.person_id,
END(pep.employment_period) AS EndEmploy,
pcp.coaching_program AS pgm,
    END(pcp.enrolled_period)                        AS EndEnroll,
pep.employment_period RDIFF pcp.enrolled_period AS EndEnrollToEndEmployPeriod
FROM person_employment_period AS pep
JOIN person_coaching_period AS pcp
ON pep.person_id = pcp.person_id
WHERE END(pep.employment_period) IS NOT UNTIL_CHANGED -- Employment has ended
ORDER BY
pep.person_id,
pcp.coaching_program;

Results ("12:00:00 AM" has been removed from LastDate entries for readability.)

person_id EndEmploy pgm EndEnroll EndEnrollToEndEmployPeriod
1001 8/12/2010 SC 8/1/2010 ('2010-08-01', '2010-08-12')
1002 10/1/2010 WM 9/1/2010 ('2010-09-01', '2010-10-01')
-- Show above results in number of days
SELECT
pep.person_id,
pcp.coaching_program,
INTERVAL(pep.employment_period RDIFF pcp.enrolled_period) DAY(4) AS DayCt
FROM person_employment_period AS pep
JOIN person_coaching_period AS pcp
ON pep.person_id = pcp.person_id
WHERE END(pep.employment_period) IS NOT UNTIL_CHANGED
ORDER BY
pep.person_id,
pcp.coaching_program;

Results

person_id coaching_program DayCt
1001 SC 11
1002 WM 30

P_NORMALIZE()

P_NORMALIZE "returns a Period value that is the combination of the two Period expressions if the Period expressions overlap or meet."

P_NORMALIZE() in the SELECT and WHERE Clauses

DELETE FROM person_coaching_period;
INSERT INTO person_coaching_period VALUES (1001, 'WM', PERIOD(DATE '2010-03-01', DATE '2010-08-01'));
INSERT INTO person_coaching_period VALUES (1001, 'SC', PERIOD(DATE '2010-08-01', UNTIL_CHANGED));
INSERT INTO person_coaching_period VALUES (1002, 'SC', PERIOD(DATE '2010-08-01', CURRENT_DATE + 1)); -- Today ends period
INSERT INTO person_coaching_period VALUES (1003, 'SC', PERIOD(DATE '2010-04-01', DATE '2010-09-01'));
INSERT INTO person_coaching_period VALUES (1004, 'WM', NULL);
INSERT INTO person_coaching_period VALUES (1005, 'SC', PERIOD(DATE '2009-06-01', DATE '2010-02-04'));
INSERT INTO person_coaching_period VALUES (1005, 'WM', PERIOD(DATE '2010-02-01', DATE '2010-10-01'));
INSERT INTO person_coaching_period VALUES (1006, 'SC', PERIOD(DATE '2009-08-01', DATE '2010-01-01'));
INSERT INTO person_coaching_period VALUES (1006, 'WM', PERIOD(DATE '2010-01-02', UNTIL_CHANGED));

-- Find people that were enrolled in more than one program at a time.
SELECT
pcp1.person_id,
pcp1.coaching_program AS Pgm1,
pcp1.enrolled_period AS Pgm1Period,
pcp2.coaching_program AS Pgm2,
pcp2.enrolled_period AS Pgm2Period,
pcp1.enrolled_period P_NORMALIZE pcp2.enrolled_period AS FullEnrolledPeriod
FROM person_coaching_period AS pcp1
JOIN person_coaching_period AS pcp2
ON pcp1.person_id = pcp2.person_id
WHERE pcp1.enrolled_period <= pcp2.enrolled_period
AND pcp1.coaching_program <> pcp2.coaching_program
AND pcp1.enrolled_period P_NORMALIZE pcp2.enrolled_period IS NOT NULL
ORDER BY
pcp1.person_id,
pcp1.coaching_program;

Results

person_id Pgm1 Pgm1Period Pgm2 Pgm2Period FullEnrolledPeriod
1001 WM ('2010-03-01', '2010-08-01') SC ('2010-08-01', '9999-12-31') ('2010-03-01', '9999-12-31')
1005 SC ('2009-06-01', '2010-02-04') WM ('2010-02-01', '2010-10-01') ('2009-06-01', '2010-10-01')

Note that person_id 1001 is included because the two periods meet. There is no record for person_id 1006 because there is a one-day gap between the completion of SC and the start of WM.

Arithmetic Operators

Arithmetic operators "add or subtract an Interval value to or from a Period value, or add a Period value to an Interval value."

Here, we have two executives that are about to retire. The company will cover them with health insurance after leaving at a rate of 1 year for every 4 years served.

DELETE FROM person_employment_period;
INSERT INTO person_employment_period VALUES (1001, PERIOD(DATE '1997-01-01', CURRENT_DATE));
INSERT INTO person_employment_period VALUES (1002, PERIOD(DATE '1990-01-01', CURRENT_DATE));

-- Cover 1 year for every 4 years of employment
SELECT
person_id,
employment_period AS EmploymentPeriod,
(INTERVAL(employment_period) YEAR) AS YearsEmployed,
(INTERVAL(employment_period) YEAR) / 4 AS YearsEarned,
END(employment_period)
+ (INTERVAL(employment_period) YEAR) / 4 AS InsuranceCoverageEnds
FROM person_employment_period
ORDER BY person_id;

Results

person_id EmploymentPeriod YearsEmployed YearsEarned InsuranceCoverageEnds
1001 ('1997-01-01', '2011-01-24') 14 3 1/24/2014 12:00:00 AM
1002 ('1990-01-01', '2011-01-24') 21 5 1/24/2016 12:00:00 AM

Conclusion

These examples give you a starting point that should help you understand the functions, but you will have different requirements and will need to dig deeper.

Be aware that Teradata 13.10 introduces significant functionality beyond what is available in 13.0. Understanding the features and functionality avialable in 13.0 will give you a great foundation as you begin to absorb the full temporal feature set in 13.10.

References

4 REPLIES
Enthusiast

Re: Exploring Teradata 13's PERIOD Functions

Great article.
Ah... Is it possible to create a PPI on the column with Period datatype? If it were possbile, is it helpful to partition a period column?
Enthusiast

Re: Exploring Teradata 13's PERIOD Functions

According to the Teradata 13.10 SQL Data Types and Literals manual: "A primary index column or partitioning column cannot be a column that has a Period data type." So it isn't possible to create a PPI on a column with the PERIOD data type.
Enthusiast

Re: Exploring Teradata 13's PERIOD Functions

Very useful article !!

I ran into a problem with partitioning, please read below :

Created a table as below :

CREATE multiSET TABLE t12 (a INTEGER,b PERIOD (date) FORMAT 'YYYY-MM-DD' NULL)

PRIMARY INDEX(a)

PARTITION BY CAST((BEGIN(b)) AS INTEGER);

when i am trying to insert the data using below statement, i get an error saying "INSERT Failed 5728: Partitioaning violation for table t12".

insert into t12 values(1, Period ( DATE '2011-01-26', DATE '2011-10-21'))

I don't get the insert error when i don't partition the period column. but i need the partitioning as i cannot index a Period data type column.

Do you know how to resolve the insert error ?

Enthusiast

Re: Exploring Teradata 13's PERIOD Functions

Essentially what you're doing with this example is trying to create a PPI using a PERIOD data type even though you're only taking the beginning boundary from it, which cannot be done. To work around this limitation you can add a date column to your example table that holds the beginning boundary of your PERIOD and then partition this new column.

Here's an example:
CREATE MULTISET TABLE t12 (a INTEGER,b PERIOD (date) FORMAT 'YYYY-MM-DD' NULL,c date FORMAT 'YYYY-MM-DD' NULL)
PRIMARY INDEX(a)
PARTITION BY RANGE_N(c BETWEEN DATE '2011-01-01' AND DATE '2012-01-31' EACH INTERVAL '1' MONTH );

insert into t12
values(1, Period(DATE '2011-01-26', DATE '2011-10-21'), BEGIN(Period(DATE '2011-01-26', DATE '2011-10-21')));