want to sum time (00:00:00) in AWK command

user_10870863 0
Viewed 744

I have awk command to sum time from another filed it works with the numbers, but when tried with hours it doesn't work

example

> cat ee.txt
amazon|4
ebay|5
alibab|10
ebay|4
amazon|5

> awk -F '|' '$1 ~ /ebay/{ sum += $2 } END{ print sum }' ee.txt
8

but when I need to sum the time the command doesnt work 

> cat ee.txt
amazon  1:02:00
ebay    2:00:00
alibab  240:00:00
ebay    3:00:00
amazon  72:00:00

> awk -F '|' '$1 ~ /ebay/{ sum += $2 } END{ print sum }' ee.txt

the output should be 5:00:00

awk
2 Answers

Something like this maybe:

$ awk '
/ebay/ {
    split($2,a,":")                # separate hs, ms and sses
    b+=a[1]*3600+a[2]*60+a[3]      # sum them up as as seconds
}
END {
    h=int(b/3600)                  # separate hs
    m=int((b-3600*h)/60)           # and ms
    s=b-3600*h-60*m                # and sses
    printf "%d:%02d:%02d\n",h,m,s  # output
}' file
5:00:00

Basically you need to convert all time components to seconds, sum the records up and break them up to components again. You could also use GNU awk's time functions but that wouldn't allow all hh:mm:ss output, as times > 24 h would convert to dates.

Edit:

Added a version for all summing upp all values and grouping by company name:

$ awk '
{
    split($2,a,":")                         # separate hs, ms and sses
    b[$1]+=a[1]*3600+a[2]*60+a[3]           # sum them up as as seconds
}
END { 
    for(i in b) {
        h=int(b[i]/3600)                    # separate hs
        m=int((b[i]-3600*h)/60)             # and ms
        s=b[i]-3600*h-60*m                  # and sses
        printf "%s %d:%02d:%02d\n",i,h,m,s  # output
    }
}' file
alibab 240:00:00
amazon 73:02:00
ebay 5:00:00
user_41623560

Another awk using space and : as delimiter

$ awk -F"[ :]+" ' { a[$1]+=$2*3600+$3*60+$4 } END {for(i in a){hr=int(a[i]/3600);mi=int((a[i]-hr*3600)/60);s=a[i]-3600*hr-60*mi;printf("%s %02d:%02d:%02d\n", i,hr,mi,s) }}' ee.txt
ebay 05:00:00
alibab 240:00:00
amazon 73:02:00
user_68670480
Related