CODEWITHSUNDEEP
javasqloracle-database
johndonnon
johndonnon

Reputation: 13

What data type should a time difference column in oracle sql be?

Hey so I have a database where i have a started and a completed timestamp. Now I need a third one for the time difference which I want to set by calculating completed - started in the setter of the variable in my java code so what data type should this column be?

Upvotes: 1

Views: 140

Answers (2)

Thangamani Eraniyan
Thangamani Eraniyan

Reputation: 83

It should be returning number, when you subtracting 2 different date that will return difference in no of days.

Thanks Thangamani Eraniyan

Upvotes: -2

MT0
MT0

Reputation: 167832

In Oracle you want to use intervals to represent a time difference so either an INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH data type.

However, do you really need a third column? You can easily compute it from the other two columns as required (and could even create a virtual column rather than storing the calculated value). Then you would not have issues of updating either the start or end and forgetting to update the difference column and them becoming out of sync.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE your_table (
  start_time  TIMESTAMP,
  end_time    TIMESTAMP,
  difference  GENERATED ALWAYS AS ( end_time - start_time )
);

INSERT INTO your_table ( start_time, end_time )
SELECT SYSTIMESTAMP, SYSTIMESTAMP + INTERVAL '01:02:03' HOUR TO SECOND FROM DUAL;

Query 1:

SELECT *
FROM   your_table

Results:

|              START_TIME |                END_TIME | DIFFERENCE |
|-------------------------|-------------------------|------------|
| 2017-11-23 10:12:06.741 | 2017-11-23 11:14:09.741 |  0 1:2:3.0 |

Upvotes: 3

Related Questions