Hi Maurice
I figure how to get a correct output with the function: timestampdiff
(parameter 4 is for minutes)
sum(timestampdiff( 4, char(end_time-start_time))) as elapsed,
here output:
NODENAME Date (D/M/Y) TIME ELAPSED Examined Obj
Affected Obj Failed Obj Bytes
---------------- ------------- --------- ------------ ---------------------
-------------- ----------- --------------
SYMSRV01 2010-11-14 11:13:57 3 21609
21608 1 16 Mb
I have to figure how to get it in format: hh:mm:ss
Regards
Robert
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 <rou...@univ.haifa.ac.il>:
> 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
