/*==========================================================================*/
/* Period Test Cases for the Oracle DBMS                                    */
/*==========================================================================*/

/*--------------------------------------------------------------------------*/
/* Define some variables for use in the period test cases                   */
/*--------------------------------------------------------------------------*/

DEFINE P1 = "TO_DATE('1997-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE P2 = "TO_DATE('1997-06-06 11:10:09', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE Q1 = "TO_DATE('1997-03-03 06:05:04', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE Q2 = "TO_DATE('1997-12-12 05:06:07', 'YYYY-MM-DD HH24:MI:SS')";
/* DEFINE J = "(TO_NUMBER(SUBSTR('1 23:45:56', 1, LENGTH('1 23:45:56') - 9)) */
/*  + TO_NUMBER(SUBSTR('1 23:45:56', LENGTH('1 23:45:56') - 7, 2)) / 24      */
/*  + TO_NUMBER(SUBSTR('1 23:45:56', LENGTH('1 23:45:56') - 4, 2)) / 1440    */
/*  + TO_NUMBER(SUBSTR('1 23:45:56', LENGTH('1 23:45:56') - 1, 2)) / 86400)";*/
DEFINE J = "TO_NUMBER('1.99023')";
DEFINE A = "TO_DATE('1996-12-20 00:01:02', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE B = "TO_DATE('1998-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS')";

/*--------------------------------------------------------------------------*/

/*** Types: ***/

/* period */
SELECT &P1 AS FIRST, &P2 AS LAST FROM DUAL;

/*** Predicates: ***/

/* p equals q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 = &Q1 AND &P2 = &Q2;

/* p before q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P2 < &Q1;

/* p before-1 q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &Q2 < &P1;

/* p meets q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P2 = &Q1;

/* p meets-1 q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &Q2 = &P1;

/* p overlaps q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 < &Q1 AND &Q1 < &P2;

/* p overlaps-1 q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &Q1 < &P1 AND &P1 < &Q2;

/* p during q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &Q1 < &P1 AND &P2 < &Q2;

/* p during-1 q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 < &Q1 AND &Q2 < &P2;

/* p FIRSTs q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 = &Q1 AND &P2 < &Q2;

/* p FIRSTs-1 q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &Q1 = &P1 AND &Q2 < &P2;

/* p finishes q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &Q1 < &P1 AND &P2 = &Q2;

/* p finishes-1 q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 < &Q1 AND &Q2 = &P2;

/* p OVERLAPS q */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 <= &Q2 AND &Q1 <= &P2;

/* p IS NULL */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &P1 IS NULL;

/*** Datetime Constructors: ***/

/* beginning(p) */
SELECT &P1 AS RESULT FROM DUAL;

/* previous(p) */
SELECT &P1 - 1 AS RESULT FROM DUAL;

/* last(p) */
SELECT &P2 - 1 AS RESULT FROM DUAL;

/* LASTing(p) */
SELECT &P2 AS RESULT FROM DUAL;

/*** Interval Constructors: ***/

/* duration(p) */
SELECT &P2 - &P1 AS RESULT FROM DUAL;

/*** Period Constructors: ***/

/* p + i */
SELECT &P1 + &J AS FIRST, &P2 + &J AS LAST FROM DUAL;

/* i + p */
SELECT &P1 + &J AS FIRST, &P2 + &J AS LAST FROM DUAL;

/* p - i */
SELECT &P1 - &J AS FIRST, &P2 - &J AS LAST FROM DUAL;

/* a extLAST b */
SELECT LEAST(&A, &B) AS FIRST, GREATEST(&A, &B) AS LAST FROM DUAL;

/* p extLAST q */
SELECT LEAST(&P1, &Q1) AS FIRST, GREATEST(&P2, &Q2) AS LAST FROM DUAL;

/* p extLAST a */
SELECT LEAST(&P1, &A) AS FIRST, GREATEST(&P2, &A) AS LAST FROM DUAL;

/* a extLAST p */
SELECT LEAST(&A, &P1) AS FIRST, GREATEST(&A, &P2) AS LAST FROM DUAL;

/* p INTERSECT q */
SELECT GREATEST(&P1, &Q1) AS FIRST, LEAST(&P2, &Q2) AS LAST FROM DUAL;

/* p UNION q */
SELECT LEAST(&P1, &Q1) AS FIRST, GREATEST(&P2, &Q2) AS LAST FROM DUAL;

/*** Other Operators: ***/

/* CAST (a AS PERIOD) */
SELECT &A AS FIRST, &A AS LAST FROM DUAL;

/* CAST (p AS CHAR) */
SELECT '[' || TO_CHAR(&P1, 'YYYY-MM-DD HH24:MI:SS') || ', '
    || TO_CHAR(&P2, 'YYYY-MM-DD HH24:MI:SS') || ')' AS RESULT FROM DUAL;

/*==========================================================================*/
