Invoking 'date' command inside awk string, with +%a formatting

Question

Still newish to the site, but here goes... Basically I'm storing events in multiple files, with each event being a line and each line containing dates ($1), start($2) and stop($3) times and several other pieces of data. I use two double underscores ("__") as Field Separators. I've been using a variety of shell scripts to manage the data, and I was using awk to calculate stats and I'm having trouble invoking the date function so I can get a total by day of the week. After much tinkering and scanning of discussion boards I got to this:

ls /home/specified/folder/MBRS.db/* |
xargs -n 1 -I % awk -F"__" '$6 == "CLOSED" && $1 >= "'$backDATE'" { print $0 }' % |
awk 'BEGIN{FS="__"}{specDATE=system("date --date="$1" +%a")} specDATE == "Tue" {print $2" "$3}'

or

ls /home/lingotech/Einstein/data/MBRS.db/* |
xargs -n 1 -I % awk -F"__" '$6 == "CLOSED" && $1 >= "'$backDATE'" { print $0 }' % |
awk 'BEGIN{FS="__"}system("date --date="$1" +%a") == "Mon" {print $2" "$3}'`

Instead of outputting the start and stop times, I'm getting a list of all the different days of the week for each entry.

I've tried more variations of the date usage than I care to admit, including:

for y in Sun Mon Tue Wed Thu Fri Sat; do
  for directory in $( ls /home/specified/directory/MBRS.db/* | xargs -n 1 ); do
    printf "."
    [[ $( cat $directory | awk -F"__" '$6 == "CLOSED" && $1 >= "'$backDATE'" { print $1 }' | xargs -n 1 -I z date +%a -d z ) == "$y" ]] && echo BLAH
  done
done

Some helpful explanation of what I'm screwing up would be much appreciated. Thanks in advance. Oh and I'm storing the date in YYMMDD format but that doesn't seem to be an issue for ubuntu server's version of 'date'.

Answer 1

I don't know about all the rest of it (too much text for my reading tastes!) but wrt the answer you posted, this part of it:

awk 'BEGIN{FS="__"} NF == 10 && $1 >= "'$backDATE'" && $4 == "'$x'" && $6 == "CLOSED" {while ( "date +%a -d "$1"" | getline newDAY){if (newDAY == "'$y'") print $2" "$3}}' /home/absolute/path/*

assuming it does what you want would be written as:

awk -v backDATE="$backDATE" -v x="$x" -v y="$y" '
    BEGIN { FS="__" }
    (NF == 10) && ($1 >= backDATE) && ($4 == x) && ($6 == "CLOSED") {
        cmd = "date +%a -d \"" $1 "\""
        while ( (cmd | getline newDAY) > 0 ) {
            if (newDAY == y) {
                print $2, $3
            }
        }
        close(cmd)
    }
' /home/absolute/path/*

wrt why use awk variables instead of letting shell variables expand to become part of the body of a shell script, the answer is robustness and simplicity.

This is letting a shell variable expand to become part of the body of an awk script:

$ x="hello world"
$ awk 'BEGIN{ print '$x' }'
awk: cmd. line:1: BEGIN{ print hello
awk: cmd. line:1:                   ^ unexpected newline or end of string
$ awk 'BEGIN{ print "'$x'" }'
awk: cmd. line:1: BEGIN{ print "hello
awk: cmd. line:1:              ^ unterminated string
awk: cmd. line:1: BEGIN{ print "hello
awk: cmd. line:1:              ^ syntax error
$ awk 'BEGIN{ print "'"$x"'" }'
hello world
$ x="hello
world"
$ awk 'BEGIN{ print "'"$x"'" }'
awk: cmd. line:1: BEGIN{ print "hello
awk: cmd. line:1:              ^ unterminated string
awk: cmd. line:1: BEGIN{ print "hello
awk: cmd. line:1:              ^ syntax error

and this is using an awk variable initialized with the value of a shell variable:

$ x="hello world"
$ awk -v x="$x" 'BEGIN{ print x }'
hello world

$ x="hello
world"
$ awk -v x="$x" 'BEGIN{ print x }'
hello
world

See the difference?

As for why store the command in a variable - because you have to close it after you use it and it must be spelled exactly the same way in the close command as it was when you opened the pipe. Compare:

cmd = "date +%a -d \"" $1 "\""
cmd | getline
close(cmd)

vs:

"date +%a -d \"" $1 "\"" | getline
close("date +%a -d \"" $l "\"")

and take an extremely close second look to spot the bug in the 2nd version.

Answer 2

Ok, so I ended up using this:

>backDATE=150000; 
>     for x in $listOFsites; do
>        for y in Sun Mon Tue Wed Thu Fri Sat; do
>            totalHOURS=$( awk 'BEGIN{FS="__"} NF == 10 && $1 >= "'$backDATE'" && $4 == "'$x'" && $6 == "CLOSED" {while ( ( "date +%a -d \""$1"\"" | getline newDAY) > 0 ){if (newDAY == "'$y'") print $2" "$3}}' /home/absolute/path/* | xargs -I % /home/custom/duration/calc % | paste -sd+ | bc ); printf "."; 
>        done
>     done

I had to use date inside the single quotes (so that I could pass $1 to it) rather than outside (using -F"__" -v newDAY=...), but inside the single quotes getting the output of system() is problematic. After seeing:How can I pass variables from awk to a shell command? I finally saw the while (cmd | get line x) format which was the crux of my issue. Props to Ed Morton