PERIOD: An useful function in Teradata

Let’s start with an example where a table is defined with a column with the type PERIOD.  PERIOD data type in Teradata can hold two dates or two time stamps. To ease your date and time calculation PERIOD can play a big role. The below example will show university students’ enrollment history with sample data. 

We created a table where column 'Enrolled_period' is defined as PERIOD type.

CREATE MULTISET TABLE person_University_period (

  person_id        INTEGER                          NOT NULL,

  University_Name varchar(40)                          NOT NULL,

  Enrolled_period  PERIOD(DATE) FORMAT 'YYYY-MM-DD' NULL

)

PRIMARY INDEX (person_id);

And insert some dummy data in the table:

Insert into TD8245.person_University_period

Values(24568,'Gothenburg University', PERIOD(DATE '2007-08-09',DATE '2009-08-25'));

Insert into TD8245.person_University_period

Values(24569,'Gothenburg University', PERIOD(DATE '2009-07-09',DATE '2011-06-25'));

Data looks like below:

Fig 1 : Student's enrollment data

From the above table we can see Enrolled_period holds start date and end date. Lets see different type of operation you can do on Enrolled_period field.

e.g. you to know how many months or year or hour student is enrolled to the university. To find out that you don't need to do use any formula. PERIOD can find out months or year or day from the column 'enrolled_period'. Below Syntax will find out the number of months the students have studied.

Select INTERVAL(Enrolled_period) MONTH as enrolled_month from person_University_period

Fig 2: Number of months studied by students

At the same way you can calculate interval in day, hour, minutes and seconds. 

From the above 'enrolled_period' column you may need to find out start date, so syntax goes like:

select begin(Enrolled_period) as Enrolled_dt from person_University_period

And to find end date SQL will be:

select END(Enrolled_period) as Enrolled_dt from person_University_period

This period function is helpful when you consider banking operation, e.g. you open a bank account and you should have account opening date and closing date. Generally in the database table we make  two fields to serve the purpose which are Effective date and End date.

Instead of using two fields in the table, we can use one column that can hold both start and end date and calculation on two dates or time stamp become easier.

As an example:

INSERT INTO Account

VALUES (

  21222250,

  'Paul Sandal',

  PERIOD(

    CURRENT_DATE,

    UNTIL_CHANGED

  )

);

Result will look like below:

Account_number Account_Name      Account_Period

21222250        Paul Sandal              ('2015-08-20','9999-12-31')

Fig 3: Date with current and until changed

Here, UNTIL_CHANGED set date as '9999-12-31'. If the account is updated for any reason; then the date '9999-12-31' will be updated with the date which account get updated.