How to calculate the starting and end date difference using awk

Question

I need to print the difference (in days) in ($6) between the starting and end date of records for each unique ID ($5) on a new field.
the data looks like this

7  65  2    5   32070  2010-12-14    13:25:30    
7  82  2    10  41920  2010-12-14    11:30:45  
7  83  1    67  29446  2010-12-14    04:15:25      
7  81  1    47  32070  2011-5-11     08:14:20  
7  83  1    67  29446  2011-6-22     07:13:24
7  82  2    10  41920  2011-5-14     06:15:25  

I need to see as follows:

7  65  2    5   32070  2010-12-14    13:25:30   147  
7  82  2    10  41920  2010-12-14    11:30:45   150  
7  83  1    67  29446  2010-12-14    04:15:25   189  
7  81  1    47  32070  2011-5-11     08:14:20   147  
7  83  1    67  29446  2011-6-22     07:13:24   189  
7  82  2    10  41920  2011-5-14     06:15:25   150 

I have used the following code but give me error message. could you help me if you have another option?

awk '{  
       split($6,arr,"-")  
      a=sprintf("%s %s %s 0 0 0",arr[1], arr[2], arr[3])  
      d=mktime(a)    
      delta[$5]=delta[$5] " " d  
     }   
   END {for(i in delta) {print i, delta[i]}  }'  filename > tmp.dat  

awk '{  
     if (FILENAME=="tmp.dat" )  
     {   
       delta[$1]=$0;   
       next  
     }  
     if (FILENAME=="filename")  
     {   
       a="-1"  
       if($5 in delta)  
      {  
        cnt=split(delta[$5],arr)  
       if(cnt==3)  
       {  
         a=arr[3] - arr[2]  
         a/=86400  
         a=int(a)  
       }  
       }  
      print $0, a        
      next  
       }  
        }' tmp.dat filename     

Answer 1

I know you are asking for an awk solution but maybe consider a Python/Pandas solution for this.

Convert source file

awk '{ $1 = $1; $0 = $0; print }' OFS=, tmp.dat > tmp1.dat

Then use pandas

import pandas as pd                                                                                                                                                                            
import numpy as np                                                                                                                                                                                   


df=pd.read_csv("/tmp/tmp1.dat",names=[0,1,2,3,4,5,6],dtype={1:str,
                                                            2:str,
                                                            3:str,
                                                            4:str,
                                                            5:str,
                                                            6:str})                                                                                                                                          
df[5]=pd.to_datetime((df[5].astype(str)+" "+df[6].astype(str))); del df[6] 

for i,j in df.groupby(4):
    df.ix[df[4]==i,'days']=j[5].diff().fillna(method='bfill')

df['days']=(df['days']/np.timedelta64(1,'D')).astype(int)

df.to_csv("/tmp/ans)

ans looks like this

7,65,2,5,32070,2010-12-14 13:25:30,147
7,82,2,10,41920,2010-12-14 11:30:45,150
7,83,1,67,29446,2010-12-14 04:15:25,190
7,81,1,47,32070,2011-05-11 08:14:20,147
7,83,1,67,29446,2011-06-22 07:13:24,190
7,82,2,10,41920,2011-05-14 06:15:25,150

Answer 2

In awk. Source file is read in twice. On the first go time difference is computed, on the second records are outputed with appended time differencies.

$ awk 'NR==FNR {
           c = "date -d \""$6 "\" +%s";   # use system date for epoch time seconds
           c | getline d;                 # execute command in c var, output to d 
           a[$5] = (($5 in a) ? d-a[$5] : d); # set or subtract from array
           next                           # skip to next record
       } {                                # for the second go:
           # $1=$1;                       # uncomment to clean trailing space
           print $0, int(a[$5]/86400)     # print record and time  difference
       }' file file
7  65  2    5   32070  2010-12-14    13:25:30     147
7  82  2    10  41920  2010-12-14    11:30:45   150
7  83  1    67  29446  2010-12-14    04:15:25       189
7  81  1    47  32070  2011-5-11     08:14:20   147
7  83  1    67  29446  2011-6-22     07:13:24 189
7  82  2    10  41920  2011-5-14     06:15:25   150

The spacing before time difference varies because your data has trailing space after $NF. You can trim it out with for example $1=$1; before the print.

EDIT: It expects that there are only 2 of each unique IDs in field $5. When the first occurrance of an ID is found, the date in field $6 (and only the date part) is converted to seconds and stored to array a[$5]. When the next one is found, the time stored to a[$5] is subtracted from the later found time and stored to a[$5]. If there are more than 2 occurrences of the unique ID $5 time in a[$5] is subtracted from the last found time and resulting in chaos.