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))
);
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')
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 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
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')
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')
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')
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')
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
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
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
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.