Help to find the time difference between the lines


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help to find the time difference between the lines
# 1  
Old 09-11-2009
Help to find the time difference between the lines

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?

Last edited by sathik; 09-11-2009 at 11:47 AM..
# 2  
Old 09-11-2009
UNIX Shell Script Tutorials & Reference
look at the s_interval function.
# 3  
Old 09-14-2009
Code:
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  
Old 09-14-2009
Why not derive the time difference in your Oracle SQL query itself ?

Code:
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  
Old 09-14-2009
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

In HP-UX how to find the date time difference ?

Hello, In HP-UX how to find the date time difference ? Start time: 28-APR-2019 21:36:01 End time : 29-APR-2019 00:36:04 ---------------------- Difference is ---------------------- Much appreciate any pointer or view on this. ... (3 Replies)
Discussion started by: Siva SQL
3 Replies

2. Shell Programming and Scripting

Calculate time difference between two lines

i grepped the time stamp in a file as given below now i need to calculate time difference file data: 18:29:10 22:15:50 (5 Replies)
Discussion started by: vivekn
5 Replies

3. Shell Programming and Scripting

Find time difference based on logfile

Hi All, Firstly thank you for the forum members I need to find time difference b'w two rows of timestamp using awk/shell. Here is the logfile: cat business_file start:skdjh:22:06:2010:10:30:22 sdfnskjoeirg wregn'wergnoeirnfqoeitgherg end:siifneworigo:22:06:2010:10:45:34... (3 Replies)
Discussion started by: Srinivas Gadi
3 Replies

4. Shell Programming and Scripting

Check/Parse log file's lines using time difference/timestamp

I was looking at this script which outputs the two lines which differs less than one sec. #!/usr/bin/perl -w use strict; use warnings; use Time::Local; use constant SEC_MILIC => 1000; my $file='infile'; ## Open for reading argument file. open my $fh, "<", $file or die "Cannot... (1 Reply)
Discussion started by: cele_82
1 Replies

5. UNIX for Dummies Questions & Answers

Find time difference

I have a file wich contains time formats and i need to get the time difference TIME1 TIME2 =============== =================== 20120624192555.6Z 20120624204006.5Z which means first date 2012/6/24 19:25:55,second date 2012/6/24 20:40:06 so when i get the time... (23 Replies)
Discussion started by: wnaguib
23 Replies

6. Shell Programming and Scripting

How to find time difference?

I have a file wich contains time formats and i need to get the time difference TIME1 TIME2 ================================== 20120624192555.6Z 20120624204006.5Z which means first date 2012/6/24 19:25:55,second date 2012/6/24 20:40:06 so when i get the time... (1 Reply)
Discussion started by: wnaguib
1 Replies

7. Shell Programming and Scripting

Find time difference between two consecutive lines in same file.

Hello I have a file in following format: IV 08:09:07 NM 08:12:01 IC 08:12:00 MN 08:14:20 NM 08:14:15 I need a script to compare time on each line with previous line and show the inconsecutive line. Ex.: 08:12:00 08:14:15 A better way... (6 Replies)
Discussion started by: vilibit
6 Replies

8. Shell Programming and Scripting

Find Time difference in Unix

Hi, START_TIME :- "10-NOV-2009 00:00:04" STOP_TIME :- "10-NOV-2009 00:05:47" Please help to find difference between these two. Searched for the same topic but did not find an answer for the same time format :( Regards, Robin (3 Replies)
Discussion started by: robinbannis
3 Replies

9. AIX

How to find time difference between 2 timestamps?

HI All, can some one please help me how to fine the difference between two time stamps say a= Nov 10, 2009 9:21:25 AM b= Nov 10, 2009 10:21:25 AM I want to find difference between the a & b I googled and tried with some options but no luck. My OS is AIX (1 Reply)
Discussion started by: bandlan9
1 Replies

10. Shell Programming and Scripting

To find the time difference between two lines of the same log file

Hello Friends, I want to write a script for the following: nlscux62:tibprod> grep "2008 Apr 30 01:" SA_EHV_SPEED_SFC_IN_03-SA_EHV_SPEED_SFC_IN_03-2.log | grep -i post | more 2008 Apr 30 01:01:23:928 GMT +2 SAPAdapter.SA_EHV_SPEED_SFC_IN_03-SA_EHV_SPEED_SFC_IN_03-2 Info AER3-000095 IDOC... (2 Replies)
Discussion started by: satyakam
2 Replies
Login or Register to Ask a Question

Featured Tech Videos