2

Would like to calculate number of days between two dates , for the below example, No of days == $6-$4 position.

Input.txt

Desc1,Desc2,Desc3,DATE_ACTIVE,STATEMENT_DATE,Desc4
abc,def,ghi,21-MAR-08,01-JUL-14,jkl
abc,def,ghi,01-JUL-14,15-JUL-14,jkl
abc,def,ghi,06-NOV-06,08-JUL-14,jkl
abc,def,ghi,18-NOV-03,08-JUL-14,jkl
abc,def,ghi,14-JUN-06,15-JUL-14,jkl

Desired Output:

Desc1,Desc2,Desc3,DATE_ACTIVE,STATEMENT_DATE,Desc4,No_Of_Days
abc,def,ghi,21-MAR-08,01-JUL-14,jkl,2293
abc,def,ghi,01-JUL-14,15-JUL-14,jkl,14
abc,def,ghi,06-NOV-06,08-JUL-14,jkl,2801
abc,def,ghi,18-NOV-03,08-JUL-14,jkl,3885
abc,def,ghi,14-JUN-06,15-JUL-14,jkl,2953

Have tried below commands and partial ...

awk 'BEGIN {FS=OFS=","} {print $0,$6-$4}' Input.txt

awk 'BEGIN {FS=OFS=","} {print $0,$date($6)-$date($4)}' Input.txt

Any suggestions ..

VNA
  • 605
  • 2
  • 8
  • 18

3 Answers3

5

one way to do that:

 kent$  awk -F, -v q='"' '{
 c="date -d"q$4q" +%s";c|getline d1;close(c); 
 c="date -d"q$5q" +%s";c|getline d2;close(c);
 printf "%s,%d\n", $0,(d2-d1)/3600/24}' file
abc,def,ghi,21-MAR-08,01-JUL-14,jkl,2292
abc,def,ghi,01-JUL-14,15-JUL-14,jkl,14
abc,def,ghi,06-NOV-06,08-JUL-14,jkl,2800
abc,def,ghi,18-NOV-03,08-JUL-14,jkl,3884
abc,def,ghi,14-JUN-06,15-JUL-14,jkl,2953
Kent
  • 189,393
  • 32
  • 233
  • 301
1

Not the awk, but true date/days calculation:

#!/bin/bash
read line < input;
echo -n $line; echo "No_Of_Days";
sed '1d' input| 
while IFS=,
read a b c d e f;
do 
 let DIFF=(`date +%s -d $d`-`date +%s -d $e`)/86400;
 echo -n $a,$b,$c,$d,$e,$f,
 echo $(($DIFF *-1));
done

produces:

Desc1,Desc2,Desc3,DATE_ACTIVE,STATEMENT_DATE,Desc4No_Of_Days
abc,def,ghi,21-MAR-08,01-JUL-14,jkl,2293
abc,def,ghi,01-JUL-14,15-JUL-14,jkl,14
abc,def,ghi,06-NOV-06,08-JUL-14,jkl,2801
abc,def,ghi,18-NOV-03,08-JUL-14,jkl,3885
abc,def,ghi,14-JUN-06,15-JUL-14,jkl,2953
Ruslan Gerasimov
  • 1,752
  • 1
  • 13
  • 20
0

The other examples here are script examples in unix, and not necessarily awk. It can be done natively in some awk extensions, such as nawk or gawk, as well, using the mktime(datespec) function. You need to convert your date to a standardized format:

function getDate(date) {
    split(date, a, "-");
    return mktime("20" a[3] " " sprintf("%02i",(index("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC", a[2])+2)/3) " " a[1] " 00 00 00")
}

BEGIN {FS=","}

{printf("%i\n",0.4+(getDate($5)-getDate($4))/86400)}

The extra 0.4 is needed to compensate for leap time units.

Bex
  • 2,905
  • 2
  • 33
  • 36