Help to find the time difference between the lines

Shell Programming and Scripting
1

Hi guru's,

Am new to shell scripting.
I am getting the below o/p from the oracle database, when I fire a query.

ID JOB_ID ELAPSED_TIME FROM TO

62663 11773 01/06/2009 09:49:13 SA CM
62664 11773 01/06/2009 09:49:18 SA CM
62665 11773 01/06/2009 09:49:19 CM VR
62666 11773 01/06/2009 09:49:23 VR JE
62667 11773 01/06/2009 09:49:25 VR JE
62668 11773 01/06/2009 09:49:29 SA CM

Now I want to calculate the time difference between all the elapsed_time and produce the report as below.

ID JOB_ID ELAPSED_TIME DURATION FROM TO

62663 11773 01/06/2009 09:49:13 SA CM
62664 11773 01/06/2009 09:49:18 00:00:05 SA CM
62665 11773 01/06/2009 09:49:19 00:00:01 CM VR
62666 11773 01/06/2009 09:49:23 00:00:04 VR JE
62667 11773 01/06/2009 09:49:25 00:00:02 VR JE
62668 11773 01/06/2009 10:57:29 01:08:04 SA CM

Can anybody help me to get this done?

2

UNIX Shell Script Tutorials & Reference
look at the s_interval function.

3 
awk '{
if(NR<3) 
print $0
if(NR==3){
print $0
split($4,arr,":")
pre=arr[1]*60*60+arr[2]*60+arr[3]
} 
if(NR>=4){
split($4,arr,":")
cur=arr[1]*60*60+arr[2]*60+arr[3]
clapse=cur-pre
print $1" "$2" "$3" "$4" "clapse" "$5" "$6
pre=cur
}
}'
4

Why not derive the time difference in your Oracle SQL query itself ?

test@XE> 
test@XE> --
test@XE> with t as (
  2    select 62663 id, 11773 job_id, to_date('01/06/2009 09:49:13','mm/dd/yyyy hh24:mi:ss') elapsed_time,
  3          'SA' "from", 'CM' "to" from dual union all
  4    select 62664, 11773, to_date('01/06/2009 09:49:18','mm/dd/yyyy hh24:mi:ss'), 'SA', 'CM' from dual union all
  5    select 62665, 11773, to_date('01/06/2009 09:49:19','mm/dd/yyyy hh24:mi:ss'), 'CM', 'VR' from dual union all
  6    select 62666, 11773, to_date('01/06/2009 09:49:23','mm/dd/yyyy hh24:mi:ss'), 'VR', 'JE' from dual union all
  7    select 62667, 11773, to_date('01/06/2009 09:49:25','mm/dd/yyyy hh24:mi:ss'), 'VR', 'JE' from dual union all
  8    select 62668, 11773, to_date('01/06/2009 10:57:29','mm/dd/yyyy hh24:mi:ss'), 'SA', 'CM' from dual)
  9  --
 10  select id,
 11        job_id,
 12        elapsed_time,
 13        (elapsed_time - lag(elapsed_time) over (order by elapsed_time)) day to second diff,
 14        "from",
 15        "to"
 16    from t;

       ID     JOB_ID  ELAPSED_TIME        DIFF                      from  to
---------- ---------- ------------------- ------------------------- ----- -----
     62663      11773 01/06/2009 09:49:13                           SA    CM
     62664      11773 01/06/2009 09:49:18 +00 00:00:05.000000       SA    CM
     62665      11773 01/06/2009 09:49:19 +00 00:00:01.000000       CM    VR
     62666      11773 01/06/2009 09:49:23 +00 00:00:04.000000       VR    JE
     62667      11773 01/06/2009 09:49:25 +00 00:00:02.000000       VR    JE
     62668      11773 01/06/2009 10:57:29 +00 01:08:04.000000       SA    CM

6 rows selected.

test@XE> 
test@XE>

tyler_durden

5

Hi Durden,

Thanks a lot. It is working fine.

Can you help me once again to suppress the below coloured things?

ID JOB_ID ELAPSED_TIME DIFF from to
----- ------ ------------------ ------------------- ------- ---
62663 11773 01/06/09 09:49:13 SA CM
62664 11773 01/06/09 09:49:18 +00 00:00:05.000000 SA CM
62665 11773 01/06/09 09:49:19 +00 00:00:01.000000 CM VR
62666 11773 01/06/09 09:49:23 +00 00:00:02.000000 VR JE
62667 11773 01/06/09 09:49:25 +00 00:00:04.000000 VR JE
62668 11773 01/06/09 10:57:29 +00 01:08:04.000000 SA CM