CODEWITHSUNDEEP
sqldatedatetimeoracle11g
Mowgli
Mowgli

Reputation: 3512

How to convert Minutes to Hours Minutes and Seconds in SQL?

Currently I am using this query, which gives me duration in minutes.

Part of My query

Select 
   E.xyme

From
    (SELECT Timee as xyme from  
            (select round(avg(tableT.DURATIONMIN),2) as Timee
                FROM ownerName.tableT tableT
                   where tableT.FLAG = 0 )
    )E

Output would be

Xyme
----
125.58

Output Looking for

Xyme
----
2 hours, 5 minutes, 35 seconds

Part solution I know we can use something like below but I am not able to implement.

 floor((sysdate-Timee)*24)
  || ' HOURS ' ||
  mod(floor((sysdate-Timee)*24*60),60)
  || ' MINUTES ' ||
  mod(floor((sysdate-Timee)*24*60*60),60)
  || ' SECS ' Duration

Upvotes: 0

Views: 5219

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can convert the numeric value for the minutes to an interval type using the numtodsinterval() function, and then extract() the elements from that:

select extract (hour from numtodsinterval(timee, 'MINUTE')) || ' hours, '
  || extract (minute from numtodsinterval(timee, 'MINUTE')) || ' minutes, '
  || extract (second from numtodsinterval(timee, 'MINUTE')) || ' seconds' as xyme
from (select 125.58 as timee from dual);

XYME                                   
----------------------------------------
2 hours, 5 minutes, 34.8 seconds

You can round or trunc the seconds value as appropriate; looks like you want round from your sample.

Upvotes: 7

Related Questions