Help to find the time difference between the lines Post: 302352856

Sponsored Content

Top Forums Shell Programming and Scripting Help to find the time difference between the lines Post 302352856 by durden_tyler on Sunday 13th of September 2009 11:38:48 PM

09-14-2009

Registered User

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

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. 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. 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

3. 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

4. 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...

5. 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...

6. 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...

7. 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...

8. 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...

9. 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

10. 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. ...

LEARN ABOUT DEBIAN

net::proxy::connector::dual

Net::Proxy::Connector::dual(3pm)			User Contributed Perl Documentation			  Net::Proxy::Connector::dual(3pm)

NAME

       Net::Proxy::Connector::dual - Y-shaped Net::Proxy connector

DESCRIPTION

       "Net::Proxy::Connecter::dual" is a "Net::Proxy::Connector" that can forward the connection to two distinct services, based on the client
       connection, before any data is exchanged.

CONNECTOR OPTIONS

       This connector can only work as an "in" connector.

       The "server_first" and "client_first" options are required: they are hashrefs containing the options necessary to create two "out"
       "Net::Proxy::Connector" objects that will be used to connect to the requested service.

       The "Net::Proxy::Connector::dual" object decides between the two services by waiting during a short timeout. If the client sends some data
       directly, then it is connected via the "client_first" connector. Otherwise, at the end of the timeout, it is connected via the
       "server_first" connector.

       o   host

	   The hostname on which the connector will listen for client connections.  Default is "localhost".

       o   port

	   The port on which the connector will listen for client connections.

       o   server_first

	   Typically an "out" connector to a SSH server or any service that sends a banner line.

       o   client_first

	   Typically an "out" connectrot to a web server or SSL server.

       o   timeout

	   The timeout in seconds (can be decimal) to make a decision.	Default is 1 second.

AUTHOR

       Philippe 'BooK' Bruhat, "<book@cpan.org>".

ACKNOWLEDGMENTS

       This module is based on a script named sslh, which I wrote with Frederic Ple "<sslh@wattoo.org>" (who had the original insight about the
       fact that not all servers speak first on the wire).

       Frederic wrote a C program, while I wrote a Perl script (based on my experience with connect-tunnel).

       Now that "Net::Proxy" is available, I've ported the Perl script to use it.

COPYRIGHT

       Copyright 2006 Philippe 'BooK' Bruhat, All Rights Reserved.

LICENSE

       This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.10.1							    2009-10-18					  Net::Proxy::Connector::dual(3pm)