Fetch last 5 mins data and add the column also number the failed output

Question

I need your expert advice to get the below output

INPUT

2020-01-09 15:39:39,sharm,07296,97  
2020-01-09 15:40:40,SRI,6657,197  
2020-01-09 15:42:40,rinku,null,0  
2020-01-09 15:42:40,SVRMO,8588,39  
2020-01-09 15:44:58,MASTA,093720,48  

here, fetch the last 5 min data. Assuming the command is run at 15:45, data from (15:40-15:45) should be selected.

2020-01-09 15:40:40,SRI,6657,197  
2020-01-09 15:42:40,rinku,null,0  
2020-01-09 15:42:40,SVRMO,8588,39  
2020-01-09 15:44:58,MASTA,093720,48

The output should be

284,4,3   

Where

• 284 show sum of last row data (197+0+39+48)
• 4 show total count of data in last 5 min
• 3 show total count of data where last column value is not 0 (in the example: lines 1,3, and 4 where the value is 197,39, and 48, respectively)

Answer 1

Below awk command first getting current & past 5minutes ago dates and convert them to epoch time (Unix time) and set in startTime and endTime respectively; and then for every line we read it also converts first field date column into epoch as well and checking if time is between startTime&endTime then do calculations required. In the END we print the last value of sum, total and nonZero variables calculated.

awk -vstartTime="$(date -d'-5 minutes' '+%s')" \
    -vendTime="$(date '+%s')" \
    -F, '{ epoch="date -d\"" $1 "\" +%s"; epoch |getline timestamp; }
         (timestamp>=startTime && timestamp<=endTime){ sum+=$NF; total++; if($NF!=0)nonZero++ }
END{ print sum, total, nonZero }' OFS=, infile

---

manually test on test data given:

awk -vstartTime=1578571800 \
    -vendTime=1578572100 \
    -F, '{ epoch="date -d\"" $1 "\" +%s"; epoch |getline timestamp; }
         (timestamp>=startTime && timestamp<=endTime){ sum+=$NF; total++; if($NF!=0)nonZero++ }
END{ print sum, total, nonZero }' OFS=, infile