Awk between two dates in a logfile - almost working

Question

I have a csh script which is trying to identify entries in a logfile between two dates

(in the script they are $start_date and $end_date entered as DD/MM/YYYY but I have simplified here)

more text_B_14_FEB_03.dt | grep TMYO 

TMYO140043J:=TMYO140043J     P33BJm SOLO            03/02/2014 
TMYO140044J:=TMYO140044J     P4m    FINL            03/02/2014 
TMYO140044M:=TMYO140044M     P3BJ   FINL            03/02/2014 
TMYO140045M:=TMYO140045M     P33BJq MARS            04/02/2014 
TMYO140046M:=TMYO140046M     P33BJq RENN            04/02/2014 
TMYO140047M:=TMYO140047M     P33BJl AKHT            05/02/2014 
TMYO140048M:=TMYO140048M     P3l    MACL            05/02/2014 
TMYO140049M:=TMYO140049M     P3q    HAYE            06/02/2014 
TMYO140050M:=TMYO140050M     P3q    ROCH            06/02/2014 
TMYO140051M:=TMYO140051M     P3q    FORR            06/02/2014 
TMYO140052L:=TMYO140052L     P3v    ROSE            07/02/2014 
TMYO140053L:=TMYO140053L     P3v    CAIR            07/02/2014 
TMYO140054L:=TMYO140054L     P3v    MURR            07/02/2014

I have tried the following but it can't handle dates properly from previous year?

more text_B_14_FEB_03.dt | grep TMYO | awk '$5>="02/01/2013" && $5<="13/02/2014"'

TMYO140043J:=TMYO140043J     P33BJm SOLO            03/02/2014 
TMYO140044J:=TMYO140044J     P4m    FINL            03/02/2014 
TMYO140044M:=TMYO140044M     P3BJ   FINL            03/02/2014 
TMYO140045M:=TMYO140045M     P33BJq MARS            04/02/2014 
TMYO140046M:=TMYO140046M     P33BJq RENN            04/02/2014 
TMYO140047M:=TMYO140047M     P33BJl AKHT            05/02/2014 
TMYO140048M:=TMYO140048M     P3l    MACL            05/02/2014 
TMYO140049M:=TMYO140049M     P3q    HAYE            06/02/2014 
TMYO140050M:=TMYO140050M     P3q    ROCH            06/02/2014 
TMYO140051M:=TMYO140051M     P3q    FORR            06/02/2014 
TMYO140052L:=TMYO140052L     P3v    ROSE            07/02/2014 
TMYO140053L:=TMYO140053L     P3v    CAIR            07/02/2014 
TMYO140054L:=TMYO140054L     P3v    MURR            07/02/2014

here it wrongly misses date entries from 03/02/2014 when i change start date to 04/01/2013??

more text_B_14_FEB_03.dt | grep TMYO | awk '$5>="04/01/2013" && $5<="13/02/2014"'

TMYO140045M:=TMYO140045M     P33BJq MARS            04/02/2014 
TMYO140046M:=TMYO140046M     P33BJq RENN            04/02/2014 
TMYO140047M:=TMYO140047M     P33BJl AKHT            05/02/2014 
TMYO140048M:=TMYO140048M     P3l    MACL            05/02/2014 
TMYO140049M:=TMYO140049M     P3q    HAYE            06/02/2014 
TMYO140050M:=TMYO140050M     P3q    ROCH            06/02/2014 
TMYO140051M:=TMYO140051M     P3q    FORR            06/02/2014 
TMYO140052L:=TMYO140052L     P3v    ROSE            07/02/2014 
TMYO140053L:=TMYO140053L     P3v    CAIR            07/02/2014 
TMYO140054L:=TMYO140054L     P3v    MURR            07/02/2014

Any idea where is the awk part is going wrong? I appreciate perl is probably the most flexible answer to this, but my perk scripting is not there yet, and I would like to solve this using awk first.

`more` is completely [useless](http://partmaps.org/era/unix/award.html) here, and the `grep` can also easily be factored out; `awk '/TMYO/ && ...' file` — tripleee, Feb 13 '14 at 17:34

score 4 · Answer 1 · answered Feb 13 '14 at 17:32

You need to parse dates to seconds and compare them. You have to use mktime() function, that accepts a string with each part of a date, so you will have to split() it first. This program is weird because it has much repeated code, but it seems to work, and I hope you get the idea:

awk '
    BEGIN { 
        date1 = "04/02/2014"
        split(date1, arr, "/")
        seconds1 = mktime(arr[3] " " arr[2] " " arr[1] " 0 0 0") 

        date2 = "06/02/2014"
        split(date2, arr, "/")
        seconds2 = mktime(arr[3] " " arr[2] " " arr[1] " 0 0 0")
    }

    {
        split($NF, arr, "/")
        s = mktime(arr[3] " " arr[2] " " arr[1] " 0 0 0")

        if (s >= seconds1 && s <= seconds2) {
            print $0
        }
    }
' infile

With your second example data, it yields:

TMYO140045M:=TMYO140045M     P33BJq MARS            04/02/2014 
TMYO140046M:=TMYO140046M     P33BJq RENN            04/02/2014 
TMYO140047M:=TMYO140047M     P33BJl AKHT            05/02/2014 
TMYO140048M:=TMYO140048M     P3l    MACL            05/02/2014 
TMYO140049M:=TMYO140049M     P3q    HAYE            06/02/2014 
TMYO140050M:=TMYO140050M     P3q    ROCH            06/02/2014 
TMYO140051M:=TMYO140051M     P3q    FORR            06/02/2014

score 2 · Accepted Answer · answered Feb 13 '14 at 17:32

2

You should transform date to a format YYYYMMDD so it can be lexicographilly ordered. You can do it with gawk and regex, or by doing substrings operations with awk. Here is the gawk way

more text_B_14_FEB_03.dt | grep TMYO | gawk 'match($5, "([0-9]+)/([0-9]+)/([0-9]+)", ary) {B
=ary[3] ary[2] ary[1]; if (B < 20140213 && B> 20130104) print }'

answered Feb 13 '14 at 17:32

Raul Andres

3,766
15
24

This works well as shown - i then tried feeding my converted variable into it but it does not like that... `more text_B_14_FEB_03.dt| grep TMYO | gawk 'match($5, "([0-9]+)/([0-9]+)/([0-9]+)", ary) {B=ary[3] ary[2] ary[1]; if (B < "$end_date_mod" && B> "$start_date_mod") print $1}'` – scottyjock Feb 13 '14 at 18:05
@scottyjock I think single quotes are preventing bash to substitue your variables. You should then double quote awk statement and escape existing quotes, as in `a=s awk "BEGIN{print \"$a\"}" – Raul Andres Feb 13 '14 at 18:29
1

For what it's worth, `more A | grep B | awk 'C { D }'` is much better written as `awk '/B/ && C { D }' A` with no `more` (or `cat` or `dd` or whatever sick replacement you come up with next; `tar`?) and no separate `grep`. – tripleee Oct 12 '17 at 11:22

Awk between two dates in a logfile - almost working

2 Answers2