Calculating the total time in an online meeting

Question

Suppose there was a meeting and the meeting record is saved in a CSV file. How to write a bash script/awk script to find out the total amount of time for which an employee stayed online. One employee may leave and rejoin the meeting, all his/her online time should be calculated.

What I did is as follows, but got stuck on how to compare one record with all other record, and add the total time of each joined and left pairs of a person.

#!/bin/bash

inputFile=$1
startTime=$(date -u -d $2 +"%s")
endTime=$(date -u -d $3 +"%s")

awk 'BEGIN{ FS=","; totalTime=0; }

        {
            for (rows=1; rows <= NR; rows++) {
             #I am stuck here on how to compare a record with each and every record
                     if (($1==?? && $2=="Joined") && ($1==?? && $2=="Left")) {
                     totalTime=$($(date -u -d $3 +"%s")-$(date -u -d $3 +"%s"))
       print $1 "," $totalTime +"%H:%M:%S"
 }' $inputFile

The start_time and end_time of the meeting are given at command line such as:

$ ./script.sh input.csv 10:00:00 13:00:00

The output look like this: (Can be stored in an output file)

Bob,    00:30:00
John,    01:02:00

The contents of the CSV file is as follows:

    Employee_name, Joined/Left, Time  
    John, joined, 10:00:00  
    Bob, joined, 10:01:00  
    James, joined, 10:00:30  
    Bob, left, 10:20:00  
    Bob, joined, 10:35:00  
    Bob, left, 11:40:00  
    James, left, 11:40:00  
    John, left, 10:41:00
    Bob, joined, 11:45:00

`name=$1; if (name in names) { times[name]=$3} else {names[$1]=$1;times[$1]=$3}` is an idea about how to *"compare a record with each and every record*". . Work thru the [awk tutorial](http://grymoire.com/Unix/Awk.html) . Pay particular attention to `awk`'s associative arrays. Calculating the elapsed time from the provided data will be an advanced problem. You will probably need to make a separate Q about that. Hopefully you have access to a `gawk v >= 4.0`. Good luck. — shellter, Sep 03 '20 at 21:05
Replace the block of text under `The output may look like this` with what the actual output MUST look like for the sample input you provided. — Ed Morton, Sep 03 '20 at 21:34

Ed Morton · Accepted Answer · 2020-09-03T21:35:12.367

$ cat tst.awk
BEGIN { FS=" *, *"; OFS=", " }
NR==1 { next }
$1 in joined {
    jt = time2secs(joined[$1])
    lt = time2secs($3)
    totSecs[$1] += (lt - jt)
    delete joined[$1]
    next
}
{ joined[$1] = $3 }
END {
    for (name in totSecs) {
        print name, secs2time(totSecs[name])
    }
}

function time2secs(time,        t) {
    split(time,t,/:/)
    return (t[1]*60 + t[2])*60 + t[3]
}

function secs2time(secs,        h,m,s) {
    h = int(secs / (60*60))
    m = int((secs - (h*60*60)) / 60)
    s = int(secs % 60)
    return sprintf("%02d:%02d:%02d", h, m, s)
}

.

$ awk -f tst.awk file
James, 01:39:30
Bob, 01:24:00
John, 00:41:00

If you need to consider DST transitions, leap-seconds, meetings going overnight (or for multiple days), people still being in the meeting when it ends, or anything else you haven't shown in your question - that is left as an exercise :-).

Thanks, it helped me a lot. I am curious on how to improve it so that (Only the time between startTime (10:00:00) and EndTime (13:00:00) must considered. And also the persons who have joined but not left to be considered as left at Endtime and their online time should be added also). I tried but no desired output — Jan, Sep 04 '20 at 08:53

Calculating the total time in an online meeting

1 Answers1

Linked