Is there an alternative to timestamp in oracle to show the time?

I am wondering whether or not there is an alternative to timestamp to display the time as well as date? I don’t really want to use timestamp as it confuses me and I am not sure if I have done it right anyway.

My code can be seen below:

--CREATE SCRIPTS
/*put your create scripts here – your script should not commented out*/

-- this is creating a table called Project that contains 3 variables, the primary key being ProjectID
CREATE TABLE Project
(
    Proj_ID integer,
    Proj_Name varchar(10),
    Proj_Start_Date date,
    primary key (Proj_ID)
);
-- this is creating a table called Bug that has 4 variables, BugID being the primary key
CREATE TABLE Bug
(
    Bug_ID integer,
    Bug_Type varchar(20),
    Bug_Desc varchar(20),
    Bug_Time timestamp(4),
    primary key(Bug_ID)
);

-- this is creating a table called Bug_Project with 2 variables; BugID and ProjectID which combine and make a composite key
CREATE TABLE Bug_Project
(
    Bug_ID integer,
    Proj_ID integer,
    primary key(Bug_ID, Proj_ID),
    foreign key(Bug_ID) references Bug (Bug_ID),
    foreign key(Proj_ID) references  Project (Proj_ID)
);

CREATE TABLE Engineer
(
    Engineer_ID integer,
    Engineer_Name varchar(10),
    Engineer_Type varchar(20),
    primary key (Engineer_ID)
);

CREATE TABLE Fix_Allocation
(
    Engineer_ID integer,
    Bug_ID integer,
    primary key(Engineer_ID, Bug_ID),
    foreign key(Engineer_ID) references Engineer (Engineer_ID),
    foreign key(Bug_ID) references Bug (Bug_ID)
);

CREATE TABLE Test_Allocation
(
    Engineer_ID integer,
    Bug_ID integer,
    primary key(Engineer_ID, Bug_ID),
    foreign key(Engineer_ID) references Engineer (Engineer_ID),
    foreign key(Bug_ID) references Bug (Bug_ID)
);

CREATE TABLE Note
(
    Engineer_ID integer,
    Bug_ID integer,
    Note_author varchar(10),
    Note_contents varchar(20),
    primary key(Engineer_ID, Bug_ID),
    foreign key(Engineer_ID) references Engineer (Engineer_ID),
    foreign key(Bug_ID) references Bug (Bug_ID)
);

COMMIT;
--INSERT SCRIPTS
/*put your insert scripts here – your script should not commented out */

INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (00, 'Project 1', DATE '1900-02-14');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (01, 'Project 2', DATE '1950-12-11');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (02, 'Project 3', DATE '1974-07-01');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (03, 'Project 4', DATE '2000-07-22');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (04, 'Project 5', DATE '2012-03-19');

INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (00, 'BugType1', 'Bug Description', timestamp '1997-01-31 09:26:50.12' );
INSERT INTO Bug VALUES ();
INSERT INTO Bug VALUES ();

INSERT INTO Bug_Project VALUES ();
INSERT INTO Bug_Project VALUES ();
INSERT INTO Bug_Project VALUES ();

INSERT INTO Engineer VALUES (00, "James", "Tester");
INSERT INTO Engineer VALUES (01, "Jeff", "Fixer");
INSERT INTO Engineer VALUES (02, "Jacob", "Fixer");
INSERT INTO Engineer VALUES (03, "John", "Tester");

INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Fix_Allocation VALUES ();

INSERT INTO Test_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();

INSERT INTO Note VALUES ();
INSERT INTO Note VALUES ();
INSERT INTO Note VALUES ();

COMMIT;
--SELECT SCRIPT
/*put your select scripts here (with indication of which query is answered) – your script should not commented out

-- Query 1:  List of all the bugs, and their details.
SELECT * FROM Bug;

-- Query 2: List of all bugs, and their notes.

-- Query 3: List of all bugs, with their notes, and the engineers who have written them; sorted by name of engineer.

-- Query 4: List the bugs and how much cumulative time (in hours) they have taken; ordered by time taken.

-- Query 5: The bug that has taken most time to fix, and the projects it is connected to.

COMMIT;
--DROP SCRIPT
/*put your drop scripts here (in the correct order)– your script should not commented out

DROP TABLE Note;
DROP TABLE Test_Allocation;
DROP TABLE Fix_Allocation;
DROP TABLE Engineer;
DROP TABLE Bug_Project;
DROP TABLE Bug;
DROP TABLE Project;

COMMIT;

here is the outcome of the first bug insert statement, also trying to get rid of all the 0s.

Any help would be greatly appreciated!

Answer

The DATE data type stores time and date. Read Oracle Docs – DATE Data Type for more information.

Advertisement

Answer