This discussion is archived
4 Replies Latest reply: Jan 17, 2013 9:40 PM by avish16 RSS

Convert seconds to hours and minutes

BobH90024 Newbie
Currently Being Moderated
Hi,

I have a column which stores elapsed time in seconds. I'd like to convert it to hours and minutes.

E.G.

126 seconds = 2:06. I can't seem to find anything with a search.

Thanks in advance.
  • 1. Re: Convert seconds to hours and minutes
    Himanshu Kandpal Pro
    Currently Being Moderated
    Hi,

    please look at this link they have nice example

    http://www.oracle.com/technology/oramag/code/tips2005/060605.html

    thanks
  • 2. Re: Convert seconds to hours and minutes
    666352 Expert
    Currently Being Moderated
    Hi ,
    try this code.
    Regards salim.
    SELECT TO_CHAR (TRUNC (SYSDATE) + NUMTODSINTERVAL (126, 'second'),
                    'hh24:mi:ss'
                   ) hr
      FROM DUAL;
    
    
    HR      
    --------
    00:02:06
    
    
    1 row selected.
  • 3. Convert minutes to hrs
    985628 Newbie
    Currently Being Moderated
    hi

    i have a query which give mins as 33
    but i want it as 00:33
    find the below query

    select (ad.dischargeintimateddatetime-ad.dischargeinitiateddatetime)*24*60
    FROM WARDS.ADMISSIONDETAILS AD


    kindly help anybody on this
  • 4. Re: Convert minutes to hrs
    avish16 Explorer
    Currently Being Moderated
    with t as (select to_date('12/03/2012 10:00:16','mm/dd/yyyy hh24:mi:ss') starttime, to_date('12/03/2012 10:33:39','mm/dd/yyyy hh24:mi:ss') endtime from dual union all
    select to_date('12/24/2012 19:01:57','mm/dd/yyyy hh24:mi:ss'), to_date('12/24/2012 23:57:04','mm/dd/yyyy hh24:mi:ss') from dual)
    select starttime,
    endtime
    , (24 * extract(day from (endtime - starttime) day(9) to second))
    + extract(hour from (endtime - starttime) day(9) to second)
    + ((1/100) * extract(minute from (endtime - starttime) day(9) to second)) as "HOUR.MINUTE"
    from t order by 1,2

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points