Visit Our UNIX and Linux User Community


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 10:47 AM..
# 2  
Old 09-11-2009
UNIX Shell Script Tutorials & Reference
look at the s_interval function.
# 3  
Old 09-13-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

Previous Thread | Next Thread
Test Your Knowledge in Computers #811
Difficulty: Easy
RGBA stands for red green blue alpha.
True or False?

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

Featured Tech Videos