# Get time different between two dates using awk.

**URL:** <https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488>\
**Category:** Shell Programming and Scripting\
**Created:** [January 29, 2013, 11:58am UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488 "2013-01-29T11:58:12Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![benefactr](https://community.unix.com/letter_avatar/benefactr/32/6_449c533f586939b6e5f246632a46421f.png) [@benefactr](https://community.unix.com/u/benefactr)\
**Post date:** [January 29, 2013, 11:58am UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/1 "2013-01-29T11:58:12Z")

</div>

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.

```nohighlight
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

```

---

<div class="post-metadata">

**Author:** ![DGPickett](https://community.unix.com/letter_avatar/dgpickett/32/6_449c533f586939b6e5f246632a46421f.png) [@DGPickett](https://community.unix.com/u/DGPickett)\
**Post date:** [January 29, 2013, 12:16pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/2 "2013-01-29T12:16:49Z")

</div>

Use mktime to get the seconds and substract: [Time Functions - The GNU Awk User's Guide](http://www.gnu.org/software/gawk/manual/html_node/Time-Functions.html)

---

<div class="post-metadata">

**Author:** ![Yoda](https://community.unix.com/user_avatar/community.unix.com/yoda/32/2498_2.png) [@Yoda](https://community.unix.com/u/Yoda)\
**Post date:** [January 29, 2013, 12:29pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/3 "2013-01-29T12:29:27Z")

</div>

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

```nohighlight
#!/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:

```nohighlight
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

```

---

<div class="post-metadata">

**Author:** ![DGPickett](https://community.unix.com/letter_avatar/dgpickett/32/6_449c533f586939b6e5f246632a46421f.png) [@DGPickett](https://community.unix.com/u/DGPickett)\
**Post date:** [January 29, 2013, 1:20pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/4 "2013-01-29T13:20:53Z")

</div>

With millisecond resolution?

---

<div class="post-metadata">

**Author:** ![Yoda](https://community.unix.com/user_avatar/community.unix.com/yoda/32/2498_2.png) [@Yoda](https://community.unix.com/u/Yoda)\
**Post date:** [January 29, 2013, 2:54pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/5 "2013-01-29T14:54:17Z")

</div>

> [@dgpickett](#):
>
> With millisecond resolution?

No, I think it should be just seconds.

---

<div class="post-metadata">

**Author:** ![DGPickett](https://community.unix.com/letter_avatar/dgpickett/32/6_449c533f586939b6e5f246632a46421f.png) [@DGPickett](https://community.unix.com/u/DGPickett)\
**Post date:** [January 29, 2013, 3:01pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/6 "2013-01-29T15:01:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![benefactr](https://community.unix.com/letter_avatar/benefactr/32/6_449c533f586939b6e5f246632a46421f.png) [@benefactr](https://community.unix.com/u/benefactr)\
**Post date:** [January 29, 2013, 4:49pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/7 "2013-01-29T16:49:33Z")

</div>

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.

```nohighlight
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)
}'

```

---

<div class="post-metadata">

**Author:** ![DGPickett](https://community.unix.com/letter_avatar/dgpickett/32/6_449c533f586939b6e5f246632a46421f.png) [@DGPickett](https://community.unix.com/u/DGPickett)\
**Post date:** [January 29, 2013, 5:26pm UTC](https://community.unix.com/t/get-time-different-between-two-dates-using-awk/323488/8 "2013-01-29T17:26:07Z")

</div>

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