Simple oracle function numtodsinterval

It takes in a number, and a string to identify the input units, and spits out dd hh:mm:ss

Options for units are: 'DAY' , 'HOUR', 'MINUTE' and 'SECOND'

In your case, I think you want to change it to:
substr (to_char (numtodsinterval(tot_cyc,   'HOUR')), 9,8)

Jim

numtodsInterval converts a period of time represented in a unit(say 'DAY', 'HOUR', 'MINUTE' or 'SECOND' to a INTERVAL DAY TO SECOND format.

INTERVAL DAY TO SECOND is a descriptive representation of time represented as days, hours, minutes and seconds .

Here you are cutting the DAYS , hours and the minutes part from the expression.

What is the unit of tot_cyc? Is it number of days/hours?

here is the complete query
 cursor tcyc (sdate in varchar2, edate in varchar2) is
      select item_type,station,  sum_use as usage, tst_cnt as test_cnt, ser_cnt as sn_cnt,
             substr (to_char (numtodsinterval(avg_cyc,   'DAY')), 9,8) as avg_cyc,
             substr (to_char (numtodsinterval(stddev_cyc,'DAY')), 9,8) as stddev_cyc,
             substr (to_char (numtodsinterval(min_cyc,   'DAY')), 9,8) as min_cyc,
             substr (to_char (numtodsinterval(max_cyc,   'DAY')), 9,8) as max_cyc,
             substr (to_char (numtodsinterval(tot_cyc,   'DAY')), 9,8) as toT_cyc
        from (
         select distinct item_type,station_name as station,
                round (sum (cyc) over (partition by item_type,station_name)   * 100/ (to_date(edate)+1-to_date(sdate)) /
                decode (station_name,
                'Station1', 2,
                'Station2', 2,
                'Station3', 2,
                'Station4', 2,
                'Station5', 2,
                'Station6', 2,
                'Station7', 2,
                'Station8', 2,
                'Station9', 2,
                 1), 2) as sum_use,
                tst_cnt,ser_cnt,avg_cyc,stddev_cyc,min_cyc,max_cyc,cyc as tot_cyc
         from (                                
            select distinct item_type, station_name,
                    sum (end_time - start_time)    over (partition by item_type,td.station_id) as cyc,
                    count (distinct test_id)       over (partition by item_type,td.station_id) as tst_cnt,
                    count (distinct serial_number) over (partition by item_type,td.station_id) as ser_cnt,
                    avg (end_time - start_time)    over (partition by item_type,td.station_id) as avg_cyc,
                    stddev (end_time - start_time)    over (partition by item_type,td.station_id) as stddev_cyc,
                    min (end_time - start_time)    over (partition by item_type,td.station_id) as min_cyc,
                    max (end_time - start_time)    over (partition by item_type,td.station_id) as max_cyc
              from test.test_data td, test.list_stations s
             where td.station_id=s.station_id
               and creation_date between TO_DATE(sdate) and TO_DATE(edate)+1
         )
       ) order by item_type,station;  
the unit of cyc seems to be time. so what does the result 38 16:28 implies?

See my first post. If input is hours, use:
substr (to_char (numtodsinterval(tot_cyc,   'HOUR')), 9,8)

you mean to say if the input is hrs then its should be hrs if the input is days then days?

Exactly.

then it looks the query is converting it incorrectly and i should get it changed to HOUR instead of DAY

Yes. The HOUR DAY etc tells the function the units of the input value.

ok Jim i checked further in detail such that the cycle time that i calculate
sum (end_time - start_time)    over (partition by item_type,td.station_id) as cyc,
==> this end_time and start_time is stored in date format in the table and the values maybe like this:
24-APR-07 so does this mean the input should be in HOUR OR DAY?
pls help clarify

Since they are stored as dates and not datetimes, the difference will be a number, and the units are days. So, the original SQL should have been correct. Are you sure
"my cycle time comes out to be 38.10625 hrs" that value wasn't days?

You could test this with a simple select with known input values.

help me clarify as i am myself confused.

select distinct item_type, station_name,
sum (end_time - start_time)    over (partition by item_type,td.station_id) as cyc

i get my cycle time as above.the datatype of end_time and start_time is date. when i do (end_time-start-time) the difference will be 10 mins or 1 hr so the diff can be in mins or hrs. but the day remains the same. but when i do sum(end_time-start_time) then what will i get in hrs? or days. pls calrify

so when i am storing as DATE then how do i get the time from this date?
because in one of the pages i convert these dates as
to_char (r1.start_time-1/3, 'DD-MON-YY HH12:MI am')

ok i understand what you mean.
then can you help clarify one more thing. the above big query i run for 31 days. so how can the total cycle time can come as "38 23:28"

it means it took 38 days?
the cycle time is being calculated based on the sum(end_time-start_time). this start time and end time is the date when a test starts and when it ends on a station. so in one day we can many tests on one station. i am not able to relate how can a cycle time come as 38 days in 31 days?
sorry for askign such simple ques

As a test,

SELECT end_time, start_time,  (end_time - start_time),
             (to_char (numtodsinterval(avg_cyc,   'DAY')),
 from a single test

And see what you get for each column in the result row. That might clarify things.