How to use awk command to calculate the date difference between two columns in the same file?

Question

I have a multi columns csv file, comma separated which has two columns with different date (mm/dd/yyyy). I am going to identify the difference between these two date. following is the example:

echo filename
    001xxxc,28.2,03/04/2009,11/19/2009
    00cvbfd,34.4,03/04/2009,01/06/2010
    04rsdsd,34,12/01/2006,10/02/2001
    456dfds,40,12/01/2006,04/23/2002
    et556ss,40.8,12/01/2006,10/22/2002

I wonder is there anyway to use awk command to get the date difference.

I tried this awk command but I am sure it is not the correct way?

awk -F, '{print $1","$2","$3-$4}' filename

Solution

Assuming you want the difference in days, then if you have GNU awk (gawk) you could do something like

gawk -F, '
  {
    split($3,a,"/"); 
    split($4,b,"/"); 
    t1 = mktime(sprintf("%d %d %d 0 0 0 0",a[3],a[1],a[2])); 
    t2 = mktime(sprintf("%d %d %d 0 0 0 0",b[3],b[1],b[2])); 
    print (t2-t1)/86400
  }
' filename
260
308
-1886
-1683
-1501

The mktime argument needs to be a string of the format "YYYY MM DD HH MM SS [DST]"; setting the optional DST flag to zero tells it to ignore daylight savings (otherwise the naive division by 86400 results in fractional days).

See Gawk: Effective AWK Programming, 9.1.5 Time Functions

Comments

[shadowtalker]

As far as I know AWK doesn't have date manipulating functions. You could do it in a more powerful scripting language like Python, Perl, Ruby, etc

[Daniel]

@ steeldriver Thanks a lot for the information and kind help.

[smw]

@Daniel you're welcome - note that (to avoid surprises) you should probably check the return values of the mktime calls to confirm that the strings were parsed successfully