4 Replies Latest reply: Jan 17, 2013 11:40 PM by avish16 RSS

    Convert seconds to hours and minutes

    BobH90024
      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
          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
            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
              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
                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