CODEWITHSUNDEEP
oracle-databasedate
Paul Duer
Paul Duer

Reputation: 1120

How do I convert a decimal representation of Days into the number of hours and minutes in Oracle SQL?

I have an Oracle query I am working on and one of the columns returns the number of days represented in decimal where 1 day = 1.0

I have numbers like this:

0.004722222222222222222222222222222222222223
3.12383101851851851851851851851851851851
0.000856481481481481481481481481481481481479
0.002592592592592592592592592592592592592593
0.001041666666666666666666666666666666666667

and I want the number of hours and minutes each of those represent. Like 16 Hours 5 Minutes or 8 hours 59 Minutes.

How do I do that? Preferably as a single column return as the string.

Upvotes: 0

Views: 956

Answers (2)

MT0
MT0

Reputation: 168671

Oracle Setup:

CREATE TABLE table_name ( value ) AS
SELECT 0.004722222222222222222222222222222222222223 FROM DUAL UNION ALL
SELECT 3.12383101851851851851851851851851851851 FROM DUAL UNION ALL
SELECT 0.000856481481481481481481481481481481481479 FROM DUAL UNION ALL
SELECT 0.002592592592592592592592592592592592592593 FROM DUAL UNION ALL
SELECT 0.001041666666666666666666666666666666666667 FROM DUAL;

Query:

SELECT NUMTODSINTERVAL( value, 'DAY' ) FROM table_name;

Output:

NUMTODSINTERVAL(VALUE,'DAY')
----------------------------
0 0:6:48.0                   
3 2:58:19.0                  
0 0:1:14.0                   
0 0:3:44.0                   
0 0:1:30.0

Query 2:

SELECT TRIM( BOTH FROM
         CASE WHEN dd <> 0 THEN dd || ' Days' END
         || CASE WHEN hh <> 0 THEN ' ' || hh || ' Hours' END
         || CASE WHEN mm <> 0 THEN ' ' || mm || ' Minutes' END
         || CASE WHEN ss <> 0 THEN ' ' || ss || ' Seconds' END
       ) AS period
FROM   (
  SELECT EXTRACT( DAY    FROM period ) AS dd,
         EXTRACT( HOUR   FROM period ) AS hh,
         EXTRACT( MINUTE FROM period ) AS mm,
         EXTRACT( SECOND FROM period ) AS ss
  FROM   (
    SELECT NUMTODSINTERVAL( value, 'DAY' ) AS period
    FROM   table_name
  )
);

Output:

PERIOD
------------------------------------
6 Minutes 48 Seconds
3 Days 2 Hours 58 Minutes 19 Seconds
1 Minutes 14 Seconds
3 Minutes 44 Seconds
1 Minutes 30 Seconds

Upvotes: 1

Husqvik
Husqvik

Reputation: 5809

You can convert the decimal into interval and then extract the elements you want:

WITH data (value) AS (
    SELECT 0.004722222222222222222222222222222222222223 FROM DUAL UNION ALL
    SELECT 3.12383101851851851851851851851851851851     FROM DUAL UNION ALL
    SELECT 0.000856481481481481481481481481481481481479 FROM DUAL UNION ALL
    SELECT 0.002592592592592592592592592592592592592593 FROM DUAL UNION ALL
    SELECT 0.001041666666666666666666666666666666666667 FROM DUAL),
intervals (value) AS (
    SELECT numtodsinterval(value, 'DAY') interval FROM data)
SELECT
    extract(DAY FROM value) day,
    extract(HOUR FROM value) hour,
    extract(MINUTE FROM value) minute,
    extract(SECOND FROM value) second
FROM
    intervals

Upvotes: 1

Related Questions