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.
1 comment:
I like that, thanks.
Post a Comment