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.

Mike · Accepted Answer · 2012-11-07T22:59:19.023

38

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.

edited Nov 07 '12 at 22:59

answered Jun 12 '12 at 21:04

Mike

1,039
1
12
20

1

This works good. But if the hours > 99 hours then its not working correctly – user1430989 Jun 13 '12 at 15:33
2

See my edit using TO_CHAR instead of LPAD. It should handle up to 9999 hours now. If you need more than that, you can add extra 9's to the format string in the first TO_CHAR function. – Mike Jun 13 '12 at 17:14
This is not coming correctly for below case: SELECT TO_CHAR(TRUNC(76014000/3600),'FM999999900') || 'Hrs ' || TO_CHAR(TRUNC(MOD(76014000,3600)/60),'FM00') || 'Min ' FROM DUAL; – ngrashia Apr 15 '14 at 05:28
@Nisha - running your query gave me 21115Hrs 00Min... seems correct to me. – Mike Apr 16 '14 at 20:35
1

@Mike: Expected answer was 21.115 hours. But the query returned 21115 hours. Just now realized that i was using Milli seconds. The query works right. I will have to convert the milliseconds to seconds and replace it in place of X and it works... Thanks!! – ngrashia Apr 17 '14 at 03:23
Great answer @Mike! Is there any way to show the miliseconds aswell? – Guilherme Matheus May 27 '20 at 18:07
@GuilhermeMatheus If you mean that your “x” value is in milliseconds, you could use something like: SELECT  TO_CHAR(TRUNC(x/1000/3600),'FM9900') || ':' ||  TO_CHAR(TRUNC(MOD(x/1000,3600)/60),'FM00') || ':' ||  TO_CHAR(MOD(x/1000,60),'FM00') || '.' ||  TO_CHAR(MOD(x,1000),'FM000') FROM DUAL – Mike Sep 14 '20 at 18:20

score 37 · Answer 2 · answered Oct 23 '12 at 12:43

37

Try this one. Very simple and easy to use

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

answered Oct 23 '12 at 12:43

vogash

930
1
10
15

20

I like this better than my answer, but it doesn't work for more than 86399 seconds (1 day). – Mike Dec 04 '13 at 21:16
perfect solution – TroniPM Jun 12 '20 at 12:46

score 8 · Answer 3 · answered Jun 18 '15 at 19:48

8

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

answered Jun 18 '15 at 19:48

What if I want to add 'days'? – wonsuc Aug 28 '20 at 04:58

score 2 · Answer 4 · answered Jun 12 '12 at 20:13

2

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

answered Jun 12 '12 at 20:13

Bjarni Sævarsson

186
2
8

I tried this. This doesn't work good. because, let's think of seconds which are more than 86400 secs(more than 24 hours, a day). Example: 86410 seconds, i.e. 1 day 10 secs, which is equals to : 24:00:10 in hh:mm:ss format.. But, as per above it just gives you: 00:00:10 value, which is wrong... – user1430989 Jun 12 '12 at 20:16
I tried something like this.. SELECT TO_CHAR (TRUNC (SYSDATE) + NUMTODSINTERVAL (82400 , 'second'), 'hh24:mi:ss' ) hr FROM DUAL; – user1430989 Jun 12 '12 at 20:19
My solution does in fact work. You are getting 00:00:10 because you use "TRUNC(SYSDATE)". Replace "TRUNC (SYSDATE)" with "systimestamp" and try again. – Bjarni Sævarsson Jun 12 '12 at 20:23
By the way, sysdate is accurate to second, systimestamp is accurate to millisecond. – Bjarni Sævarsson Jun 12 '12 at 20:30

Scott Baxter · Answer 5 · 2015-02-10T11:06:41.697

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.

Ben · Answer 6 · 2012-06-13T06:53:35.437

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.

Adding one second is a lot easier: `sysdate + interval '1' second` — , Jun 12 '12 at 20:52
@a_horse_with_no_name, fair enough, it's actually longer and doesn't solve the second point but is cleaner looking! — Ben, Jun 12 '12 at 20:58
Perhaps you meant `trunc(sysdate) + (x / (60 * 60 * 24))` in the first snippet above? — Bob Jarvis - Слава Україні, Jun 12 '12 at 22:26

Bob Jarvis - Слава Україні · Answer 7 · 2012-06-13T13:08:03.273

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.

score 1 · Answer 8 · answered Sep 18 '14 at 16:39

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

score 1 · Answer 9 · answered Jun 25 '15 at 13:10

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

score 0 · Answer 10 · answered Feb 29 '16 at 12:35

0

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

answered Feb 29 '16 at 12:35

Andrey Hideki Nakano

31
3

score 0 · Answer 11 · answered Dec 16 '16 at 16:01

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

score 0 · Answer 12 · answered Aug 04 '17 at 09:33

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;

score -1 · Answer 13 · edited Jun 09 '14 at 05:33

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

score -3 · Answer 14 · answered Jun 12 '12 at 20:06

-3

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

Syntax:

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

answered Jun 12 '12 at 20:06

James

1,036
1
8
24

Oracle Convert Seconds to Hours:Minutes:Seconds

14 Answers14

Linked

Related