0

In a nutshell, my main problem is printing a record when date in one field is within a month from another field. All dates are in MM-DD-YYYY format.

Specifically, I am looking to extract records from a file that contains 108 pipe (|) delimited fields, if they meet the following criteria:

  1. Date field 14 & 61 must have October data #Resolved

  2. Date field 14 must be smaller than date field 15 + 1month ($15 < $14+ 1month) #Not resolved

My code:

awk -F'|' '{ if ($14 ~ /10-..-2016/ && $61 ~ /10-..-2016/ && $15< date -d '$14 1 month'  ) print $0}' <input >output

The part that's not working is $15< date -d '$14 1 month'. The main problem is the MM-DD-YYYY format and that I'm comparing two fields.

Input(I do not have headers, using them just to help explain my sample data. In bold the reason for not including the record):
.....|field14|field15|.....|Field61|.....
1.....|10-21-2016|11-23-2016|.....|10-25-2016|.....
2.....|10-21-2016|11-20-2016|.....|11-25-2016|.....
3.....|10-21-2016|11-19-2016|.....|10-25-2016|.....
4.....|10-15-2016|11-10-2016|.....|10-25-2016|.....
5.....|10-21-2016|10-19-2016|.....|10-25-2016|.....
6.....|09-21-2016|09-19-2016|.....|10-25-2016|.....
Desirable Output(headers used just for explanation):
.....|field14|field15|.....|Field61|.....
3.....|10-21-2016|11-19-2016|.....|10-25-2016|.....
4.....|10-21-2016|11-15-2016|.....|10-25-2016|.....

How can I fix this?

  • 3
    Use date's format strings to convert to seconds since epoch (date +%s), and then you just have an integer comparison to do. – DopeGhoti Mar 29 '17 at 18:14
  • Please edit your question and show us an example of your input file and the output you would want from it. Say one line that should be printed and one that shouldn't. It's very hard to test this sort of thing without any test data. – terdon Mar 29 '17 at 18:46
  • @DopeGhoti Can you elaborate? I'm getting an error as date is not recognizing MM-DD-YYYY format. Error code: date: invalid date – Greg Mar 29 '17 at 18:47
  • You might need to reformat it into YYYY-MM-DD from MM-DD-YYYY; date -d is pretty adaptable but seems to stumble on MDY: date -d"2018-04-14" +%s works for example. – DopeGhoti Mar 29 '17 at 18:52
  • @terdon provided sample input and output data – Greg Mar 29 '17 at 19:19
1
perl -F'[|]' -lane '
   ($m2, $d2, $y2, $m1, $d1, $y1) = map { split /-/ } @F[14,13];

   ($m2, $d2, $y2, $m1, $d1, $y1) =
   ($m1, $d1, $y1, $m2, $d2, $y2) if !($y2 > $y1 or $m2 > $m1 or $d2 > $d1);

   print if
      2 == grep /^10-\d{2}-\d{4}$/, @F[13,60]
                and
      (((12*($y2-$y1)+$m2-$m1) == 1 && ($d2 < $d1))
                    ||
          ((12*($y2-$y1)+$m2-$m1) == 0))
' yourfile

Explanation

We setup an implicit line reading loop and split each line read in using the pipe '|' delimiter and the arrray @F which index started at 0 is built up.

Then we stuff month/year/day info from fields 14 and 15 into scalar variables for ease of manipulations later on down under in the code.

And while we're at it, we do a slight tweak to ensure that m2y2d2 date is always newer than the m1y1d1 just so as to simplify our date logic calculations down under.

Finally, we decide on printing the current record, aka, line, judging by these 4 criteria, viz.,

  • The 14-th element,i.e., $F[13] is an October month date. AND
  • The 61-st element,i.e., $F[60] is an October month date too. AND
  • The two dates are separated by a month, the year has been factored in using the term (y2-y1)*12, they lie within a month of each other when higher date's day is < lower date's day. OR
  • The two dates are of the same year, same month => they are within a month anyway.
0

Running date for every line would be quite inefficient, you'd be better off using a text-processing tool that can do date calculation by itself like perl:

perl -MTime::Piece -F'[|]' -lane 'print if
   $F[13] =~ /10-..-2016/ && 
   $F[60] =~ /10-..-2016/ &&
   Time::Piece->strptime($F[14], "%m-%d-%Y") <
     Time::Piece->strptime($F[13], "%m-%d-%Y")->add_months(1)' file 
  • @ Stephane I'm getting error code: Can't locate Time/Piece.pm. Given that I found the directory that has Piece.pm using locate piece, I'm guessing it's installed at the wrong location? Given the above, how can I fix this and is there a way of completing this using only awk? – Greg Mar 29 '17 at 20:59
  • @Stephane: -F'|' => -F'\|' or unambiguous -F'[|]' – user218374 Mar 30 '17 at 4:42
  • @Rakesh, thanks. I had incorrectly assumed it was the same as with awk (that when the separator is a single character, it is not treated as a regexp) and did the wrong test to verify (echo 'a|b'|perl -F '|' -lane 'print $F[0]'). Fixed now. – Stéphane Chazelas Mar 30 '17 at 6:34
0

Actually, it is not all that difficult if you use GNU awk, which has built-in time functions:

$2 ~ /^10/ && $5 ~ /^10/ {
    split($2, t, "-");
    t1 = mktime(t[3] " " t[1] " " t[2] " 0 0 0");
    split($3, t, "-");
    t2 = mktime(t[3] " " t[1] " " t[2] " 0 0 0");
    if (t2 >= t1 && t2 - t1 <= 30*24*3600) {
        print;
    }
}
  • @Michael_Vehrs: The OP wanted to see dates that are within a month of each other, whereas your date logic just looks at whether the before date is older compared to after date. – user218374 Mar 30 '17 at 7:20
  • @RakeshSharma Thanks for pointing that out. I did not understand what the OP was after. – Michael Vehrs Mar 30 '17 at 8:53

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.