4

I have a file of size 7GB. Now have two date time in that I want to use awk to get this difference of time between that two DateTime.

Below is how my files look like:

A          B      C      D         E
18/06/28 09:19:07 295  141536 18-06-28 09:17:47
18/06/28 09:20:07 268  1160   18-06-28 09:18:58
18/06/28 09:21:07 317  1454   18-06-28 09:19:47
18/06/28 09:22:07 275  1491   18-06-28 09:20:59
18/06/28 09:23:07 320  1870   18-06-28 09:21:07
18/06/28 09:24:07 310  1869   18-06-28 09:22:30
18/06/28 09:25:07 150   693   18-06-28 09:23:28
18/06/28 09:26:07 414  2227   18-06-28 09:24:34 

I want the difference between (AB) - (E).

I tried this :

cat filename | awk -F " " '{print date -d ($1$2)-($5)}' 

Output should be the time difference between the two datetime. Like for the first row difference will be 1min 20sec

  • your desired result format? – msp9011 Jun 28 '18 at 17:03
  • time difference output will be 02:40 for the first row – Developer Jun 28 '18 at 17:06
  • Why specifically in awk and then why adding the perl tag? – Patrick Mevzek Jun 28 '18 at 17:08
  • Please don't clarify with comments; edit the question to clarify. – DopeGhoti Jun 28 '18 at 17:09
  • @glennjackman I did it my mistake you are correct – Developer Jun 28 '18 at 17:14
5

Using GNU awk:

gawk '
  function dt2epoch(date, time,      timestr) {
    timestr = "20" substr(date,1,2) " " substr(date,4,2) " " substr(date,7,2) \
               " " substr(time,1,2) " " substr(time,4,2) " " substr(time,7,2)
    return mktime(timestr)
  }
  function epoch2hms(t) {
    return strftime("%H:%M:%S", t, 1)
  }
  function abs(n) {return n<0 ? -1*n : n}
  NR == 1 {next}
  { print epoch2hms(abs(dt2epoch($5,$6) - dt2epoch($1,$2))) }
' file

outputs

00:01:20
00:01:09
00:01:20
00:01:08
00:02:00
00:01:37
00:01:39
00:01:33

with perl, I'd use the DateTime ecosystem:

perl -MDateTime::Format::Strptime -lane '
    BEGIN {$f = DateTime::Format::Strptime->new(pattern => "%y-%m-%d %H:%M:%S")}
    next if $. == 1;
    $F[0] =~ s{/}{-}g;
    $t1 = $f->parse_datetime("$F[0] $F[1]");
    $t2 = $f->parse_datetime("$F[4] $F[5]");
    $d = $t1->subtract_datetime($t2);
    printf "%02d:%02d:%02d\n", $d->hours, $d->minutes, $d->seconds;
' file

A much faster perl version, that does not require any non-core modules

perl -MTime::Piece -lane '
    next if $. == 1;
    $t1 = Time::Piece->strptime("$F[0] $F[1]", "%y/%m/%d %H:%M:%S");
    $t2 = Time::Piece->strptime("$F[4] $F[5]", "%y-%m-%d %H:%M:%S");
    $diff = gmtime(abs($t1->epoch - $t2->epoch));
    print $diff->hms;
' file

or, alternate output:

$ perl -MTime::Piece -lane '
    next if $. == 1;
    $t1 = Time::Piece->strptime("$F[0] $F[1]", "%y/%m/%d %H:%M:%S");
    $t2 = Time::Piece->strptime("$F[4] $F[5]", "%y-%m-%d %H:%M:%S");
    print abs($t1 - $t2)->pretty;
' file
1 minutes, 20 seconds
1 minutes, 9 seconds
1 minutes, 20 seconds
1 minutes, 8 seconds
2 minutes, 0 seconds
1 minutes, 37 seconds
1 minutes, 39 seconds
1 minutes, 33 seconds
0

Using bash and awk combined:

$ awk 'NR>1 {print $1,$2,$5,$6}' input  | while read d1 t1 d2 t2; do
  i1=$(date -u -d "20$d1 $t1" +%s)
  i2=$(date -u -d "20$d1 $t2" +%s)
  date -d @"$((i1-i2))" +%M:%S; 
done
01:20
01:09
01:20
01:08
02:00
01:37
01:39
01:33
  • I think this will be extremely slow for a huge input file, calling out to date for every line. – glenn jackman Jun 28 '18 at 17:29
  • It might, but then both of our solutions do a whole passel of string comparisons. It'd also be doable, but a larger lift, to actually leverage awk with FS=/[\s/:-]/and doing all the arithmetic "by hand". – DopeGhoti Jun 28 '18 at 17:53
  • date -d and date +%s are both proprietary extensions. – schily Jun 28 '18 at 20:51
  • GNU is proprietary now? And I've never seen an implementation of date that didn't have output format strings. – DopeGhoti Jul 2 '18 at 15:02
0

benchmarking: I duplicated your sample data a whole bunch of times

$ wc -l file
131073 file

Now some timing:

$ time  awk 'NR>1 {print $1,$2,$5,$6}' file |
while read d1 t1 d2 t2; do
  i1=$(date -u -d "20$d1 $t1" +%s)
  i2=$(date -u -d "20$d1 $t2" +%s)
  date -d @"$((i1-i2))" +%M:%S
done > /dev/null

real    8m55.533s
user    5m46.956s
sys     1m33.726s
$ time perl -MDateTime::Format::Strptime -lane '
    BEGIN {$f = DateTime::Format::Strptime->new(pattern => "%y-%m-%d %H:%M:%S")}
    next if $. == 1;
    $F[0] =~ s{/}{-}g;
    $t1 = $f->parse_datetime("$F[0] $F[1]");
    $t2 = $f->parse_datetime("$F[4] $F[5]");
    $d = $t1->subtract_datetime($t2);printf "%02d:%02d:%02d\n", $d->hours, $d->minutes, $d->seconds;
' file > /dev/null

real    0m37.684s
user    0m33.168s
sys     0m0.058s
$ time gawk '
  function dt2epoch(date, time,      timestr) {
    timestr = "20" substr(date,1,2) " " substr(date,4,2) " " substr(date,7,2) \
               " " substr(time,1,2) " " substr(time,4,2) " " substr(time,7,2)
    return mktime(timestr)
  }
  function epoch2hms(t) {
    return strftime("%H:%M:%S", t, 1)
  }
  function abs(n) {return n<0 ? -1*n : n}
  NR == 1 {next}
  { print epoch2hms(abs(dt2epoch($5,$6) - dt2epoch($1,$2))) }
' file > /dev/null

real    0m1.074s
user    0m0.610s
sys     0m0.366s

GNU awk, with the built-in time functions is the clear winner, even with all the string manipulation.


Update: new perl implementation. Still slower than gawk, but miles ahead of the version using the feature-rich but heavyweight DateTime module:

$ time perl -MTime::Piece -lane '
    next if $. == 1;
    $t1 = Time::Piece->strptime("$F[0] $F[1]", "%y/%m/%d %H:%M:%S");
    $t2 = Time::Piece->strptime("$F[4] $F[5]", "%y-%m-%d %H:%M:%S");
    $diff = gmtime(abs($t1->epoch - $t2->epoch));
    print $diff->hms;           
' file > /dev/null

real    0m2.631s
user    0m2.231s
sys     0m0.170s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.