Oracle Convert Seconds to Hours Minutes Seconds

Question

I have a requirement to display user available time in Hours Minutes Seconds format from a given total number of seconds value  Appreciate if you know a ORACLE function to do the same  I m using Oracle   Thank you for your time

User · Answer

If you have a variable containing f e  1 minute in seconds   you can add it to the systimestamp then use to char to select the different time parts from it   select to char systimestamp 60  24 60 60    yyyy mm dd HH24 mi ss   from dual

User · Answer

Convert minutes to hour min sec format  SELECT     TO CHAR TRUNC  MINUTES   60    3600    FM9900                TO CHAR TRUNC MOD  MINUTES   60   3600    60    FM00                TO CHAR MOD  MINUTES   60   60    FM00   AS MIN TO HOUR FROM DUAL

User · Answer

Unfortunately not    However  there s a simple trick if it s going to be less than 24 hours    Oracle assumes that a number added to a date is in days  Convert the number of seconds into days  Add the current day  then use the to date function to take only the parts your interested in  Assuming you have x seconds   select to char sysdate    x     60   60   24       HH24 MI SS     from dual   This won t work if there s more than 24 hours  though you can remove the current data again and get the difference in days  hours  minutes and seconds   If you want something like  51 10 05  i e  51 hours  10 minutes and 5 seconds then you re going to have to use trunc   Once again assuming that you have x seconds      The number of hours is trunc x   60   60  The number of minutes is trunc  x -   trunc x   60   60    60   60      60  The number of seconds is therefore the x - hours   60   60 - minutes   60   Leaving you with   with hrs as     select x  trunc x   60   60  as h     from dual               mins as     select x  h  trunc  x - h   60   60    60  as m     from hrs            select h  m  x -  h   60   60  -  m   60    from mins   I ve set up a SQL Fiddle to demonstrate

User · Answer

Try this one  Very simple and easy to use  select to char to date 10000  sssss    hh24 mi ss   from dual

User · Answer

For greater than 24 hours you can include days with the following query  The returned format is days hh24 mi ss  Query  select trunc trunc sysdate    numtodsinterval 9999999   second    - trunc sysdate            to char trunc sysdate    numtodsinterval 9999999   second     hh24 mi ss   from dual   Output  115 17 46 39

User · Answer

If you re just looking to convert a given number of seconds into HH MI SS format  this should do it  SELECT      TO CHAR TRUNC x 3600   FM9900                 TO CHAR TRUNC MOD x 3600  60   FM00                 TO CHAR MOD x 60   FM00   FROM DUAL   where x is the number of seconds

User · Answer

Assuming your time is called st etime below and stored in seconds  here is what I use  This handles times where the seconds are greater than 86399 seconds  which is 11 59 59 pm   case when st etime   86399 then to char to date st etime - 86400  sssss    HH24 MI SS   else to char to date st etime  sssss    HH24 MI SS   end readable time

User · Answer

The following is Yet Another Way  tm  - still involves a little calculation but provides an example of using EXTRACT to pull the individual fields out of an INTERVAL   DECLARE    SUBTYPE BIG INTERVAL IS INTERVAL DAY 9  TO SECOND     i        BIG INTERVAL    nSeconds NUMBER    86400000     FUNCTION INTERVAL TO HMS STRING inv IN BIG INTERVAL      RETURN VARCHAR2   IS     nHours    NUMBER      nMinutes  NUMBER      nSeconds  NUMBER      strHour format  VARCHAR2 10      09       workInv   INTERVAL DAY 9  TO SECOND 9     BEGIN     nHours    EXTRACT HOUR FROM inv     EXTRACT DAY FROM inv    24       strHour format    TRIM RPAD      LENGTH TRIM TO CHAR ABS nHours       0       9         nMinutes    ABS EXTRACT MINUTE FROM inv        nSeconds    ABS EXTRACT SECOND FROM inv         RETURN TRIM TO CHAR nHours  strHour format                        TRIM TO CHAR nMInutes   09                         TRIM TO CHAR nSeconds   09       END INTERVAL TO HMS STRING   BEGIN   i    NUMTODSINTERVAL nSeconds   SECOND       DBMS OUTPUT PUT LINE  i  fields         INTERVAL TO HMS STRING i    END    The code which extracts the fields  etc  still has to contain a calculation to convert the DAY field to equivalent hours  and is not the prettiest  but wrapped up neatly in a procedure it s not too bad to use   Share and enjoy

User · Answer

create or replace function  seconds hh mi ss   seconds in number       return varchar2 is hours var number      minutes var number      seconds var number      remeinder var number      output var varchar2 32       begin     select seconds - mod seconds 3600  into hours var from dual      select seconds - hours var into remeinder var from dual      select  remeinder var - mod remeinder var 60   into minutes var from dual      select seconds -  hours var minutes var  into seconds var from dual      output var    hours var 3600       minutes var 60       seconds var      return output var       end

User · Answer

The following code is less complex and gives the same result  Note that  X  is the number of seconds to be converted to hours   In Oracle use   SELECT TO CHAR  TRUNC  SYSDATE    NUMTODSINTERVAL  X   second                     hh24 mi ss                   hr   FROM DUAL    In SqlServer use   SELECT CONVERT varchar  DATEADD s  X  0   108

User · Answer

create or replace procedure mili num in number  as yr number  yrsms number  mon number  monsms number  wk number  wksms number  dy number  dysms number  hr number  hrsms number  mn number  mnsms number  sec number  begin  yr    FLOOR num 31556952000   yrsms    mod num  31556952000   mon    FLOOR yrsms 2629746000   monsms    mod num 2629746000   wk    FLOOR monsms  604800000    wksms    mod num 604800000    dy    floor wksms   24 60 60 1000    dysms   mod num 24 60 60 1000   hr    floor  dysms   60 60 1000    hrsms    mod num 60 60 1000   mn    floor  hrsms   60 1000    mnsms    mod num 60 1000   sec    floor  mnsms   1000    dbms output put line   Year    yr    Month    mon    Week    wk    Day    dy    Hour    hr    Min    mn    Sec     sec   end      begin  mili 12345678904234   end

User · Answer

My version  Show Oracle DB uptime in format DDd HHh MMm SSs  select to char trunc    86400 x  60  60  24       d         to char trunc   86400 x  60  60 -24  trunc    86400 x  60  60  24     FM00       h         to char trunc  86400 x  60 -60  trunc   86400 x  60  60     FM00       m         to char trunc 86400 x -60  trunc  86400 x  60     FM00       s   UPTIME   from  select  sysdate - t startup time  x from V INSTANCE t     idea from Date   Time Arithmetic with Oracle 9 10

User · Answer

For the comment on the answer by vogash  I understand that you want something like a time counter  thats because you can have more than 24 hours  For this you can do the following   select to char trunc xxx 3600      to char to date mod xxx  86400   sssss     mi ss   as time from dual    xxx are your number of seconds   The first part accumulate the hours and the second part calculates the remaining minutes and seconds  For example  having 150023 seconds it will give you 41 40 23    But if you always want have hh24 mi ss even if you have more than 86000 seconds  1 day  you can do   select to char to date mod xxx  86400   sssss    hh24 mi ss   as time  from dual    xxx are your number of seconds   For example  having 86402 seconds it will reset the time to 00 00 02

User · Answer

You should check out this site  The TO TIMESTAMP section could be useful for you   Syntax   TO TIMESTAMP   string     format mask       nlsparam

[sql] Oracle Convert Seconds to Hours:Minutes:Seconds

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to plsqldeveloper