Get time different between two dates using awk.

Shell Programming and Scripting
1

Hello,
I have looked at perados date topics and couldn't find one that would suit my needs. I have the output below from Microsoft sql server. It shows where there was a status change. The date time in column two and the status indicator is in column 4.

In the first two rows it goes from status 5 to status 6. I need to know the difference in probably seconds as I will have to keep a tally on the different status changes throughout the day. So the first status changes looks to be about 184 seconds. Anyone have a nice awk script to do this with. You can see there are more 5 status down row so I would need to keep how many seconds they were in each status before it was changed. Thanks for any help.

2067|2013-01-24 16:03:12.653|-300|5|0|1|ccf58dcab49ca44e8b8076f7dd7f253b
2067|2013-01-24 16:06:17.280|-300|6|0|1|240b9783db05ff4b82349abaf5c1753b
2067|2013-01-24 16:08:35.530|-300|3|0|1|58387401f78a0148b800e3ee80ecf9d0
2067|2013-01-24 16:11:06.780|-300|4|0|1|05f50fa0a6c3cc4a9f8032edd0c7b474
2067|2013-01-24 16:11:13.110|-300|5|0|1|73d0a5dbe599d84ea146657673d43997
2067|2013-01-24 16:15:58.830|-300|6|0|1|12ae1952dbb1f345bc19beb38a72f445
2067|2013-01-24 16:18:26.160|-300|3|0|1|136f38b46e929b41824b333d3e919e3a
2067|2013-01-24 16:29:34.087|-300|4|0|1|1f112c97a196b244b2425c5704aeb1e3
2067|2013-01-24 16:29:41.680|-300|5|0|1|3377cd61bf7b50448b14e8a15fc90c7d
2067|2013-01-24 16:32:06.120|-300|6|0|1|16549721e5a19842a7ed6b16e27a7200
2067|2013-01-24 16:37:23.357|-300|3|0|1|e48e6cfa25177b48aa1b181af70bc78e
2067|2013-01-24 16:37:39.327|-300|2|4|1|f8d1a6d599464845890da88858735bc2
2067|2013-01-24 16:39:59.810|-300|3|0|1|7dd37aaebb8631408fa6c2b672c91dc3
2067|2013-01-24 16:51:46.753|-300|4|0|1|8d3c60b2d6bd2e4c91cec113579dd2b2
2067|2013-01-24 16:51:53.363|-300|5|0|1|1fdaf2ae7f15c240964bc251db6ce1fa
2067|2013-01-24 16:55:12.427|-300|6|0|1|adca256dc1ef644dad78fca87c9b9c47
2

Use mktime to get the seconds and substract: Time Functions - The GNU Awk User's Guide

3

If you have GNU date , then use a BASH script:

#!/bin/bash
c=0
while IFS="|" read f1 f2 f3
do
        ef2=$( date -d"$f2" +"%s" )
        [[ $c -eq 0 ]] && echo "$f1|$f2|$f3|NA"
        if [ $c -ne 0 ]
        then
                D=$(( ef2 - ep2 ))
                echo "$f1|$f2|$f3|$D"
        fi
        p2="$f2"; ep2=$( date -d"$p2" +"%s" )
        c=$(( c + 1 ))
done < filename

Here is the output:

2067|2013-01-24 16:03:12.653|-300|5|0|1|ccf58dcab49ca44e8b8076f7dd7f253b|NA
2067|2013-01-24 16:06:17.280|-300|6|0|1|240b9783db05ff4b82349abaf5c1753b|185
2067|2013-01-24 16:08:35.530|-300|3|0|1|58387401f78a0148b800e3ee80ecf9d0|138
2067|2013-01-24 16:11:06.780|-300|4|0|1|05f50fa0a6c3cc4a9f8032edd0c7b474|151
2067|2013-01-24 16:11:13.110|-300|5|0|1|73d0a5dbe599d84ea146657673d43997|7  
2067|2013-01-24 16:15:58.830|-300|6|0|1|12ae1952dbb1f345bc19beb38a72f445|285
2067|2013-01-24 16:18:26.160|-300|3|0|1|136f38b46e929b41824b333d3e919e3a|148
2067|2013-01-24 16:29:34.087|-300|4|0|1|1f112c97a196b244b2425c5704aeb1e3|668
2067|2013-01-24 16:29:41.680|-300|5|0|1|3377cd61bf7b50448b14e8a15fc90c7d|7  
2067|2013-01-24 16:32:06.120|-300|6|0|1|16549721e5a19842a7ed6b16e27a7200|145
2067|2013-01-24 16:37:23.357|-300|3|0|1|e48e6cfa25177b48aa1b181af70bc78e|317
2067|2013-01-24 16:37:39.327|-300|2|4|1|f8d1a6d599464845890da88858735bc2|16 
2067|2013-01-24 16:39:59.810|-300|3|0|1|7dd37aaebb8631408fa6c2b672c91dc3|140
2067|2013-01-24 16:51:46.753|-300|4|0|1|8d3c60b2d6bd2e4c91cec113579dd2b2|707
2067|2013-01-24 16:51:53.363|-300|5|0|1|1fdaf2ae7f15c240964bc251db6ce1fa|7  
2067|2013-01-24 16:55:12.427|-300|6|0|1|adca256dc1ef644dad78fca87c9b9c47|199
4

With millisecond resolution?

5

No, I think it should be just seconds.

6

Yes, but the milliseconds are in the time stamps being compared. Can we mine them out, multiply up the sec and add in? Sybase datediff() does it.

7

Thanks all for your help. Bipinajith your solution worked but I needed something in awk. Searching further I found a awk example and was able to cobble this together and it seems to work. I ignored the milliseconds. Thanks.

cat tmp1 | awk '
BEGIN{
FS="|"
}
{
  TIME=substr($2,12,8)
  m=split(TIME,t,":")
  n=split(PREVTIME,w,":")
  FIRSTTIME= (t[1]*3600) + (t[2]*60) + t[3]
  SECONDTIME= (w[1]*3600) + (w[2]*60) + w[3]
  DIFFTIME=(FIRSTTIME - SECONDTIME)
  PREVTIME=TIME
  printf("%s|%s|%s|%s\n",TIME,FIRSTTIME,SECONDTIME,DIFFTIME)
}'
8

Shucks, no mktime(), no milliseconds, no date!