Oracle WMSYS Functions and Operators
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose A group of related operators of great value in date comparison
Data Types CREATE OR REPLACE TYPE wm_period AS OBJECT (
validfrom TIMESTAMP WITH TIME ZONE,
validtill TIMESTAMP WITH TIME ZONE);
Dependencies
OWM_VT_PKG    
Security Model conn / as sysdba

ALTER USER wmsys ACCOUNT UNLOCK IDENTIFIED BY wmsys;

For other schemas to have access to WMSYS objects explicit EXECUTE permission on the object should be granted.
 
WM_CONTAINS
Checks if the first period contains the second period WM_CONTAINS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_CONTAINS';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_CONTAINS') FROM dual;

set serveroutput on

DECLARE
 retval INTEGER;
 p1b    DATE := TO_DATE('01-JAN-2013');
 p1e    DATE := TO_DATE('05-JAN-2013');
 p2b    DATE := TO_DATE('01-JAN-2013');
 p2e    DATE := TO_DATE('03-JAN-2013');
BEGIN
  SELECT WM_CONTAINS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);

  p1b := TO_DATE('02-JAN-2013');
  p1e := TO_DATE('06-JAN-2013');

  SELECT WM_CONTAINS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval); 
END;
/
 
WM_EQUALS
Checks if two periods are equal (that is, their start and end times are the same) WM_EQUALS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_EQUALS';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_EQUALS') FROM dual;

set serveroutput on

DECLARE
 retval INTEGER;
 p1b    DATE := TO_DATE('01-JAN-2013');
 p1e    DATE := TO_DATE('03-JAN-2013');
 p2b    DATE := TO_DATE('01-JAN-2013');
 p2e    DATE := TO_DATE('03-JAN-2013');
BEGIN
  SELECT WM_EQUALS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);

  p1b := TO_DATE('01-JAN-2013');
  p1e := TO_DATE('04-JAN-2013');

  SELECT WM_EQUALS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval); 
END;
/
 
WM_GREATERTHAN
Checks if the start of the first period is greater than (that is, later than) the end of the second period WM_GREATERTHAN(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_GREATERTHAN';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_GREATERTHAN') FROM dual;

set serveroutput on

DECLARE
 retval INTEGER;
 p1b    DATE := TO_DATE('01-FEB-2013');
 p1e    DATE := TO_DATE('03-FEB-2013');
 p2b    DATE := TO_DATE('01-JAN-2013');
 p2e    DATE := TO_DATE('31-JAN-2013');
BEGIN
  SELECT WM_GREATERTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);

  p2b := TO_DATE('02-FEB-2013');
  p2e := TO_DATE('31-DEC-2013');

  SELECT WM_GREATERTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval); 
END;
/
 
WM_INTERSECTION
Returns the intersection of the two periods, that is, the time range common to both periods WM_INTERSECTION(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_INTERSECTION';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_INTERSECTION') FROM dual;

set serveroutput on

DECLARE
 retval WM_PERIOD;
 p1b    DATE := TO_DATE('01-JAN-2013');
 p1e    DATE := TO_DATE('10-JAN-2013');
 p2b    DATE := TO_DATE('08-JAN-2013');
 p2e    DATE := TO_DATE('12-JAN-2013');
BEGIN
  SELECT WM_INTERSECTION(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval.validfrom);
  dbms_output.put_line(retval.validtill);
END;
/
 
WM_LDIFF
Returns the difference between the two periods on the left (that is, earlier in time) WM_LDIFF(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_LDIFF';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_LDIFF') FROM dual;

set serveroutput on

DECLARE
 retval WM_PERIOD;
 p1b    DATE := TO_DATE('01-NOV-2013');
 p1e    DATE := TO_DATE('31-DEC-2013');
 p2b    DATE := TO_DATE('29-NOV-2013');
 p2e    DATE := TO_DATE('01-DEC-2013');
BEGIN
  SELECT WM_LDIFF(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval.validfrom);
  dbms_output.put_line(retval.validtill);
END;
/
 
WM_LESSTHAN
Checks if the end of the first period is less than (that is, earlier than) the start of the second period WM_LESSTHAN(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_LESSTHAN';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_LESSTHAN') FROM dual;

set serveroutput on

DECLARE
 retval INTEGER;
 p1b    DATE := TO_DATE('01-JAN-2013');
 p1e    DATE := TO_DATE('02-JAN-2013');
 p2b    DATE := TO_DATE('03-JAN-2013');
 p2e    DATE := TO_DATE('16-JAN-2013');
BEGIN
  SELECT WM_LESSTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);

  p1b := TO_DATE('04-JAN-2013');
  p1e := TO_DATE('10-JAN-2013');

  SELECT WM_LESSTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval); 
END;
/
 
WM_MEETS
Checks if the end of the first period is the start of the second period WM_MEETS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_MEETS';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_OVERLAPS') FROM dual;

set serveroutput on

DECLARE
 retval INTEGER;
 p1b    DATE := TO_DATE('01-JAN-2013');
 p1e    DATE := TO_DATE('03-JAN-2013');
 p2b    DATE := TO_DATE('03-JAN-2013');
 p2e    DATE := TO_DATE('06-JAN-2013');
BEGIN
  SELECT WM_MEETS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);

  p1b := TO_DATE('01-JAN-2013');
  p1e := TO_DATE('04-JAN-2013');

  SELECT WM_MEETS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval); 
END;
/
 
WM_OVERLAPS
Checks if two periods overlap WM_OVERLAPS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN NUMBER;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_OVERLAPS';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_OVERLAPS') FROM dual;

set serveroutput on

DECLARE
 retval INTEGER;
 p1b    DATE := TO_DATE('01-JAN-2013');
 p1e    DATE := TO_DATE('31-JAN-2013');
 p2b    DATE := TO_DATE('31-DEC-2005');
 p2e    DATE := TO_DATE('02-JAN-2013');
BEGIN
  SELECT WM_OVERLAPS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);

  p1b := TO_DATE('03-JAN-2013');
  p1e := TO_DATE('31-JAN-2013);

  SELECT WM_OVERLAPS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval);
END;
/
 
WM_RDIFF
Returns the difference between the two periods on the right (that is, later in time) WM_RDIFF(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys

SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_RDIFF';

set long 1000000

SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_RDIFF') FROM dual;

set serveroutput on

DECLARE
 retval WM_PERIOD;
 p1b    DATE := TO_DATE('01-JAN-2010');
 p1e    DATE := TO_DATE('31-DEC-2010');
 p2b    DATE := TO_DATE('12-JAN-2009');
 p2e    DATE := TO_DATE('16-JAN-2010');
BEGIN
  SELECT WM_RDIFF(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
  INTO retval
  FROM dual;

  dbms_output.put_line(retval.validfrom);
  dbms_output.put_line(retval.validtill);
END;
/

Related Topics
Analytic Functions
Built-in Functions
Cast
Character Functions
Collection Functions
Conversion Functions
Miscellaneous Functions
Numeric Functions
OLAP Functions
User Defined Operators
String Functions
Timestamp

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved