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

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 Replies)
Discussion started by: satyakam
2 Replies

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 (1 Reply)
Discussion started by: bandlan9
1 Replies

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 (3 Replies)
Discussion started by: robinbannis
3 Replies

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... (6 Replies)
Discussion started by: vilibit
6 Replies

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... (1 Reply)
Discussion started by: wnaguib
1 Replies

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... (23 Replies)
Discussion started by: wnaguib
23 Replies

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... (1 Reply)
Discussion started by: cele_82
1 Replies

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... (3 Replies)
Discussion started by: Srinivas Gadi
3 Replies

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 (5 Replies)
Discussion started by: vivekn
5 Replies

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. ... (3 Replies)
Discussion started by: Siva SQL
3 Replies
DATETIME.DIFF(3)							 1							  DATETIME.DIFF(3)

DateTime::diff - Returns the difference between two DateTime objects

       Object oriented style

SYNOPSIS
public DateInterval DateTime::diff (DateTimeInterface $datetime2, [bool $absolute = false]) DESCRIPTION
DateInterval DateTimeImmutable::diff (DateTimeInterface $datetime2, [bool $absolute = false]) DateInterval DateTimeInterface::diff (Date- TimeInterface $datetime2, [bool $absolute = false]) Procedural style DateInterval date_diff (DateTimeInterface $datetime1, DateTimeInterface $datetime2, [bool $absolute = false]) Returns the difference between two DateTimeInterface objects. PARAMETERS
o $datetime - The date to compare to. o $absolute - Should the interval be forced to be positive? RETURN VALUES
The DateInterval object representing the difference between the two dates or FALSE on failure. EXAMPLES
Example #1 DateTime.diff(3) example Object oriented style <?php $datetime1 = new DateTime('2009-10-11'); $datetime2 = new DateTime('2009-10-13'); $interval = $datetime1->diff($datetime2); echo $interval->format('%R%a days'); ?> Procedural style <?php $datetime1 = date_create('2009-10-11'); $datetime2 = date_create('2009-10-13'); $interval = date_diff($datetime1, $datetime2); echo $interval->format('%R%a days'); ?> The above examples will output: +2 days Example #2 DateTime object comparison Note As of PHP 5.2.2, DateTime objects can be compared using comparison operators. <?php $date1 = new DateTime("now"); $date2 = new DateTime("tomorrow"); var_dump($date1 == $date2); var_dump($date1 < $date2); var_dump($date1 > $date2); ?> The above example will output: bool(false) bool(true) bool(false) SEE ALSO
DateInterval.format(3), DateTime.add(3), DateTime.sub(3). PHP Documentation Group DATETIME.DIFF(3)
All times are GMT -4. The time now is 05:07 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy