Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Find the timestamp difference between two different colums in a file

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Find the timestamp difference between two different colums in a file
# 1  
Old 10-10-2014
Lightbulb Find the timestamp difference between two different colums in a file
Sample Input File

Code:
2014/10/09    CDE876172588765        00:09:45    00:10:10    200    200    11.7093
2014/10/09    CDE366134588757        01:04:34    01:04:54    210    210    9.8898
2014/10/09    CDE765172345745        03:05:46    03:06:01    100    100    10.0601
2014/10/09    CDE896983529766        08:01:12    08:01:49    120    120    13.2231
2014/10/09    CDE123422323765        11:23:23    11:24:02    110    110    11.2226
2014/10/09    CDE098672481122        16:45:22    16:45:57    290    290    12.7123
2014/10/09    CDE886488758909        19:56:41    19:57:04    100    100    12.7693
2014/10/09    CDE398075446784        23:07:11    23:07:51    170    170    6.9857

The sample input file has 7 columns from which output file should contain first 4 columns and additional column which is difference of the time stamps in 3rd & 4th column.

Sample Output File

Code:
2014/10/09    CDE876172588765        00:09:45    00:10:10    25 Secs
2014/10/09    CDE366134588757        01:04:34    01:04:54    20 Secs
2014/10/09    CDE765172345745        03:05:46    03:06:01    15 Secs
2014/10/09    CDE896983529766        08:01:12    08:01:49    37 Secs
2014/10/09    CDE123422323765        11:23:23    11:24:02    39 Secs
2014/10/09    CDE098672481122        16:45:22    16:45:57    35 Secs
2014/10/09    CDE886488758909        19:56:41    19:57:04    23 Secs
2014/10/09    CDE398075446784        23:07:11    23:07:51    40 Secs

Last edited by rpm120; 10-10-2014 at 12:52 PM.. Reason: Identation
# 2  
Old 10-10-2014
Code:
$ cat timesub.awk

BEGIN {
        # Where time digits wrap.
        split("24 60 60", T);
        OFS="\t"
}
{
        # A[1]="00", A[2]="10", A[3]="10", etc.
        split($3, A, ":")
        split($4, B, ":")

        # Subtract group by group like longhand arithmetic.
        for(N=3; N>=1; N--)
        {
                B[N] -= A[N]
                M=N

                # If the count goes negative, borrow from the next digit, until we stop needing to borrow or run out of digits.
                while(M>0 && (B[M] < 0))
                {
                        B[M] += T[M]
                        B[M-1] --;
                        M--;
                }
        }

        NF=4    # Strip off extra columns
        # Add another column
        $(NF+1)=sprintf("%02d:%02d:%02d", B[1], B[2], B[3]);
} 1

$ awk -f timesub.awk data

2014/10/09      CDE876172588765 00:09:45        00:10:10        00:00:25
2014/10/09      CDE366134588757 01:04:34        01:04:54        00:00:20
2014/10/09      CDE765172345745 03:05:46        03:06:01        00:00:15
2014/10/09      CDE896983529766 08:01:12        08:01:49        00:00:37
2014/10/09      CDE123422323765 11:23:23        11:24:02        00:00:39
2014/10/09      CDE098672481122 16:45:22        16:45:57        00:00:35
2014/10/09      CDE886488758909 19:56:41        19:57:04        00:00:23
2014/10/09      CDE398075446784 23:07:11        23:07:51        00:00:40

$

Last edited by Corona688; 10-10-2014 at 01:25 PM..
These 2 Users Gave Thanks to Corona688 For This Post:
RavinderSingh13 rpm120
# 3  
Old 10-10-2014
Input
Code:
akshay@nio:/tmp$ cat file
2014/10/09    CDE876172588765        00:09:45    00:10:10    200    200    11.7093
2014/10/09    CDE366134588757        01:04:34    01:04:54    210    210    9.8898
2014/10/09    CDE765172345745        03:05:46    03:06:01    100    100    10.0601
2014/10/09    CDE896983529766        08:01:12    08:01:49    120    120    13.2231
2014/10/09    CDE123422323765        11:23:23    11:24:02    110    110    11.2226
2014/10/09    CDE098672481122        16:45:22    16:45:57    290    290    12.7123
2014/10/09    CDE886488758909        19:56:41    19:57:04    100    100    12.7693
2014/10/09    CDE398075446784        23:07:11    23:07:51    170    170    6.9857

Command
Code:
awk 'function dfor(time, a){ split(time, a, /:/); return 3600*a[1] + 60*a[2] + a[3] }{print $1,$2,$3,$4,dfor($4)-dfor($3)" secs"}' OFS='\t' file

Same in Perl if interested

Code:
perl -wlape 'sub dfor{@x=split(":",shift);return $x[0]*3600+$x[1]*60+$x[2]} $F[4] = dfor($F[3])-dfor($F[2])." secs"; $_ = join("\t",@F[0 .. 4]) ' file


Output
Code:
2014/10/09	CDE876172588765	00:09:45	00:10:10	25 secs
2014/10/09	CDE366134588757	01:04:34	01:04:54	20 secs
2014/10/09	CDE765172345745	03:05:46	03:06:01	15 secs
2014/10/09	CDE896983529766	08:01:12	08:01:49	37 secs
2014/10/09	CDE123422323765	11:23:23	11:24:02	39 secs
2014/10/09	CDE098672481122	16:45:22	16:45:57	35 secs
2014/10/09	CDE886488758909	19:56:41	19:57:04	23 secs
2014/10/09	CDE398075446784	23:07:11	23:07:51	40 secs

These 2 Users Gave Thanks to Akshay Hegde For This Post:
RavinderSingh13 rpm120
# 4  
Old 10-10-2014
A slight change to Akshay's Perl snippet
Code:
perl -wlane 'sub dfor{@x=split(":",shift); $x[0]*3600+$x[1]*60+$x[2]}; $,="\t"; print @F[0..3], dfor($F[3])-dfor($F[2])." secs";' file

Or a big change
Code:
perl -lane '$,="\t"; print @F[0..3], sub {($sH,$sM,$sS,$eH,$eM,$eS) = map{split(":")} @_; (($eH-$sH)*3600+($eM-$sM)*60+($eS-$sS))}->(@F[2,3]) . " secs";' file

Code:
2014/10/09	CDE876172588765	00:09:45	00:10:10	25 secs
2014/10/09	CDE366134588757	01:04:34	01:04:54	20 secs
2014/10/09	CDE765172345745	03:05:46	03:06:01	15 secs
2014/10/09	CDE896983529766	08:01:12	08:01:49	37 secs
2014/10/09	CDE123422323765	11:23:23	11:24:02	39 secs
2014/10/09	CDE098672481122	16:45:22	16:45:57	35 secs
2014/10/09	CDE886488758909	19:56:41	19:57:04	23 secs
2014/10/09	CDE398075446784	23:07:11	23:07:51	40 secs

Last edited by Aia; 10-10-2014 at 06:06 PM..
# 5  
Old 10-10-2014
I am struck by the lack of date for the second time, but I guess if we go negative just add a day.

If you add ':' and '/' to $IFS you can parse this in shell usng 'while read y mo d x h m s h2 m2 s2 y' and calculate in '((...))'.
# 6  
Old 10-12-2014
Quote:
Originally Posted by Corona688
Code:
$ cat timesub.awk

BEGIN {
        # Where time digits wrap.
        split("24 60 60", T);
        OFS="\t"
}
{
        # A[1]="00", A[2]="10", A[3]="10", etc.
        split($3, A, ":")
        split($4, B, ":")

        # Subtract group by group like longhand arithmetic.
        for(N=3; N>=1; N--)
        {
                B[N] -= A[N]
                M=N

                # If the count goes negative, borrow from the next digit, until we stop needing to borrow or run out of digits.
                while(M>0 && (B[M] < 0))
                {
                        B[M] += T[M]
                        B[M-1] --;
                        M--;
                }
        }

        NF=4    # Strip off extra columns
        # Add another column
        $(NF+1)=sprintf("%02d:%02d:%02d", B[1], B[2], B[3]);
} 1

$ awk -f timesub.awk data

2014/10/09      CDE876172588765 00:09:45        00:10:10        00:00:25
2014/10/09      CDE366134588757 01:04:34        01:04:54        00:00:20
2014/10/09      CDE765172345745 03:05:46        03:06:01        00:00:15
2014/10/09      CDE896983529766 08:01:12        08:01:49        00:00:37
2014/10/09      CDE123422323765 11:23:23        11:24:02        00:00:39
2014/10/09      CDE098672481122 16:45:22        16:45:57        00:00:35
2014/10/09      CDE886488758909 19:56:41        19:57:04        00:00:23
2014/10/09      CDE398075446784 23:07:11        23:07:51        00:00:40

$

Corona688 sir fan of you really Smilie, thank you for great code.


Thanks,
R. Singh
These 2 Users Gave Thanks to RavinderSingh13 For This Post:
Corona688 rpm120
# 7  
Old 10-13-2014
Quote:
Originally Posted by Akshay Hegde
Input
Code:
akshay@nio:/tmp$ cat file
2014/10/09    CDE876172588765        00:09:45    00:10:10    200    200    11.7093
2014/10/09    CDE366134588757        01:04:34    01:04:54    210    210    9.8898
2014/10/09    CDE765172345745        03:05:46    03:06:01    100    100    10.0601
2014/10/09    CDE896983529766        08:01:12    08:01:49    120    120    13.2231
2014/10/09    CDE123422323765        11:23:23    11:24:02    110    110    11.2226
2014/10/09    CDE098672481122        16:45:22    16:45:57    290    290    12.7123
2014/10/09    CDE886488758909        19:56:41    19:57:04    100    100    12.7693
2014/10/09    CDE398075446784        23:07:11    23:07:51    170    170    6.9857

Command
Code:
awk 'function dfor(time, a){ split(time, a, /:/); return 3600*a[1] + 60*a[2] + a[3] }{print $1,$2,$3,$4,dfor($4)-dfor($3)" secs"}' OFS='\t' file

Same in Perl if interested

Code:
perl -wlape 'sub dfor{@x=split(":",shift);return $x[0]*3600+$x[1]*60+$x[2]} $F[4] = dfor($F[3])-dfor($F[2])." secs"; $_ = join("\t",@F[0 .. 4]) ' file


Output
Code:
2014/10/09    CDE876172588765    00:09:45    00:10:10    25 secs
2014/10/09    CDE366134588757    01:04:34    01:04:54    20 secs
2014/10/09    CDE765172345745    03:05:46    03:06:01    15 secs
2014/10/09    CDE896983529766    08:01:12    08:01:49    37 secs
2014/10/09    CDE123422323765    11:23:23    11:24:02    39 secs
2014/10/09    CDE098672481122    16:45:22    16:45:57    35 secs
2014/10/09    CDE886488758909    19:56:41    19:57:04    23 secs
2014/10/09    CDE398075446784    23:07:11    23:07:51    40 secs

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk code to find difference in second file which is not present in first file .

Hi All, I want to find difference between two files and output only lines which are not present in second file .I am using awk and I am getting only the first difference but I want to get all the lines which are not present in file2 .Below is the code I am using . Please help to get the desired... (7 Replies)
Discussion started by: srinivasrao
7 Replies

2. UNIX for Beginners Questions & Answers

UNIX utility to find difference in folder, file and contents of file against a base version

Hi, I am trying to find out whether there are any Unix utilities that compares folders, files and contents within the file and provides a comprehensive report. The comparison can be against base version of a folder and file with content. Can you please let me know of such a utility? Thanks,... (6 Replies)
Discussion started by: Sripathi_ks
6 Replies

3. Shell Programming and Scripting

Reading colums from a text file

Hi all, I have a text file that has (4) columns. There are about 300 lines on this file. It is a plain text file. I am looking to write a simple script that will read each line from the file and generate another text file. The file looks something like this: These are the columns: ... (4 Replies)
Discussion started by: adamw
4 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. Shell Programming and Scripting

Rearranging the colums in a tab delimited file

I want to rearrange some of my columns in my dat file; how do i do this using a script Suppose, I have an input file like this: BASENAME STREETTYPE PREFIX SUFFIX HOUSENUMBER BUILDUP ORDER8 ORDER2 ORDER1 ISOCOUNTRYCODE POSTALCODE SILVER LAKE RD NW 1135 NEW BRIGHTON RAMSEY MINNESOTA USA 55112... (4 Replies)
Discussion started by: ramky79
4 Replies

6. Shell Programming and Scripting

Find file size difference in two files using awk

Hi, Could anyone help me to solve this problem? I have two files "f1" and "f2" having 2 fields in each, a) file size and b) file name. The data are almost same in both the files except for few and new additional lines. Now, I have to find out and print the output as, the difference in the... (3 Replies)
Discussion started by: royalibrahim
3 Replies

7. Shell Programming and Scripting

find difference in file column...

Hi All, i have a file that is tab delimited. i need help to find the rows which are having same price based on the site code but some times, there are difference so i need to find only the records which are different in all site code. Dept Sec Barcode 10001 10002 10003 10004... (1 Reply)
Discussion started by: malcomex999
1 Replies

8. UNIX for Advanced & Expert Users

RCS - Find difference between 2 different versions of a file

Hi, I have a c file in my repository. We are using RCS(Revision Control System) to control and manage the versions. I need to find 1. Difference between the current version with a different version 2. Difference between any two different versions of a file. Ex Difference between 1.14 and... (1 Reply)
Discussion started by: kelangovan
1 Replies

9. AIX

Unix shell scripting to find latest file having timestamp embedded...

Hi guys, I have a directory in UNIX having files with the below format, i need to pickup the latest file having recent timestamp embedded on it, then need to rename it to a standard file name. Below is the file format: filename_yyyymmdd.csv, i need to pick the latest and move it with the... (2 Replies)
Discussion started by: kaushik25
2 Replies

10. Shell Programming and Scripting

[Shell] How make colums in text file ??

hi, i have little pb, i would like make a colums, but my server not recongize "\t" or i write wrong.... and iam little noobs and no know awk... #!/bin/ksh #---------------------------------------------------------------------------- # Fichiers : ctrl.sh et ctrl2005.txt ... (6 Replies)
Discussion started by: parola
6 Replies
Login or Register to Ask a Question