Extract equivalent HOUR of a Time duration

Question

I have the following date field format: "HH:MM", which represents the duration of an event, and I'd like to extract the equivalent value in HOUR (example: 2h:30min ==> result : 2,5 hour).

Keep the hours as is, and just divide the minutes by 60. isn't that what you want ? — Sudipta Mondal, Jan 08 '19 at 10:20
To format of the field is a varchar, so i can't directly divide the minutes by 60 ! — Rida, Jan 08 '19 at 10:31
You should tell people that you're using an analytical application and NOT some SQL code! OBI analyses do not work by writing SQL code. — Chris, Jan 08 '19 at 16:22

MT0 · Answer 1 · 2019-01-10T13:07:57.263

Ideally, change your table to store the data as an INTERVAL DAY TO SECOND data type and then you can just store INTERVAL '2:30' HOUR TO MINUTE and use date arithmetic to get your answer.

SELECT ( DATE '1970-01-01' + your_interval_value - DATE '1970-01-01' ) * 24
FROM   DUAL;

Since you are storing strings instead of intervals then you can use NUMTODSINTERVAL and string functions to convert the hours and minutes to intervals and then use the same date arithmetic:

db<>fiddle here

Oracle Setup:

 CREATE TABLE table_name ( value ) AS
 SELECT '2h:30min' FROM DUAL UNION ALL
 SELECT '15min' FROM DUAL UNION ALL
 SELECT '3h' FROM DUAL UNION ALL
 SELECT '26 h : 20 min' FROM DUAL UNION ALL
 SELECT '-2h:30min' FROM DUAL UNION ALL
 SELECT '-4h' FROM DUAL UNION ALL
 SELECT '-45min' FROM DUAL UNION ALL
 SELECT '0h:0min' FROM DUAL UNION ALL
 SELECT '0h' FROM DUAL UNION ALL
 SELECT '-0min' FROM DUAL;

Query:

 SELECT value,
        ( DATE '1970-01-01'
        + NUMTODSINTERVAL(
            CASE WHEN INSTR( value, '-' ) > 0 THEN -1 ELSE 1 END
            *
            TO_NUMBER( COALESCE( REGEXP_SUBSTR( value, '(\d*)\s*h', 1, 1, 'i', 1 , '0' ) ),
            'HOUR'
          )
        + NUMTODSINTERVAL(
            CASE WHEN INSTR( value, '-' ) > 0 THEN -1 ELSE 1 END
            *
            TO_NUMBER( COALESCE( REGEXP_SUBSTR( value, '(\d*)\s*min', 1, 1, 'i', 1 ), '0' ) ),
            'MINUTE'
          )
        - DATE '1970-01-01'
        ) * 24 AS hours_difference
 FROM   table_name;

Output:

VALUE         |                          HOURS_DIFFERENCE
:------------ | ----------------------------------------:
2h:30min      |                                       2.5
15min         | .2500000000000000000000000000000000000008
3h            |                                         3
26 h : 20 min | 26.33333333333333333333333333333333333328
-2h:30min     |                                      -2.5
-4h           |                                        -4
-45min        |                                      -.75
0h:0min       |                                         0
0h            |                                         0
-0min         |                                         0

It's a shame that using intervals is so longwinded and clunky — Caius Jard, Jan 10 '19 at 12:23
@CaiusJard Ideally the OP should be storing the data as `INTERVAL '2:30' HOUR TO MINUTE` rather than as a string like `2h:30min` and then you can skip the string conversions and just use date arithmetic. Unfortunately, they aren't using the right data type for the job so parsing the strings is necessary. If the OP is never going to have negative dates then they can strip out that part; similarly, if there is always an hour and a minute component then the `COALESCE` function can be removed... there is a lot of redundancy in my answer to cope with a lack of specification in the question. — MT0, Jan 10 '19 at 13:03

Caius Jard · Answer 2 · 2019-01-08T10:43:42.420

0

An oracle way:

SELECT (TO_DATE('2h:30min', 'HH24"h":MI"min"') - TO_DATE('0:00', 'HH24:MI')) * 24.0 from dual

How it works:

Parses 2h:30min into a date, and generates a date of 01-JAN-19 (all date parsing without a day, month, year becomes the 1st of the current month/year)
Parses 0:00 to a date -> same day, same month, same year as above, but a time of midnight
Subtracts them, resulting in a floating point number that represents the fractions of a day (a day is 1.0, 12 hours is 0.5 etc) between the two datetimes. This works out in this case as approx 0.1041667
We multiply by 24 to turn our 0.1041667 days into hours -> 2.5

edited Jan 08 '19 at 10:43

answered Jan 08 '19 at 10:38

Caius Jard

72,509
5
49
80

2

what if duration is > 24h? ;) – Ted at ORCL.Pro Jan 08 '19 at 11:04
1

Use Gordon's solution.. Or store the data properly in the first place! :) – Caius Jard Jan 08 '19 at 12:02

score 0 · Answer 3 · answered Jan 08 '19 at 11:50

If the value is stored as a string, then you can extract the components and use arithmetic.

One method to extract the components would be to take the first two character and then the 4th and 5th. regexp_substr() provides a bit more flexibility:

select cast(regexp_substr(yyyymm, '[^:]+', 1, 1) as number) + cast(regexp_substr(yyyymm, '[^:]+', 1, 2) as number) / 60
from (select '24:30' as yyyymm from dual) x

Extract equivalent HOUR of a Time duration

3 Answers3