Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Calculate difference in timestamps based on unique column value

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calculate difference in timestamps based on unique column value
# 1  
Old 10-22-2012
Calculate difference in timestamps based on unique column value
Hi Friends,
Require a quick help to write the difference between 2 timestamps based on a unique column value:
Input file:
Code:
08/23/2012 12:36:09,JOB_5340,08/23/2012 12:36:14,JOB_5340
08/23/2012 12:36:22,JOB_5350,08/23/2012 12:36:26,JOB_5350
08/23/2012 13:08:51,JOB_5360,08/23/2012 13:08:58,JOB_5360

Output Required:
Code:
08/23/2012 12:36:14,08/23/2012 12:36:09,00:00:05,JOB_5340
08/23/2012 12:36:26,08/23/2012 12:36:22,00:00:04,JOB_5350
08/23/2012 13:08:51,08/23/2012 13:08:58,00:00:07,JOB_5360

I'm using ksh on AIX machine, and the ksh version is Version M-11/16/88f
Many Thanks
NK
# 2  
Old 10-22-2012
The ksh does not have date arithmetic, but you can decompose the lines, fields to the integers and write some calendar subroutines to do the subtraction. I would use sed to turn the various numbers into variable settings:
Code:
$( echo $f |sed -n '
  s/^\(..\)\/\(..\)\/\(....\) \(..\):\(..\)/yy=\3 mo=\1 dy=\2 hh=\4 mi=\5 ss=\6/p'
 ' )

If you are inclined to reduce them to something like integer UNIX Zulu time, I have found it best to shift the epoch slightly (March 1, 1968) so the first days, month days for March through January, February is whatever is left over. The new epoch has 365x4+1 day quad-years from 3/1/1900 to 2/28/2100 as 2000 was a leap year century.
# 3  
Old 10-23-2012
DGPickett,

Thanks much for your response.

Can you please send me a script that can be used to integrate into my current scripts.

Many Thanks,
Nandha
# 4  
Old 10-23-2012
From googling for shell date arithmetic, I think you can find actual libraries of that stuff. I wrote mine in C for mass production, and posted it here somewhere: tm2tm.c https://www.unix.com/shell-programmin...bsd-linux.html

AIX has ksh93, which has an almost hidden ability to parse and present date/time that has not made it into bash yet, as far as I can see: http://www.scribd.com/doc/17237220/K...and-Arithmetic It's native to AIX: http://publib.boulder.ibm.com/infoce...l_enhanced.htm
Last edited by DGPickett; 10-23-2012 at 10:37 AM..
# 5  
Old 10-23-2012
This may give you something to think about
Code:
$ echo 08/23/2012 12:36:40 12:36:45| tr " " ":" | cut -d":" -f2- | awk -F":" '{tm=($1*3600)+($2*60)+$3; tm2=($4*3600)+($5*60)+$6; tmd=tm2-tm; print tm,tm2,tmd}'
45400 45405 5

I just used a part of your line, and then had awk do some simple math. Now it assumes the times are on the same day. But, this may give you an idea of how to proceed.
Last edited by joeyg; 10-23-2012 at 12:07 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calculate 5th percentile based on another column

I would like to have some help in calculating 5th percentile value of column 2 for each site, the input is like below:site val1 val2 002 10 25.3 002 20 25.3 002 30 25.3 002 40 20 002 50 20 002 60 20 002 70 20 002 80 30 002 90 30 002 100 30 002 120 30 003 20 30.3 003 20 30.3 003 30 20... (2 Replies)
Discussion started by: wuhuai
2 Replies

2. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

3. Shell Programming and Scripting

Finding difference in 2 different timestamps

Legends, I have a requirement to run the script exactly after one hour of completion of dependent script. Eg: Script B should run after one hour on the completion of Script A. I got the time stamps using following variables. these scripts runs in autosys > DATE=`date +%H:%M` >... (4 Replies)
Discussion started by: sdosanjh
4 Replies

4. Shell Programming and Scripting

Calculate the average of a column based on the value of another column

Hi, I would like to calculate the average of column 'y' based on the value of column 'pos'. For example, here is file1 id pos y c 11 1 220 aa 11 4333 207 f 11 5333 112 ee 11 11116 305 e 11 11117 310 r 11 22228 781 gg 11 ... (2 Replies)
Discussion started by: jackken007
2 Replies

5. Shell Programming and Scripting

Calculate 2nd Column Based on 1st Column

Dear All, I have input file like this. input.txt CE2_12-15 3950.00 589221.0 9849709.0 768.0 CE2_12_2012 CE2_12-15 3949.00 589199.0 9849721.0 768.0 CE2_12_2012 CE2_12-15 3948.00 589178.0 9849734.0 768.0 CE2_12_2012 CE2_12-52 1157.00 ... (3 Replies)
Discussion started by: attila
3 Replies

6. Shell Programming and Scripting

Transpose timestamp based on column values and calculate time difference

Hello Expert, I need to transpose Date-Timestamp based on same column values and calculate time difference. The input file would be as below and required output is mentioned in the bottom INPUT File ======== 08/23/2012 12:36:09 JOB_5340 08/23/2012 12:36:14 JOB_5340 08/23/2012... (2 Replies)
Discussion started by: asnandhakumar
2 Replies

7. Shell Programming and Scripting

Calculate age of a file | calculate time difference

Hello, I'm trying to create a shell script (#!/bin/sh) which should tell me the age of a file in minutes... I have a process, which delivers me all 15 minutes a new file and I want to have a monitoring script, which sends me an email, if the present file is older than 20 minutes. To do... (10 Replies)
Discussion started by: worm
10 Replies

8. Shell Programming and Scripting

Calculate difference between consecutive data points in a column from a file

Hi, I have a file with one column data (sample below) and I am trying to write a shell script to calculate the difference between consecutive data valuse i.e Var = Ni -N(i-1) 0.3141 -3.6595 0.9171 5.2001 3.5331 3.7022 -6.1087 -5.1039 -9.8144 1.6516 -2.725 3.982 7.769 8.88 (5 Replies)
Discussion started by: malandisa
5 Replies

9. Shell Programming and Scripting

Difference between two timestamps Contd..

There was this thread earlier with the same name and the solution provided was excellent. Here is the solution to find diffrenc between two timestamp $ cat timestamp #! /usr/bin/ksh echo enter first time stamp read TIME1 echo enter second time stamp read TIME2 H1=${TIME1%:+()}... (3 Replies)
Discussion started by: Shellslave
3 Replies

10. Solaris

Difference between two timestamps

I'm writting a script to find the difference between two timestamp. One field i get on delivery time of the file like 07:17 AM and other is my SLA time 06:30 AM I need to find the difference between these two time (time exceeded to meet SLA). Need some suggestions. (8 Replies)
Discussion started by: raman1605
8 Replies
Login or Register to Ask a Question