3.5 Operators for Valid Time Support
Workspace Manager provides relationship checking operators and set operators that accept two time period parameters and that can be used to apply valid time filters in a query.
The relationship checking operators return the integer value 1 if the relationship between the two periods exists, and 0 if the relationship does not exist. The following relationship checking operators for are provided for valid time support:
-
WM_OVERLAPS checks if two periods overlap.
-
WM_CONTAINS checks if the first period contains the second period.
-
WM_MEETS checks if the end of the first period is the start of the second period.
-
WM_EQUALS checks if the two periods are equal (that is, their start and end times are the same).
-
WM_LESSTHAN checks if the end of the first period is less than (that is, earlier than) the start of the second period.
-
WM_GREATERTHAN checks if the start of the first period is greater than (that is, later than) the end of the second period.
The set operators return the period reflecting the relationship between the two periods, or a null value if the two periods do not have the specified relationship. The following set operators for are provided for valid time support:
-
WM_INTERSECTION returns the intersection of the two periods, that is, the time range common to both periods.
-
WM_LDIFF returns the difference between the two periods on the left (that is, earlier in time).
-
WM_RDIFF returns the difference between the two periods on the right (that is, later in time).
You can use the relationship checking operators as alternatives to using the wm_valid.validFrom and wm_valid.validTill attributes of the row. For example, the following two queries, which select data valid on 01-Jan-1991, are equivalent:
SELECT * FROM employees e WHERE WM_CONTAINS (e.wm_valid,
WMSYS.WM_PERIOD(TO_DATE('01-01-1991', 'MM-DD-YYYY'),
TO_DATE('01-02-1991', 'MM-DD-YYYY')) = 1;
SELECT * from employees e
WHERE e.wm_valid.validFrom <= TO_DATE('01-01-1991', 'MM-DD-YYYY')
AND e.wm_valid.validTill > TO_DATE('01-03-1991', 'MM-DD-YYYY');
The rest of this section contains additional information about each operator. The operators are listed in alphabetical order.
- WM_CONTAINS
- WM_EQUALS
- WM_GREATERTHAN
- WM_INTERSECTION
- WM_LDIFF
- WM_LESSTHAN
- WM_MEETS
- WM_OVERLAPS
- WM_RDIFF
Parent topic: Workspace Manager Valid Time Support
3.5.1 WM_CONTAINS
The WM_CONTAINS operator checks if the first period contains the second period. WM_CONTAINS(p1, p2) returns 1 only if the period p1 contains the period p2; otherwise, it returns 0.
For example:
WM_CONTAINS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1988', 'MM-DD-YYYY'))) = 1
WM_CONTAINS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY'))) = 0
Example 3-4 returns all rows in the EMPLOYEES table that were valid on 01-Jan-1995 (that is, where the WM_VALID column value contains the period for 01-Jan-1995).
Example 3-4 WM_CONTAINS Operator
SELECT * FROM employees e
WHERE WM_CONTAINS(e.wm_valid,
wm_period(TO_DATE('01-01-1995', 'MM-DD-YYYY'),
TO_DATE('01-02-1995', 'MM-DD-YYYY'))) = 1;
NAME SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Adams 30000
WM_PERIOD('01-JAN-1990 12:00:00 -04:00', '01-JAN-2005 12:00:00 -04:00')Parent topic: Operators for Valid Time Support
3.5.2 WM_EQUALS
The WM_EQUALS operator checks if the first period is equal to (that is, the same as) the second period. WM_CONTAINS(p1, p2) returns 1 only if the period p1 is equal to the period p2; otherwise, it returns 0.
For example:
WM_EQUALS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY'))) = 1
WM_EQUALS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY'))) = 0
Example 3-5 returns all rows in the EMPLOYEES table that are valid from 01-Jan-1990 until 01-Jan-2005 (that is, where the WM_VALID column value is equal to that period).
Example 3-5 WM_EQUALS Operator
SELECT * FROM employees e
WHERE WM_EQUALS(e.wm_valid,
wm_period(TO_DATE('01-01-1990', 'MM-DD-YYYY'),
TO_DATE('01-01-2005', 'MM-DD-YYYY'))) = 1;
NAME SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Adams 30000
WM_PERIOD('01-JAN-1990 12:00:00 -04:00', '01-JAN-2005 12:00:00 -04:00') Parent topic: Operators for Valid Time Support
3.5.3 WM_GREATERTHAN
The WM_GREATERTHAN operator checks if the first period is greater than (that is, occurs after) the second period. WM_CONTAINS(p1, p2) returns 1 only if the entire period p1 is later than the period p2; otherwise, it returns 0.
For example:
WM_GREATERTHAN(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1970', 'MM-DD-YYYY'),
TO_DATE('01-01-1980', 'MM-DD-YYYY'))) = 1
WM_GREATERTHAN(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1970', 'MM-DD-YYYY'),
TO_DATE('01-01-1981', 'MM-DD-YYYY'))) = 0
Example 3-6 returns all rows in the EMPLOYEES table that are valid only after 01-Jan-2001 (that is, where the WM_VALID column timestamps are both after 01-Jan-2001).
Example 3-6 WM_GREATERTHAN Operator
SELECT * FROM employees e
WHERE WM_GREATERTHAN(e.wm_valid,
wm_period(TO_DATE('01-01-2001', 'MM-DD-YYYY'),
TO_DATE('01-02-2001', 'MM-DD-YYYY'))) = 1;
NAME SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Coleman 50000
WM_PERIOD('01-JAN-2003 12:00:00 -04:00', '31-DEC-9999 12:00:00 -04:00')Parent topic: Operators for Valid Time Support
3.5.4 WM_INTERSECTION
The WM_INTERSECTION operator returns the intersection of the two periods, that is, the period common to both specified periods. WM_INTERSECTION(p1, p2) returns a period that is the intersection of periods p1 and p2.
The following example returns the period between 01-Jan-1985 to 01-Jan-1988:
WM_INTERSECTION(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1988', 'MM-DD-YYYY')))
The following example returns the period between 01-Jan-1985 to 01-Jan-1990:
WM_INTERSECTION(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY')))
The following example returns a null value, because there is no intersection of the periods:
WM_INTERSECTION(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1992', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY')))
Example 3-7 returns, for each row in the EMPLOYEES table, the employee name and the period in which the WM_PERIOD column value intersects the period on 01-Jan-1995.
Example 3-7 WM_INTERSECTION Operator
SELECT e.name, WM_INTERSECTION(e.wm_valid,
wm_period(TO_DATE('01-01-1995', 'MM-DD-YYYY'),
TO_DATE('01-02-1995', 'MM-DD-YYYY')))
FROM employees e;
NAME
----------------
WM_INTERSECTION(E.WM_VALID,WM_PERIOD(TO_DATE('01-01-1995','MM-DD-
--------------------------------------------------------------------------------
Adams
WM_PERIOD('01-JAN-1995 12:00:00 -04:00', '02-JAN-1995 12:00:00 -04:00')
Baxter
Coleman
As you can see in the output of Example 3-7, only Adams has a row that is valid on 01-Jan-1995.
Parent topic: Operators for Valid Time Support
3.5.5 WM_LDIFF
The WM_LDIFF operator returns the difference between the two periods on the left (that is, earlier in time). WM_LDIFF(p1, p2) returns a period that is the difference between periods p1 and p2 on the left.
The following example returns the period between 01-Jan-1980 to 01-Jan-1985:
WM_LDIFF(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1988', 'MM-DD-YYYY')))
The following example returns a null value because p1.validFrom is greater than p2.validFrom:
WM_LDIFF(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1975', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY')))
The following example returns a null value because p2 is completely outside (in this case, later than) p1:
WM_LDIFF(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1992', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY')))
Example 3-8 returns, for each row in the EMPLOYEES table, the employee name and the period in which the WM_PERIOD column value is different on the left from 01-Jan-1995.
Example 3-8 WM_LDIFF Operator
SELECT e.name, WM_LDIFF(e.wm_valid,
wm_period(TO_DATE('01-01-1995', 'MM-DD-YYYY'),
TO_DATE('01-02-1995', 'MM-DD-YYYY')))
FROM employees e;
NAME
----------------
WM_LDIFF(E.WM_VALID,WM_PERIOD(TO_DATE('01-01-1995','MM-DD-YYYY'),
--------------------------------------------------------------------------------
Adams
WM_PERIOD('01-JAN-1990 12:00:00 -04:00', '01-JAN-1995 12:00:00 -04:00')
Baxter
Coleman
As you can see in the output of Example 3-8, only Adams has a row that is valid during the period of difference on the left.
Parent topic: Operators for Valid Time Support
3.5.6 WM_LESSTHAN
The WM_LESSTHAN operator checks if the first period is less than (that is, occurs before) the second period. WM_CONTAINS(p1, p2) returns 1 only if the entire period p1 is less than the period p2; otherwise, it returns 0.
For example:
WM_LESSTHAN(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1991', 'MM-DD-YYYY'),
TO_DATE('01-01-1992', 'MM-DD-YYYY'))) = 1
WM_LESSTHAN(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1989', 'MM-DD-YYYY'),
TO_DATE('01-01-1992', 'MM-DD-YYYY'))) = 0
Example 3-9 returns all rows in the EMPLOYEES table that are valid only before 01-Jan-2010 (that is, where the WM_VALID column timestamps are both before 01-Jan-2001).
Example 3-9 WM_LESSTHAN Operator
SELECT * FROM employees e
WHERE WM_LESSTHAN(e.wm_valid,
wm_period(TO_DATE('01-01-2010', 'MM-DD-YYYY'),
TO_DATE('01-02-2010', 'MM-DD-YYYY'))) = 1;
NAME SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Adams 30000
WM_PERIOD('01-JAN-1990 12:00:00 -04:00', '01-JAN-2005 12:00:00 -04:00')Parent topic: Operators for Valid Time Support
3.5.7 WM_MEETS
The WM_MEETS operator checks if the end of the first period is the start of the second period. WM_MEETS(p1, p2) returns 1 only if p1.validTill = p2.validFrom; otherwise, it returns 0.
For example:
WM_MEETS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1990', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY'))) = 1
WM_MEETS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1992', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY'))) = 0
Example 3-10 returns all rows in the EMPLOYEES table that are valid only if the ending timestamp of the valid date period is the same as the start of the period from 01-Jan-2005 until 01-Jan-2006 (that is, if WM_VALID.validTill is equal to the start of the specified period).
Example 3-10 WM_MEETS Operator
SELECT * FROM employees e
WHERE WM_MEETS(e.wm_valid,
wm_period(TO_DATE('01-01-2005', 'MM-DD-YYYY'),
TO_DATE('01-01-2006', 'MM-DD-YYYY'))) = 1;
NAME SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Adams 30000
WM_PERIOD('01-JAN-1990 12:00:00 -04:00', '01-JAN-2005 12:00:00 -04:00')Parent topic: Operators for Valid Time Support
3.5.8 WM_OVERLAPS
The WM_OVERLAPS operator checks if two periods overlap. WM_OVERLAPS(p1, p2) returns 1 if the periods p1 and p2 overlap; otherwise, it returns 0.
For example:
WM_OVERLAPS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY'))) = 1
WM_OVERLAPS(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1970', 'MM-DD-YYYY'),
TO_DATE('01-01-1980', 'MM-DD-YYYY'))) = 0
Example 3-11 returns all rows in the EMPLOYEES table whose valid date range overlaps the period from 01-Jan-1990 until 01-Jan-2000.
Example 3-11 WM_OVERLAPS Operator
SELECT * FROM employees e
WHERE WM_OVERLAPS(e.wm_valid,
wm_period(TO_DATE('01-01-1990', 'MM-DD-YYYY'),
TO_DATE('01-01-2000', 'MM-DD-YYYY'))) = 1;
NAME SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Adams 30000
WM_PERIOD('01-JAN-1990 12:00:00 -04:00', '01-JAN-2005 12:00:00 -04:00') Parent topic: Operators for Valid Time Support
3.5.9 WM_RDIFF
The WM_RDIFF operator returns the difference between the two periods on the right (that is, later in time). WM_RDIFF(p1, p2) returns a period that is the difference between periods p1 and p2 on the right.
The following example returns the period between 01-Jan-1988 to 01-Jan-1990:
WM_RDIFF(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1985', 'MM-DD-YYYY'),
TO_DATE('01-01-1988', 'MM-DD-YYYY')))
The following example returns a null value because p1.validTill is less than p2.validTill:
WM_RDIFF(
WM_PERIOD(
TO_DATE('01-01-1980', 'MM-DD-YYYY'),
TO_DATE('01-01-1990', 'MM-DD-YYYY')),
WM_PERIOD(
TO_DATE('01-01-1975', 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY')))
Example 3-12 returns, for each row in the EMPLOYEES table, the employee name and the period in which the WM_PERIOD column value is different on the right from 01-Jan-1995.
Example 3-12 WM_RDIFF Operator
SELECT e.name, WM_RDIFF(e.wm_valid,
wm_period(TO_DATE('01-01-1995', 'MM-DD-YYYY'),
TO_DATE('01-02-1995', 'MM-DD-YYYY')))
FROM employees e;
NAME
----------------
WM_RDIFF(E.WM_VALID,WM_PERIOD(TO_DATE('01-01-1995','MM-DD-YYYY'),
--------------------------------------------------------------------------------
Adams
WM_PERIOD('02-JAN-1995 12:00:00 -04:00', '01-JAN-2005 12:00:00 -04:00')
Baxter
Coleman
WM_PERIOD('01-JAN-2003 12:00:00 -04:00', '31-DEC-9999 12:00:00 -04:00')
As you can see in the output of Example 3-12, only Adams and Coleman have rows that are valid during the period of difference on the right.
Parent topic: Operators for Valid Time Support