substr(cast(end_time-start_time as varchar(17)),3,8) as elapsed, - Replace this part with: end_time-start_time as elapsed, - so you can see what part of the output you need to cut.
Regards, Maurice van 't Loo TSM Freelancer 2010/11/13 Robert Ouzen <[email protected]>: > Hi > > I try to figure how to calculate the elapse time of my backups in version6 , > my old script V5 give me the correct output in the V6 the format is incorrect. > > The script below: > > Select substr( entity,1,15) as nodename, - > date(start_time) as "Date (D/M/Y)", - > time(start_time) as time, - > substr(cast(end_time-start_time as varchar(17)),3,8) as elapsed, - > cast(sum(affected) as varchar(10)) as "Num Obj", - > cast(sum(failed) as varchar(10)) as "Num Obj Failed", - > case - > when sum(bytes)>1073741824 then cast(sum(bytes)/1073741824 as varchar(10)) > || ' Gb' - > when sum(bytes)>1048576 then cast(sum(bytes)/1048576 as varchar(10)) || ' > Mb' - > when sum(bytes)>1024 then cast(sum(bytes)/1024 as varchar(10)) || ' Kb' - > else cast(sum(bytes) as varchar(10)) - > end as "Bytes" - > from summary - > where activity=upper('$1') and - > start_time>=timestamp(current_date-$2 day,'16:00:00') and - > start_time<=timestamp(current_date,'09:00:00') and - > successful='YES' - > and entity=upper('$3') - > group by entity,start_time,end_time > > V5 output: > > NODENAME Date (D/M/Y) TIME ELAPSED > Num Obj Num Obj Failed Bytes > ------------------ ------------ -------- > ---------- ------- ---------- -------- > ------------- > IBROWSE2 13.11.2010 02.00.12 00:06:18 > 3755 0 1 Gb > > V6 output: > > NODENAME Date (D/M/Y) TIME ELAPSED > Num Obj Num Obj Failed Bytes > ---------------- ------------- --------- > --------- ----------- --------------- > -------------- > SYMSRV01 2010-11-12 20:33:03 9.000000 > 61 1 7 Mb > > Regards > > Robert >
