Try this. One thing might not work is if the backup spans over 24 hours, then I think u will incorrect value. But I think u can add the similar line for date.
select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, - time(start_time) as start,time(end_time) as end, - cast(substr(cast(end_time-start_time as char(20)),3,8) as char(8)) as "Length", - cast(bytes/1024/1024 as decimal(6,0)) as - megabytes,cast(affected as decimal(7,0)) as files, successful from summary where - start_time>=current_timestamp - 1 day and (entity like 'HM%' or entity like - 'PAB%' or entity like 'VMS%' or entity like 'GEOHMKLG%' ) and - activity='BACKUP' order by successful, node_name Sung Y. Lee ----- Forwarded by Sung Y Lee/Austin/IBM on 04/18/2005 09:35 AM ----- "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU> wrote on 04/18/2005 09:01:11 AM: > Hello everyone! > > I have the following command running to see how much data is backed > up/server each day. I was wondering if it is possible to take the start & > end time and figure out the amount of time it took to backup each server? > It seems like it should be rather simple, but yet I am not sure of the > syntax. Thank you in advance! > > ANS8000I Server command: 'select entity as node_name, date(start_time) as > date, cast(activity as varchar(10)) as activity, time(start_time) as start, > time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, > cast(affected as decimal(7,0)) as files, successful from summary where > start_time>=current_timestamp - 1 day and (entity like 'HM%' or entity like > 'PAB%' or entity like 'VMS%' or entity like 'GEOHMKLG%' ) and > activity='BACKUP' order by successful, node_name' > > NODE_NAME DATE ACTIVITY START END > MEGABYTES FILES SUCCESSFUL > ------------------ ---------- ---------- -------- -------- > --------- --------- ------------------ > HMCH1016 2005-04-17 BACKUP 19:06:16 19:45:46 > 813 2326 YES > HMCH1021 2005-04-17 BACKUP 19:11:00 19:23:14 > 856 2277 YES > HMCH1147 2005-04-17 BACKUP 19:00:01 19:31:47 > 558 2162 YES > HMCH1150 2005-04-17 BACKUP 19:02:11 19:14:38 > 750 0 YES > HMCH1160 2005-04-17 BACKUP 19:12:14 19:20:57 > 525 1970 YES > HMCH1161 2005-04-17 BACKUP 19:01:30 19:11:22 > 596 0 YES > HMCH1162 2005-04-17 BACKUP 19:09:19 19:21:42 > 546 2064 YES > HMCH1163 2005-04-17 BACKUP 19:02:24 19:22:12 > 625 1934 YES > HMCH1164 2005-04-17 BACKUP 19:08:26 19:18:17 > 518 1944 YES > HMCH1165 2005-04-17 BACKUP 19:02:31 19:16:55 > 525 1948 YES > HMCH1169 2005-04-17 BACKUP 19:06:35 19:16:42 > 542 2096 YES > HMCH1186 2005-04-17 BACKUP 19:14:04 19:21:10 > 556 2101 YES > HMCH1187 2005-04-17 BACKUP 19:10:09 19:15:46 > 549 2130 YES > HMCH1188 2005-04-17 BACKUP 19:12:20 19:25:01 > 688 2260 YES > HMCH1189 2005-04-17 BACKUP 19:09:21 19:15:55 > 613 2209 YES > HMCH1194 2005-04-17 BACKUP 19:02:48 19:19:11 > 1007 2779 YES > HMPG1109 2005-04-17 BACKUP 19:06:26 19:22:05 > 3474 3153 YES > HMPG1124 2005-04-17 BACKUP 19:11:48 19:23:05 > 640 2319 YES > HMPG1143 2005-04-17 BACKUP 19:00:09 20:45:30 > 312 2129 YES > HMPGCWSI 2005-04-17 BACKUP 19:08:53 19:25:23 > 3436 2475 YES > > ANS8002I Highest return code was 0. > > ******************************** > Joni Moyer > Highmark > Storage Systems > Work:(717)302-6603 > Fax:(717)302-5974 > [EMAIL PROTECTED] > ********************************
