Results 1 to 3 of 3
  1. #1
    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
    lpad(trunc((((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)-(trunc((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)))*60),2,'00')||':'||
    -- seconds
    lpad(trunc((((((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)-(trunc((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)))*60)-(trunc((((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)-(trunc((avg(dat_end-dat_start)-trunc(avg(dat_end-dat_start)))*24)))*60)))*60),2,'00') as days_hours_min_sec
    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)||':'||
    lpad(trunc((((4.5045-trunc(4.5045))*24)-(trunc((4.5045-trunc(4.5045))*24)))*60),2,'00')||':'||
    lpad(trunc((((((4.5045-trunc(4.5045))*24)-(trunc((4.5045-trunc(4.5045))*24)))*60)-(trunc((((4.5045-trunc(4.5045))*24)-(trunc((4.5045-trunc(4.5045))*24)))*60)))*60),2,'00') as days_hours_min_sec
    from dual

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

    Ciao,
    Alessio Caldarozzi

  2. #2
    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. #3
    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
  •