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.

CodePudding user response：

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:

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

CodePudding user response：

You'll first have to convert those values into valid DATE datatype value so that you could subtract them; the difference will be number of days between them. Then multiply the result by 24 (as a number of hours in a day) and 60 (as a number of minutes in an hour); a little bit of subtracting truncated value (which represents minutes) represents seconds:

SQL> with test (time_1, time_2) as
  2    (select 220400, 215040 from dual) -- 22:04:00 - 21:50:40 -> 13 min 20 sec
  3  select
  4    to_date(time_1, 'hh24miss') - to_date(time_2, 'hh24miss') diff_days,
  5    --
  6    trunc((to_date(time_1, 'hh24miss') - to_date(time_2, 'hh24miss')) * 24 * 60) diff_min,
  7    --
  8    round(((to_date(time_1, 'hh24miss') - to_date(time_2, 'hh24miss')) * 24 * 60 -
  9     trunc((to_date(time_1, 'hh24miss') - to_date(time_2, 'hh24miss')) * 24 * 60)) * 60) diff_sec
 10  from test;

 DIFF_DAYS   DIFF_MIN   DIFF_SEC
---------- ---------- ----------
.009259259         13         20

SQL>

Depending on format you want, you can concatenate these values, add hours, or whatever else you want.

As of sample data you posted: minutes format model is mi, not mm (that's for months). Also, if you said that values follow the HHMISS format, what is 278 supposed to be? That just won't work. Garbage in, garbage out.