FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE printer (
  printerID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  Name VARCHAR2(100) 
);
-- INIT history
CREATE TABLE printer_history (
  ProductID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  printer_name VARCHAR2(100), 
  action_name VARCHAR2(100),
  action_date TIMESTAMP  
);
INSERT INTO printer(Name) VALUES ('Xerox1');
INSERT INTO printer(Name) VALUES ('Xerox2');
INSERT INTO printer(Name) VALUES ('Xerox3');

INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox1','INSTALLED'  , TO_TIMESTAMP('2014-07-02 06:44:00.743000000', 'YYYY-MM-DD HH24:MI:SS.FF') );
INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox1','ISSUE_FOUND', TO_TIMESTAMP('2014-07-02 06:45:00.744000000', 'YYYY-MM-DD HH24:MI:SS.FF') );
INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox1','FIXED'      , TO_TIMESTAMP('2014-07-15 8:33:00.655000000', 'YYYY-MM-DD HH24:MI:SS.FF') );

INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox2','INSTALLED'  , TO_TIMESTAMP('2015-08-01 05:11:00.722000000', 'YYYY-MM-DD HH24:MI:SS.FF') );
INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox2','ISSUE_FOUND', TO_TIMESTAMP('2015-09-02 03:44:00.732000000', 'YYYY-MM-DD HH24:MI:SS.FF') );
INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox2','FIXED'      , TO_TIMESTAMP('2015-09-14 07:55:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF') );

INSERT INTO printer_history(printer_name,action_name,action_date) VALUES ('Xerox2','INSTALLED'  , TO_TIMESTAMP('2018-08-01 05:11:00.752000000', 'YYYY-MM-DD HH24:MI:SS.FF') );

-- QUERY database

select  (SELECT action_date from
	printer_history where printer_name='Xerox1' and action_name='FIXED')
    -
(SELECT action_date from
	printer_history where printer_name='Xerox1' and action_name='ISSUE_FOUND') diff 
    from dual;

DIFF
+000000013 01:47:59.911000