CODEWITHSUNDEEP
bashdateawksedtimestamp
Besoul
Besoul

Reputation: 35

Format and compute time difference between dates (awk/sed)

I am trying to compute time difference between dates formatted as below:

dd/mm/YY;hh:mm:ss;dd/mm/YY;hh:mm:ss (the first couple dd/mm/YY;hh:mm:ss points out the start date and the second couple is the end date)

I want the output to be like this:

dd/mm/YY;hh:mm:ss;dd/mm/YY;hh:mm:ss;hh:mm:ss , where the added hh:mm:ss is the time difference between both dates.

Here is an example:

INPUT:
12/11/15;20:04:09;13/11/15;08:46:26
13/11/15;20:05:34;14/11/15;08:42:04
14/11/15;20:02:47;16/11/15;08:44:43

OUTPUT:
12/11/15;20:04:09;13/11/15;08:46:26;12:42:17
13/11/15;20:05:34;14/11/15;08:42:04;12:36:30
14/11/15;20:02:47;16/11/15;08:44:43;36:41:56

I've tried a lot of things with gsub, mktime and awk, in order to format dates, but nothing is efficient enough (too many operations to format and split).

Here is my attempt:

cat times.txt | awk -F';' '{gsub(/[/:]/," ",$0);d1=mktime("20"substr($1,7,2)" "substr($1,4,2)" "substr($1,1,2)" "$2);d2=mktime("20"substr($3,7,2)" "substr($3,4,2)" "substr($3,1,2)" "$4); print strftime("%H:%M:%S", d2-d1,1);}' > timestamps.txt
paste -d";" times.txt timestamps.txt

What do you suggest? Thank you :)

Upvotes: 1

Views: 1207

Answers (3)

Ed Morton
Ed Morton

Reputation: 203665

You cannot do this job robustly without mktime() as the time difference calculation needs to account for leap days, leap seconds, etc. I don't think you can do it any more efficiently than this:

$ cat tst.awk
BEGIN { FS="[/;:]" }
{
    d1 = mktime("20"$3" "$2" "$1" "$4" "$5" "$6)
    d2 = mktime("20"$9" "$8" "$7" "$10" "$11" "$12)
    delta = d2 - d1
    hrs = int(delta/3600)
    min = int((delta - hrs*3600)/60)
    sec = delta - (hrs*3600 + min*60)
    printf "%s;%02d:%02d:%02d\n", $0, hrs, min, sec
}

$ awk -f tst.awk file
12/11/15;20:04:09;13/11/15;08:46:26;12:42:17
13/11/15;20:05:34;14/11/15;08:42:04;12:36:30
14/11/15;20:02:47;16/11/15;08:44:43;36:41:56

Note - you cannot use strftime() [alone] to calculate the hrs, mins, and secs because when your delta value is more than 1 day strftime() will return the hrs, mins, and secs associated with the time of day on the last day of that delta instead of the total number of hrs, mins, and secs associated with the entire delta.

Upvotes: 1

F. Knorr
F. Knorr

Reputation: 3055

You could try this and save some gsub and substr calls:

awk -F'[:;/]' '{d1=mktime("20"$3" "$2" "$1" "$4" "$5" "$6);
                d2=mktime("20"$9" "$8" "$7" "$10" "$11" "$12); 
                delta = d2-d1
                sec = delta%60
                min = (delta - sec)%3600/60
                hrs = int(delta/3600)
                print $0";"(hrs < 10 ? "0"hrs : hrs)\
                        ":"(min < 10 ? "0"min : min)\
                        ":"(sec < 10 ? "0"sec : sec);}' time.txt

Since we cannot use strftime (tanks to Ed Morton), we have to handle the case that hours > 23 or hour/min/sec < 10 manually. The above code outputs:

14/11/15;20:02:47;16/11/15;08:44:43;36:41:56
14/11/15;20:02:47;14/11/15;20:02:48;00:00:01

for the input

14/11/15;20:02:47;16/11/15;08:44:43
14/11/15;20:02:47;14/11/15;20:02:48

Upvotes: 1

ghoti
ghoti

Reputation: 46856

What you're asking will be pretty tricky traditional awk.

Of course, gawk (GNU awk) supports mktime, but other awk implementations do not. But you can do this directly in bash, relying on the date command for your conversion. This solution uses BSD date (so it'll work in FreeBSD, NetBSD, OpenBSD, OSX, etc).

while IFS=\; read date1 time1 date2 time2; do
  stamp1=$(date -j -f '%d/%m/%y %T' "$date1 $time1" '+%s')
  stamp2=$(date -j -f '%d/%m/%y %T' "$date2 $time2" '+%s')
  d=$((stamp2-stamp1))
  printf '%s;%s;%s;%s;%02d:%02d:%02d\n' "$date1" "$time1" "$date2" "$time2" $(( (d/3600)%60)) $(( (d/60)%60 )) $((d%60))
done < dates.txt

Results:

12/11/15;20:04:09;13/11/15;08:46:26;12:42:17
13/11/15;20:05:34;14/11/15;08:42:04;12:36:30
14/11/15;20:02:47;16/11/15;08:44:43;36:41:56

Of course, if you're using a non-BSD OS, you may have to install bsddate (if it's available) to get this functionality, or figure out how to get something equivalent using the tools you have on hand.

Upvotes: 0

Related Questions