/*==========================================================================*/
/* Instant and Interval Examples for the Oracle DBMS                        */
/*==========================================================================*/

/*--------------------------------------------------------------------------*/
/* Instants:                                                                */
/*      This example consists of a table containing people's names and      */
/*      their birthdates.  Several queries on the table are implemented to  */
/*      show how instants can be used in the Oracle DBMS.                   */
/*--------------------------------------------------------------------------*/

/* Create a table to contain a list of people's names and their birthdates */
CREATE TABLE Person (Name VARCHAR(20), BirthDate DATE);

/* Insert some sample data into the Person table */
INSERT INTO Person (Name, BirthDate)
	VALUES ('Chris', TO_DATE('1976-09-23', 'YYYY-MM-DD'));
INSERT INTO Person (Name, BirthDate)
	VALUES ('Nathan', TO_DATE('1980-11-01', 'YYYY-MM-DD'));
INSERT INTO Person (Name, BirthDate)
	VALUES ('Patrick', TO_DATE('1983-04-21', 'YYYY-MM-DD'));
INSERT INTO Person (Name, BirthDate)
	VALUES ('Emily', TO_DATE('1987-06-17', 'YYYY-MM-DD'));

/* List everyone in the Person table */
SELECT Name, BirthDate FROM Person;

/* List everyone born between 1-1-80 and 12-31-85 in the Person table */
SELECT Name, BirthDate FROM Person
	WHERE BirthDate BETWEEN TO_DATE('1980-01-01', 'YYYY-MM-DD') 
	AND TO_DATE('1985-12-31', 'YYYY-MM-DD');

/* List everyone born before 9-1-81 in the Person table */
SELECT Name, BirthDate FROM Person
	WHERE BirthDate < TO_DATE('1981-09-01', 'YYYY-MM-DD');

/* Drop the example Person table */
DROP TABLE Person;

/*--------------------------------------------------------------------------*/
/* Intevals:                                                                */
/*      This example consists of a table containing job names and an        */
/*      estimated time of production for each.  Several queries on the      */
/*      table are implemented to show how intervals can be used in the      */
/*      Oracle DBMS.                                                        */
/*--------------------------------------------------------------------------*/

/* Create a table to contain a list of jobs */
/* names and their estimated time of production */
CREATE TABLE Job (Name VARCHAR(20), EstTime NUMBER(12, 5));

/* Insert some sample data into the Job table */
INSERT INTO Job (Name, EstTime)
    VALUES ('Green Widgets',
    TO_NUMBER(SUBSTR('0 14:26:32', 1, LENGTH('0 14:26:32') - 9)) +
    TO_NUMBER(SUBSTR('0 14:26:32', LENGTH('0 14:26:32') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('0 14:26:32', LENGTH('0 14:26:32') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('0 14:26:32', LENGTH('0 14:26:32') - 1, 2)) / 86400);
INSERT INTO Job (Name, EstTime)
    VALUES ('Blue Widgets',
    TO_NUMBER(SUBSTR('0 22:52:06', 1, LENGTH('0 22:52:06') - 9)) +
    TO_NUMBER(SUBSTR('0 22:52:06', LENGTH('0 22:52:06') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('0 22:52:06', LENGTH('0 22:52:06') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('0 22:52:06', LENGTH('0 22:52:06') - 1, 2)) / 86400);
INSERT INTO Job (Name, EstTime)
    VALUES ('Red Widgets',
    TO_NUMBER(SUBSTR('1 04:45:52', 1, LENGTH('1 04:45:52') - 9)) +
    TO_NUMBER(SUBSTR('1 04:45:52', LENGTH('1 04:45:52') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('1 04:45:52', LENGTH('1 04:45:52') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('1 04:45:52', LENGTH('1 04:45:52') - 1, 2)) / 86400);
INSERT INTO Job (Name, EstTime)
    VALUES ('Yellow Widgets',
    TO_NUMBER(SUBSTR('2 23:30:29', 1, LENGTH('2 23:30:29') - 9)) +
    TO_NUMBER(SUBSTR('2 23:30:29', LENGTH('2 23:30:29') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('2 23:30:29', LENGTH('2 23:30:29') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('2 23:30:29', LENGTH('2 23:30:29') - 1, 2)) / 86400);

/* List all jobs in the Job table */
SELECT Name, EstTime FROM Job;

/* List jobs which will take more than 1 day to complete in the Job table */
SELECT Name, EstTime FROM Job WHERE EstTime
    > TO_NUMBER(SUBSTR('1 00:00:00', 1, LENGTH('1 00:00:00') - 9)) +
    TO_NUMBER(SUBSTR('1 00:00:00', LENGTH('1 00:00:00') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('1 00:00:00', LENGTH('1 00:00:00') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('1 00:00:00', LENGTH('1 00:00:00') - 1, 2)) / 86400;

/* List jobs which will take between 6 and */
/* 18 hours to complete in the Job table */
SELECT Name, EstTime FROM Job WHERE EstTime
    BETWEEN TO_NUMBER(SUBSTR('0 06:00:00', 1, LENGTH('0 06:00:00') - 9)) +
    TO_NUMBER(SUBSTR('0 06:00:00', LENGTH('0 06:00:00') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('0 06:00:00', LENGTH('0 06:00:00') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('0 06:00:00', LENGTH('0 06:00:00') - 1, 2)) / 86400
    AND TO_NUMBER(SUBSTR('0 18:00:00', 1, LENGTH('0 18:00:00') - 9)) +
    TO_NUMBER(SUBSTR('0 18:00:00', LENGTH('0 18:00:00') - 7, 2)) / 24 +
    TO_NUMBER(SUBSTR('0 18:00:00', LENGTH('0 18:00:00') - 4, 2)) / 1440 +
    TO_NUMBER(SUBSTR('0 18:00:00', LENGTH('0 18:00:00') - 1, 2)) / 86400;

/* Drop the example Job table */
DROP TABLE Job;

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