Re: [ADSM-L] Elapse time format incorrect in V6

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... AT univ.haifa.ac DOT 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