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

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.

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;
/

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;

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

You should check out this site. The TO_TIMESTAMP section could be useful for you!

Syntax:

TO_TIMESTAMP ( string , [ format_mask ] [ 'nlsparam' ] )

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);

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

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.

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.

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

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

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.

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

Try this one. Very simple and easy to use

select to_char(to_date(10000,'sssss'),'hh24:mi:ss') from dual;