# Thread: convert a number in day,hour,minutes and seconds

1. Registered User
Join Date
Jul 2002
Location
Rome
Posts
4

## Unanswered: convert a number in day,hour,minutes and seconds

Hi,
I have to convert a result of an average on a difference between two dates in days, hours, minutes and seconds.
I have wrote a sql statement, It works, but I don't like it:

select users,
-- days
trunc(avg(dat_end-dat_start))||' '||
-- hours
trunc((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)||':'||
-- minutes
-- seconds
from ana_cli
GROUP BY users

I test it on table dual with this:

select trunc(4.5045)||' '||
trunc((4.5045-trunc(4.5045))*24)||':'||
from dual

I hope in a help, but I think that script could be used from others programmer

Ciao,
Alessio Caldarozzi

2. Registered User
Join Date
Jul 2002
Posts
2
two solutions :

1. write and use a function

or

2. use simple sqlcreate or replace
function f_rcu (p_days number) return varchar2 is
l_string varchar2 (50) ;
l_test number ;

begin

l_test := p_days ;
l_string := trunc(l_test) || ' ' ;

l_test := 24 * (l_test - trunc(l_test)) ;
l_string := l_string || trunc(l_test) || ':' ;

l_test := 60 * (l_test - trunc(l_test)) ;
l_string := l_string || trunc(l_test) || ':' ;

l_test := 60 * (l_test - trunc(l_test)) ;
l_string := l_string || round(l_test) ;

return l_string ;

end ;
/

select f_rcu(4.5045) XXX from dual ;

XXX
-----------------
4 12:6:29

select
to_char(trunc(to_date('2000','YYYY'),'YEAR') + 4.5045 - 1
,'DDD HH24:MIS') XXX
from dual;

XXX
---------------
004 12:06:29

rainer

3. Registered User
Join Date
Jun 2013
Posts
1

## simple

SELECT NUMTODSINTERVAL (5555, 'second') AS DHMS FROM DUAL;

DHMS
----------
+00 01:32:35.000000

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•