Transpose Datefield from rows to column + Print time diff

Shell Programming and Scripting
1

Hi Experts,

Can you please help me in transposing Datefield from rows to column and calculate the time difference for each of the Jobids:

Input File:

08/23/2012 12:36:09,JOB_5340
08/23/2012 12:36:14,JOB_5340
08/23/2012 12:36:22,JOB_5350
08/23/2012 12:36:26,JOB_5350

Required Output:

08/23/2012 12:36:14,08/23/2012 12:36:09,00:00:05,JOB_5340
08/23/2012 12:36:26,08/23/2012 12:36:22,00:00:04,JOB_5350

Many Thanks

2

This is one way:

$ cat t
08/23/2012 12:36:09,JOB_5340
08/23/2012 12:36:14,JOB_5340
08/23/2012 12:36:22,JOB_5350
08/23/2012 12:36:26,JOB_5350

$ cat test.sh
while IFS="," read inDate jobName
do
  (( c+=1 ))
  if [[ $c -eq 1 ]]; then
    ts1=$inDate
  else
    s=$(( $(date -d "$inDate" +%s) - $(date -d "$ts1" +%s) ))
    printf "$inDate,$ts1,%02d:%02d:%02d" $((s/3600)) $((s%3600/60)) $((s%60))
    echo ",$jobName"
    c=0
  fi
done <t

$ test.sh
08/23/2012 12:36:14,08/23/2012 12:36:09,00:00:05,JOB_5340
08/23/2012 12:36:26,08/23/2012 12:36:22,00:00:04,JOB_5350
1 Like
3

Hi Spacebar,

Thanks a lot for your quick response. I have tried the code and this throws some errors:

./trans.ksh[17]: c+=1 : 0403-009 The specified number is not valid for this command.

Can you also please explain the given code

Thanks,
Nandha

4

The attempt is to add 1 to the variable c each time through the loop. If unset, the shell should assume 0. I'm guessing your version of Kshell is old enough that it doesn't support it. You could try replacing the line with

c=$(( $c  + 1 ))

and add

c=0

before the loop.

If it still isn't working, please post the shell, and version, that you are using in addition to the errors.

5

Add this statement before the 'when' statement and test:

c=0
6

Spacebar, Agama

I have tried the suggested changes, and it still errors out...

Changed code:

while IFS="," read inDate jobName
c=0
do
c=$(($c+1))
if [[$c -eq 1]]; then
ts1=$inDate
else
s=$(($(date -d "$inDate" +%s) - $(date -d "$ts1" +%s)))
printf "$inDate,$ts1,%02d:%02d:%02d" $((s/3600)) $((s%3600/60)) $((s%60))
echo ",$jobName"
fi
done <t.csv

Error Message:

./trans.ksh[19]: [[1:  not found.
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
./trans.ksh[22]:  - : 0403-053 Expression is not complete; more tokens expected.

I'm using ksh on AIX machine, and the ksh version is Version M-11/16/88f

Many thanks for your help
NK

7

Since your using ksh88, This is another way you can get what you want:

Perl script:
#!/usr/bin/perl -w
use POSIX;
use strict;
use warnings;

my $rc;             # Read count
my @r;              # Record fields
my @ts;             # Time stamp fields
my @sts;            # Save of start timestamp
my ( $sec, $min, $hour, $day, $mon, $year, $wday, $yday );
my $ut_str_time;    # Start timestamp in unix time
my $ut_end_time;    # End   timestamp in unix time
my $diff;           # Difference in seconds
my $dstr;           # Difference in format: hh:mm:ss

open ( INFILE, "<t") or die ("***Error- Couldn't open file: t, $!\n");
while ( <INFILE> ) {
  $rc++;
  chomp;
  @r  = split((','),$_);
  @ts = split((':|/| '),$r[0]);
  if ( $rc == 1 ) {
    @sts = @ts;
  } else {
    $ut_end_time = mktime( $ts[5], $ts[4], $ts[3], $ts[1], ($ts[0]-1), ($ts[2]-1900), 0, 0 );
    $ut_str_time = mktime( $sts[5], $sts[4], $sts[3], $sts[1], ($sts[0]-1), ($sts[2]-1900), 0, 0 );
    $diff        = $ut_end_time - $ut_str_time;
    $dstr        = POSIX::strftime("%H:%M:%S",$diff,0,0,0,0,0,0,0,0);
    print "$ts[0]/$ts[1]/$ts[2] $ts[3]:$ts[5]:$ts[4],$sts[0]/$sts[1]/$sts[2] $sts[3]:$sts[5]:$sts[4],$dstr,$r[1]\n";
    $rc = 0;
  }
}

$ cat t
08/23/2012 12:36:09,JOB_5340
08/23/2012 12:36:14,JOB_5340
08/23/2012 12:36:22,JOB_5350
08/23/2012 12:36:26,JOB_5350

$ test.pl
08/23/2012 12:14:36,08/23/2012 12:09:36,00:00:05,JOB_5340
08/23/2012 12:26:36,08/23/2012 12:22:36,00:00:04,JOB_5350