Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Get time different between two dates using awk.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Get time different between two dates using awk.
# 1  
Old 01-29-2013
Get time different between two dates using awk.
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.

Code:
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

Last edited by Scrutinizer; 01-29-2013 at 02:05 PM.. Reason: icode to code tags
# 2  
Old 01-29-2013
Use mktime to get the seconds and substract: Time Functions - The GNU Awk User's Guide
# 3  
Old 01-29-2013
If you have GNU date, then use a BASH script:
Code:
#!/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:
Code:
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  
Old 01-29-2013
With millisecond resolution?
# 5  
Old 01-29-2013
Quote:
Originally Posted by DGPickett
With millisecond resolution?
No, I think it should be just seconds.
# 6  
Old 01-29-2013
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  
Old 01-29-2013
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.

Code:
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)
}'

Last edited by vgersh99; 01-29-2013 at 06:12 PM.. Reason: fixed the code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk - printing new lines based of 2 dates

I have some test data that is seperated out into annual records, each record has a start date (COL7), an end date (COL8) and a maturity date (COL18) - What I need to do is ensure that there is one record to cover each year right up until Maturity date (COL18). In the first group of the below... (10 Replies)
Discussion started by: Ads89
10 Replies

2. Shell Programming and Scripting

awk comparison of dates

I need to use awk to return lines in multiple files that contain a date between a start date and end date. The format of the date is as seen in column 3 in the following line. A,1458147240,Mar 30 2015 12:54:00PM,s15u4chn ,2,GPS Major Alarm `clear`,component.Channel,10,15,0,138,183,,,Mar 16... (4 Replies)
Discussion started by: randman1
4 Replies

3. Programming

Find gaps in time data and replace missing time value and column 2 value by interpolation in awk

Dear all, I am kindly seeking assistance on the following issue. I am working with data that is sampled every 0.05 hours (that is 3 minutes intervals) here is a sample data from the file 5.00000 15.5030 5.05000 15.6680 5.10000 16.0100 5.15000 16.3450 5.20000 16.7120 5.25000... (4 Replies)
Discussion started by: malandisa
4 Replies

4. Shell Programming and Scripting

Perl ::duration of time in between dates

Hello All, I have two strings with date and time as follows.. $starttime= "06/11/2013 "; $starttime= "05:15"; $enddate="06/12/2013"; $endtime="04:45"; dates are in mm/dd/yyyy format and time in military format. and I am looking the duration of time(in minutes) in between dates. ... (3 Replies)
Discussion started by: scriptscript
3 Replies

5. Shell Programming and Scripting

Calculate time difference between pst and pdt dates in perl

Hi, how to calculate the time difference between PST date and PDT date in perl scripting. date1: Mon Dec 31 16:00:01 PST 2015 date2: Tue Mar 19 06:09:30 PDT 2013 and also difference between PST-PST and PDT-PDT need difference in months or days (months prefereble). (3 Replies)
Discussion started by: praveen265
3 Replies

6. Shell Programming and Scripting

Compare dates with time

Hi Team, I need to compare three dates and extract the greatest among them into a file. 21 Jan 2012 05:46:59,146 21 Jan 2012 02:12:30,113 17 Jan 2012 09:08:10,417 Please help regarding the same. Thanks in advance..!!! Please use tags where appropriate, thank you (6 Replies)
Discussion started by: jaituteja
6 Replies

7. Shell Programming and Scripting

Awk program for calculating dates.

Hi All, I have a txt file which has hundreds of lines and 41 fields. I have a requirement to pick up field 14 from the text file which is a date fiels in the following format. Field 14 : Data Type : NUMERIC DATE (YYYYMMDD) Field Length : 8 Example of Data :20090415 Field 42 : Data Type... (2 Replies)
Discussion started by: nua7
2 Replies

8. Shell Programming and Scripting

Awk question: Sum dates

Hi there, I have a logfile which has the following layout: 20080812 0 20 20080812 12 10 20080812 12 10 20080812 12 10 I want to sum the "12" on the last 3 lines and save the "20" on the first line. The final output should be 20080812 36 20 I think that should me more easier with... (6 Replies)
Discussion started by: BufferExploder
6 Replies

9. HP-UX

Time Between Dates

Time Between Dates Does anyone know how to figure out the time between two dates, by reading two log files start.log(starting time) and end.log(ending time)? So if I have the content of start.log as : Mon, Feb 18, 2008 09:30:02 PM & end.log as: Tue, Feb 19, 2008 01:25:14 AM How can... (2 Replies)
Discussion started by: Sreejith_VK
2 Replies

10. Shell Programming and Scripting

Time Between Dates

Time Between Dates Does anyone know how to figure out the time between two dates in HP-UX, by reading two log files start.log(starting time) and end.log(ending time)? So if I have the content of start.log as : Mon, Feb 18, 2008 09:30:02 PM & end.log as: Tue, Feb 19, 2008 01:25:14 AM ... (3 Replies)
Discussion started by: Sreejith_VK
3 Replies
Login or Register to Ask a Question