1

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
  • 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 – shadowtalker Dec 16 '16 at 2:50
5

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

  • @ steeldriver Thanks a lot for the information and kind help. – Daniel Dec 16 '16 at 14:58
  • @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 – steeldriver Dec 16 '16 at 17:11

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.