CODEWITHSUNDEEP
oracle-database
Vijay Kumar
Vijay Kumar

Reputation: 5

Time Difference in Oracle SQL

HI I have time as numeric in format HHMMSS

| RowA || RowB |
|:----:||:----:|
|21788 ||31788 |
|2278||13478|
|278||3033|
|231740||235210|

I want to calculate difference between the 2 times in hours an minutes. I tried select RowB-RowA from Dual; But if the time has passed next hour and less than minutes in Row A, I get in correct results.

Upvotes: 0

Views: 49

Answers (1)

MT0
MT0

Reputation: 167774

You can covert the values to DATEs (with the default year-day components) and then subtract to get an INTERVAL DAY TO SECOND data type for the difference:

SELECT ( TO_DATE(LPAD(rowb,6,'0'), 'HH24MISS')
       - TO_DATE(LPAD(rowa,6,'0'), 'HH24MISS')
       ) DAY TO SECOND AS diff
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( RowA, RowB ) AS
SELECT  21758,  31738 FROM DUAL UNION ALL
SELECT   2248,  13448 FROM DUAL UNION ALL
SELECT    238,   3033 FROM DUAL UNION ALL
SELECT 231740, 235210 FROM DUAL;

Outputs:

DIFF
+00 00:59:40.000000
+00 01:12:00.000000
+00 00:27:55.000000
+00 00:34:30.000000

fiddle

If you just want the hours and minutes components then take the sub-string with only that part:

SELECT SUBSTR(
         TO_CHAR(
           ( TO_DATE(LPAD(rowb,6,'0'), 'HH24MISS')
           - TO_DATE(LPAD(rowa,6,'0'), 'HH24MISS')
           ) DAY TO SECOND
         ),
         5,
         5
       ) AS diff
FROM   table_name;

fiddle

Upvotes: 1

Related Questions